cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Hans Pikkemaat <>
Subject Re: Object Caching
Date Fri, 13 Nov 2009 12:09:41 GMT

No not completely. Because of the problems I was having with paging in 
combination with SQLTemplate and prefetching
I was doing a simple test to see how much memory would actually be 
required to run a big query.
This in an attempt to calculate the total memory I would need for my 2.5 
mil query.

This test does not use paging (because of the earlier problems) but did 
use prefetching (because I don't
want to run 2.5 million queries for the detail table).

The problem I saw is the following. The initial query which accesses the 
main table and does a left join
to the detail table took about 1 minute. Then it took cayenne 2 minutes 
to construct the objects and
related objects from the datarows produced from this query. This 
construction does the job
correctly but in my opinion it is taking too long. If the main query can 
run in 1 minute and get all
the data from the database (which is IO and normally would be seen as 
the bottleneck) why
does it take 2 minutes to convert this into objects and relations.

The conversion into memory goes fairly quickly. After that I only see 
100% cpu and no changes
in memory occupation (used profiler). From stack traces I can see that 
all the time spent is in




which finally calls


which does all the time consuming work.

I think there is a piece of code somewhere which traverses a list of 
some kind
which is inefficient. Maybe a HashMap is used with a key object without
hashCode/equals methods?

Answering your points:

1) As I'm not using paging here prefetching does help because I don't 
have to query the detail table separately.
2) Yes I do need these 2.5 million records because after some 
investigation I will forward records I need.
    No its clearly not a user interface. I totally agree that Cayenne at 
least is not the best way to use.

What I initially started to use is the iterated query such I could 
iterate over the data rows and construct the
objects on the fly which would then be forwarded.

The problem here is that I cannot use prefetching nor can I manually 
construct relationships. The code is
probably there (prefetching uses it) but the api does not give me a (n 
easy way to) handle to use it.
This effectively leaves me running separate queries for each main record 
what is not performing.

Anyway, my conclusion is indeed: don't use cayenne for large query 



Aristedes Maniatis wrote:
> On 13/11/09 10:04 PM, Hans Pikkemaat wrote:
>> I ran some tests using 3.0b with SQLTemplate in combination with
>> prefetching and found
>> a possible new problem.
>> It seems that when running the query in eg 1 minute, it takes about 2
>> minutes before cayenne
>> has constructed the prefetched objects.
>> My query produces 2.5 million records. The query will take about 30
>> minutes. Construction
>> of the objects will then take an extra hour.
> Just to be clear about what you are doing:
> * Cayenne 3.0 beta 1
> * SQL template query
> * prefeching across to-many join
> * paging on
> You expect the first query (which gets hollow objects) to NOT include the prefetch JOIN,
but when fetching a page of results, it should use the prefetch. Cayenne is constructing the
first query which includes the JOIN and that makes it take 30 minutes in your database to
return 2.5 million records.
> Is that correct?
> My opinions:
> 1. Does prefetch really help here anyway? You are only getting (say) 100 records at a
time, so the extra queries to follow the relations may not be that significant.
> 2. Do you really want to fetch 2.5 million rows? If so, I assume this is not a user interface
:-)  Perhaps Cayenne (or any ORM for that matter) is not the best way to batch process that
many rows.
> Ari

  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message