The big hit from a query like you describe is actually retrieving the
results, not the base query itself. You can profile this if you want -
profile the time it takes to run the query (ResultSet rs = stmt.execute()),
then profile the time it takes to iterate that result set, doing nothing but
reading the PK (pk reads on query results are "free" and most dbms won't
trigger a table scan for this, let alone bring the data across the IO).
That said, I'm sure it would be faster with LIMIT/OFFSET under certain
circumstance, but I have a feeling that in general, Cayenne does the right
thing here because the pk list can be persistent across connections, meaning
that subsequent queries can just fill in the next page using an IN(), etc.
Additionally, LIMIT/OFFSET is not widely supported and for most DBMS, not
that much faster than executing the whole query anyway.
If you're dubious, it would be pretty simply to modify the SelectTranslator
to support LIMIT/OFFSET for purposes of doing a quick profiling if you're
concerned.
Cris
> -----Original Message-----
> From: ron [mailto:ron.piterma..mx.net]
> Sent: Tuesday, March 08, 2005 5:52 AM
> To: cayenne-use..bjectstyle.org
> Subject: using LIMIT and OFFSET
>
> Hi,
>
> I am aware of the paginating solution, *but*...
>
> Performing a query, letting the database do the LIMIT and
> OFFSET, instead of letting the DB return a huge resultset and
> paging through it in java: isn't that a critical performance issue?
>
> Why should'nt use OFFSET and LIMIT which are offered by the DB?
>
> what are the issues there?
>
> How *can* I use them using cayenne?
>
> Cheers,
> Ron
>
>
This archive was generated by hypermail 2.0.0 : Tue Mar 08 2005 - 08:15:10 EST