cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: complex query vs performIteratedQuery vs Prefetching
Date Tue, 10 Nov 2009 15:52:46 GMT
Not sure if that's an option for you, but upgrade to 3.0B1 is worse  
trying. It fixes lots of rough edges around joint prefetching,  
paginated queries, etc.

Andrus

On Nov 10, 2009, at 5:38 PM, Hans Pikkemaat wrote:

> Hi,
>
> It seems that when I set a page size on a regular query with join  
> prefetching, cayenne
> checks if the number of records returned is equal to the page size.
>
> Because the query which is generated contains the DISTINCT keyword
> the duplicate entries are removed.
>
> This then causes the exception.
> Any idea how to overcome this?
>
> tx
>
> HPI
>
> Hans Pikkemaat wrote:
>> Hi,
>>
>> Thanks for your quick response!
>>
>> I kind of expected that prefetches and iterated queries would not  
>> work, thats the reason for this post :)
>>
>> I tried the page size on a non iterated query but then I get a  
>> weird exception that I don't
>> understand:
>>
>> Exception in thread "main"  
>> org.apache.cayenne.CayenneRuntimeException: [v.2.0.4 October 12  
>> 2007] Some ObjectIds are missing from the database. Expected 10,  
>> fetched 8org.apache.cayenne.DataRow@7fadd3[values={OBJECT_ID=567},  
>> version=-9223372036854775795, replaces=-9223372036854775808],  
>> org.apache.cayenne.DataRow@4f1ada[values={OBJECT_ID=572},  
>> version=-9223372036854775791, replaces=-9223372036854775808]
>>    at  
>> org 
>> .apache 
>> .cayenne 
>> .access 
>> .IncrementalFaultList.resolveInterval(IncrementalFaultList.java:396)
>>    at  
>> org 
>> .apache 
>> .cayenne.access.IncrementalFaultList.get(IncrementalFaultList.java: 
>> 594)
>>    at org.apache.cayenne.access.IncrementalFaultList 
>> $1.next(IncrementalFaultList.java:508)
>>    at  
>> com 
>> .traserv 
>> .incrementalsupply 
>> .integration.demo.CayenneDemo.doit3(CayenneDemo.java:136)
>>    at  
>> com 
>> .traserv 
>> .incrementalsupply 
>> .integration.demo.CayenneDemo.main(CayenneDemo.java:149)
>>
>> I cannot forget about data objects because I don't want to query  
>> the detail records separately because of
>> bad performance (too many single queries).
>>
>> tx
>>
>> HPI
>>
>> Andrey Razumovsky wrote:
>>
>>> Maybe I'm missing something, but i doubt prefetches are  
>>> implemented for iterated queries. I would try keeping only  
>>> setPageSize(1000). It will do all prefetches and result in same  
>>> memory payload, because all datarows in your example are turned  
>>> into data objects, so they will all be cached in memory. Smart  
>>> using query caching would do the trick.
>>> Otherwise, I suggest you forget about data objects in this  
>>> function of yours and use only datarows
>>>
>>> 2009/11/10 Hans Pikkemaat <h.pikkemaat@tsi-solutions.nl <mailto:h.pikkemaat@tsi-solutions.nl

>>> >>
>>>
>>>    Hi,
>>>
>>>    I'll give you some insight in what I tried.
>>>
>>>            DataContext dataContext = this.createDataContext();
>>>
>>>            String sql = "select ... from table1 join table 2 ...";
>>>            SQLTemplate query = new SQLTemplate(Table1Object.class,  
>>> sql);
>>>                
>>> query 
>>> .addPrefetch 
>>> (table2 
>>> .RELATION_PROPERTY 
>>> ).setSemantics(PrefetchTreeNode.JOINT_PREFETCH_SEMANTICS);
>>>            query.setPageSize(1000);
>>>
>>>            ResultIterator iterator =
>>>    dataContext.performIteratedQuery(query);
>>>
>>>            while (iterator.hasNextRow()) {
>>>                DataRow dataRow = (DataRow) iterator.nextDataRow();
>>>                Table1Object table1 = (Table1Object)
>>>    dataContext.objectFromDataRow(Table1Object.class, dataRow,  
>>> false);
>>>                List<Table2Object> list = table1.getTable2Objects();
>>>                for (Table2Object t2o:list) {
>>>                   System.out.println(t2o.getName());
>>>                }
>>>             }
>>>
>>>    When the for loop starts and gets the iterator from the list,  
>>> then
>>>    a separate query is executed, indicating
>>>    that the prefetching didn't work.
>>>
>>>    tx
>>>
>>>    Hans
>>>
>>>
>>>    Andrey Razumovsky wrote:
>>>
>>>>    Hi,
>>>>
>>>>    I think the best in your case would be using disjoint  
>>>> prefetching;
>>>>    http://cayenne.apache.org/doc20/prefetching.html
>>>>    This way both table data will be returned altogether (in same  
>>>> db row), so
>>>>    you will be able to iterate though them at the same time.
>>>>
>>>>    I also highly encourage you to try today-released Cayenne3.0  
>>>> beta. It proved
>>>>    to be stable and many prefetching (ant other!) improvements  
>>>> were made. Check
>>>>    the main site!
>>>>
>>>>    2009/11/10 Hans Pikkemaat <h.pikkemaat@tsi-solutions.nl> <mailto:h.pikkemaat@tsi-solutions.nl

