cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Joe Baldwin <>
Subject Re: Best Practice Question
Date Fri, 30 Mar 2012 12:57:08 GMT
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?


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