FAQ
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

Search Discussions

  • Andreas Hartmann at Sep 27, 2009 at 6:09 pm

    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
  • Andreas Hartmann at Sep 28, 2009 at 8:42 am

    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
  • Aristedes Maniatis at Sep 28, 2009 at 10:47 pm

    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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupuser @
categoriescayenne
postedSep 27, '09 at 3:40p
activeSep 28, '09 at 10:47p
posts4
users2
websitecayenne.apache.org

People

Translate

site design / logo © 2021 Grokbase