>>>> >
>>>>
>>>>
>>>>>    Hi,
>>>>>
>>>>>    My case:
>>>>>
>>>>>    I have a complex query which returns a huge amount of data.  
>>>>> It returns data
>>>>>    from
>>>>>    two tables which are joined.
>>>>>
>>>>>    Because the amount of data is huge I cannot load all of it  
>>>>> into memory.
>>>>>    For this reason I want to use an iterated query.
>>>>>
>>>>>    I also want to prevent cayenne from executing a query for  
>>>>> each detail
>>>>>    record
>>>>>    so I want to use prefetching.
>>>>>
>>>>>    What I tried:
>>>>>
>>>>>      - I'm using cayenne 2.0.4.
>>>>>      - I use SQLTemplate to instantiate the query.
>>>>>      - I set the page size to 1000 to prevent loading the whole  
>>>>> db (required
>>>>>    for postgres)
>>>>>      - I use performIteratedQuery to run the query.
>>>>>      - As I get DataRows back from the iterated query I use
>>>>>    dataContext.objectFromDataRow(..) to create my objects
>>>>>
>>>>>    The Problem:
>>>>>
>>>>>      The relation is not automatically setup. This means that  
>>>>> for every record
>>>>>    I access the detail table a query is executed.
>>>>>
>>>>>    My Questions:
>>>>>
>>>>>      - How should I use prefetching in combination with  
>>>>> SQLTemplate and an
>>>>>    iterated query which returns DataRows?
>>>>>      -  Is there a way to manually setup this relationship  
>>>>> (without changing
>>>>>    the state of the parent object?)
>>>>>      -  Is there another way to do what I want using Cayenne?
>>>>>
>>>>>
>>>>
>>>    --     	TSi Solutions
>>>    Neptunusstraat 25
>>>    7521 WC Enschede
>>>
>>>    Tel. +31 (0)88 - 25 00 000
>>>    Fax. +31 (0)88 - 25 00 122
>>>    Hans Pikkemaat
>>>    Java Developer (Services Team)
>>>    	E-mail: h.pikkemaat@tsi-solutions.nl
>>>    <mailto:h.pikkemaat@tsi-solutions.nl>
>>>      	www.tsi-solutions.nl <http://www.tsi-solutions.nl/>
>>>    www.toeristiek.nl <http://www.toeristiek.nl/>
>>>    	
>>>
>>>    10 jaar TSi Solutions
>>>    ... marktleider in het automatiseren en outsourcen van
>>>    werkprocessen in de reisbranche
>>>    ... toonaangevende partij voor het verzamelen, structureren en
>>>    beschikbaarstellen van reiscontent
>>>    ... Reisrevue Innovatieveer 2008 - Veervolle vermelding
>>>    ... Winnaar Reisrevue Innovatieveer 2009
>>>    ... Top 20 positie in 2008 Deloitte Technology Fast50 Nederland
>>>    ... Top 10 positie in 2009 Deloitte Technology Fast50 Benelux
>>>    ... genomineerd voor Technology 500 EMEA 2009
>>>    TSi Solutions is de handelsnaam van Travel Service International
>>>    b.v.[KvK 06091935]
>>>    DISCLAIMER: De informatie opgenomen in dit bericht kan
>>>    vertrouwelijk zijn en is uitsluitend bestemd voor de  
>>> geadresseerde.
>>>    Indien u dit bericht onterecht ontvangt, wordt u verzocht de
>>>    inhoud niet te gebruiken en de afzender direct te informeren door
>>>    het bericht te retourneren.
>>>    The information contained in this message may be confidential and
>>>    is intended to be exclusively for the addressee.
>>>    Should you receive this message unintentionally, please do not  
>>> use
>>>    the contents herein and notify the sender immediately by return
>>>    e-mail.
>>>
>>>
>>>
>>>
>>> -- 
>>> Andrey
>>>
>>
>>
>


Mime
View raw message