Hi Ari, On May 25, 2007, at 8:11 AM, Aristedes Maniatis wrote:
Q1: So, my first question is why is Cayenne doing this? If paging is switched on, shouldn't Cayenne be fetching only the primary keys and then faulting in the particular records it needs in full?
The default strategy, as implemented by org.apache.cayenne.access.IncrementalFaultList, is to run a full query to fully resolve page #1, but only read id columns from the result set for pages 2, 3, etc... This strategy bets that a single fat SQL query with full ResultSet fetching, but without reading unneeded columns is faster than a PK-only query followed by a second query to resolve page #1.
In some circumstances this is true, in some (like yours) it is clearly not. I am leaning towards making a second strategy the default, as paginated queries are really intended for the huge result sets... Anybody has other thoughts on that?
Q2: Would it more more efficient from a db perspective to use the "t0.id IS IN (?, ?, ?...)" style syntax here instead or does it make no difference? I don't know enough about SQL optimisation to know either way, but at least the SQL is shorter (which might be good!).
Can't comment on the performance - this has to be checked against a specific DB, but the OR syntax is more generic, as it handles compound primary keys, while IN would only work with a single column PK.
Andrus
