cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Michael Gentry <>
Subject Re: Best Practice Question
Date Fri, 30 Mar 2012 13:40:02 GMT
Hi Joe,

I've never benchmarked/explored it, but my hunch is the number of
pages would have to be pretty low (say, less than 3-5).  Of course, if
you are trying to figure out in-memory vs hit the database again, that
logic just adds a bit more complexity to your code and is more to
maintain.  I think I'd try always hitting the database and see how
that plays out instead of trying to pre-optimize it.


On Fri, Mar 30, 2012 at 8:57 AM, Joe Baldwin <> wrote:
> OK, good analysis, that makes a lot of sense. So the thing to remember with post-query
ordering on large result sets: it is more efficient to re-submit the query to the database.
> At which point (presumably list size) do you think that orderList() becomes reasonable
to use on a result set?
> Thanks,
> Joe
> On Mar 30, 2012, at 8:33 AM, Michael Gentry wrote:
>> Hi Joe,
>> I think you are on the right path using setPageSize() to limit the
>> result set, but even 50k results is a lot of PKs to return (although
>> not a ludicrous amount, depending on your users).
>> The first approach I would take is if they do a post-query sort then
>> send another query to the database with the new sort ordering in place
>> and let the database do it.
>> Let's say you have 50,000 results and a page size of 20.  50,000 / 20
>> = 2500 pages.  For you to to sort in-memory using that paginated list
>> would require 2499 trips to the database to fetch the records and make
>> Java objects out of them (I'm assuming you showed them the first page
>> already and they sorted then, so the first page is "free").  2499
>> queries will be a killer latency-wise, even if each query is quick.  I
>> think it'll be significantly faster to let the database do it,
>> especially if you have good indexes in place.
>> mrg
>> On Thu, Mar 29, 2012 at 8:29 PM, Joe Baldwin <> wrote:
>>> I am interested in the best practice wrt ordering in a certain context.
>>> Lets say the context is a store with 1000 - 50000 items. I understand that if
I can either include the "order by" directive pre-query or post query.  Also, I understand
that if I have a opportunity, then passing the "order by" to the DBMS is Cayenne-preferred
(because, as I understand it) Cayenne can retrieve the result set ordered, and minimize the
triggering of faults resulting in minimum memory usage.  I think I understand this scenario,
and have tried to code to this strategy.
>>> The second scenario is a tad more complex and really the subject of my concern.
 Note, if the result set is small, I believe that this is of no concern.  However, web-users
do not often care about setting off "totally insane queries".  So if a query is initiated
with 'give me everything you have', then once they see the result set (which I am trying to
manage with setPageSize()), they might *then* want to sort the result set (i.e. triggering
a "orderList()" which is an in-memory ordering which is not as efficient).
>>> So given the post-query sorting requirement, a large result set, using setPageSize(),
and possibly multiple different post-sorts being initiated, what is the best-practice?  Should
I resubmit these queries with the ordering, or simply use the orderList() method on the result
set?  Or, is there another strategy for optimizing coding for these requirements?
>>> (My intuition, base on reading about Cayenne is that I just need to rely on orderList()
and buy more server memory. - but I am not sure)
>>> Thanks Joe

View raw message