I am wondering about the performance of the SelectQuery.setFetchLimit()
method in Cayenne.
Suppose I have object types A and B.
A has a one-to-many relationship with B.
I want to write a SelectQuery to retrieve, at most, N objects of type A.
So the code looks like this:
SelectQuery select = ;...
select.addOrderings( .... );
select.setFetchLimit( N );
When this gets executed, I notice that the QueryLogger indicates that
the ORDER BY and LIMIT clauses are only applied to the query that
retrieves rows from table A:
SELECT ... FROM A WHERE ... ORDER BY ... LIMIT N;
But the other queries do not have ORDER BY or LIMIT clauses. The lack of
a LIMIT clause makes sense, since we obviously don't know how many rows
we're expecting to get back.
Does the client actually retrieve all of the data rows for each of the
relationships? This seems to me like it would be inefficient, as you
would get back many rows that apply to top-level objects that were
filtered out because of the LIMIT clause.
Would it be possible, in the case of items that have only one field in
their primary key, to use an IN clause to retrieve the relationships? eg:
top level: SELECT ... FROM A WHERE ... ORDER BY ... LIMIT N;
subquery: SELECT B.* FROM B, A WHERE [join B to A] AND A.PK_FIELD IN (
[results from the top-level query] );
Alternatively, would it be possible to apply the ORDER BY clause to the
subqueries (which would necessitate joining to other tables, depending
on what we're ordering by)? If the subqueries were ordered, we could
just retrieve items from them until we hit one that had a foreign key
from A that we didn't get back in the top-level query, and then stop
processing the ResultSet.
I don't know for sure if these are good ideas, or if they're possible or
desirable in Cayenne's framework; please enlighten me =).
Thanks,
Mike Shea.
This archive was generated by hypermail 2.0.0 : Tue Sep 09 2008 - 17:44:58 EDT