You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Andreas Hartmann <an...@apache.org> on 2009/09/27 17:39:04 UTC

Prefetching has no effect

Hi Cayenne community,

I'm trying to get a large set of objects using prefetching.

The following entities are involved:


Mailing
   has one RecipientSet
     has many RecipientField(s)
     has many Recipient(s)
       has many RecipientValue(s) (one for each field)


I intend to get the whole object structure with a single query on the 
RecipientValue entity with prefetches for Recipient and RecipientField. 
The query generated by Cayenne looks like this:



    SELECT t0.field, t0.recipient_set, t0.value, t0.recipient,
           t1.position, t1.recipient_set,
           t2.name, t2.position, t2.address_field, t2.recipient_set

       FROM public.recipient_value t0
  LEFT JOIN public.recipient t1
         ON (t0.recipient = t1.position
        AND t0.recipient_set = t1.recipient_set)
  LEFT JOIN public.recipient_field t2 ON (t0.field = t2.position
        AND t0.recipient_set = t2.recipient_set)
       JOIN public.recipient_set t3
         ON (t0.recipient_set = t3.id)
       JOIN public.recipient t4
         ON (t0.recipient = t4.position
        AND t0.recipient_set = t4.recipient_set)
      WHERE t3.mailing = ? ORDER BY t4.position
[bind: 1->mailing:880]


To me this looks like everything should be available after this query. 
Nevertheless, Cayenne issues additional queries when I access the 
RecipientField and Recipient properties:

SELECT DISTINCT t0.position, t0.recipient_set
       FROM public.recipient t0
       JOIN public.recipient_value t1
         ON (t0.position = t1.recipient
        AND t0.recipient_set = t1.recipient_set)
      WHERE t1.recipient_set = ?
        AND t1.field = ?
        AND t1.recipient = ?
[bind: 1->recipient_set:980, 2->field:8, 3->recipient:0]


Here's the code that builds the query:


final String path = RecipientValue.RECIPIENT_SET_PROPERTY + "."
         + RecipientSet.MAILING_PROPERTY + "." + Mailing.ID_PROPERTY;

final SelectQuery query = new SelectQuery(RecipientValue.class, 
ExpressionFactory.matchExp(path, getId()));

query.addPrefetch(RecipientValue.RECIPIENT_PROPERTY).setSemantics(
         PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
query.addPrefetch(RecipientValue.FIELD_PROPERTY).setSemantics(
         PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);

query.addOrdering(RecipientValue.RECIPIENT_PROPERTY + "." + 
Recipient.POSITION_PROPERTY, true);

final List<RecipientValue> values = getObjectContext().performQuery(query);



What could I be doing wrong?

Thanks for any hints!

-- Andreas


-- 
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01

Re: Prefetching has no effect

Posted by Aristedes Maniatis <ar...@maniatis.org>.
On 28/09/09 4:08 AM, Andreas Hartmann wrote:
> If the table contains 22.000 records, the additional queries are executed.
>
> If it contains only 100 records, the additional queries are not
> executed, i.e. prefetching works as expected.
>
> Is there some mechanism to limit the size of the object structure built
> using a single query, maybe due to memory issues?

Do you have paging switched on? What action in your code causes the second query to be executed?  Are the objects it is fetching at that time hollow?

Ari Maniatis

-- 

-------------------------->
Aristedes Maniatis
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A

Re: Prefetching has no effect

Posted by Andreas Hartmann <an...@apache.org>.
Andreas Hartmann schrieb:
> Andreas Hartmann schrieb:
>> I'm trying to get a large set of objects using prefetching.
> 
> […]
> 
>> To me this looks like everything should be available after this query. 
>> Nevertheless, Cayenne issues additional queries when I access the 
>> RecipientField and Recipient properties:
> 
> Now that's interesting.
> 
> If the table contains 22.000 records, the additional queries are executed.
> 
> If it contains only 100 records, the additional queries are not 
> executed, i.e. prefetching works as expected.
> 
> Is there some mechanism to limit the size of the object structure built 
> using a single query, maybe due to memory issues?

A fellow developer just pointed out that the garbage collector has 
probably started to remove the objects that were created based on the 
first big query, so that additional queries are launched to re-create them.

For the moment I'm using an SQLTemplate returning data records.

-- Andreas


-- 
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01

Re: Prefetching has no effect

Posted by Andreas Hartmann <an...@apache.org>.
Andreas Hartmann schrieb:
> I'm trying to get a large set of objects using prefetching.

[…]

> To me this looks like everything should be available after this query. 
> Nevertheless, Cayenne issues additional queries when I access the 
> RecipientField and Recipient properties:

Now that's interesting.

If the table contains 22.000 records, the additional queries are executed.

If it contains only 100 records, the additional queries are not 
executed, i.e. prefetching works as expected.

Is there some mechanism to limit the size of the object structure built 
using a single query, maybe due to memory issues?

Thanks for any hints!

-- Andreas



-- 
Andreas Hartmann, CTO
BeCompany GmbH
http://www.becompany.ch
Tel.: +41 (0) 43 818 57 01