SelectQuery.setFetchLimit() performance

[Michael Shea <mi...@nitido.com>]

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.

Re: SelectQuery.setFetchLimit() performance

[Andrus Adamchik <an...@objectstyle.org>]

I think you are using prefetching, although this is not shown in your  
example. Otherwise why would Cayenne go after a related entity data.

Unfortunately you when using prefetching with a fetch limit, the fetch  
limit is only applied to the root query. This is not a bug, this is  
needed for the result correctness. Pagination solution that Ari  
suggested should (indirectly) solve prefetching / fetch limit combo  
limitation.

Andrus

On Sep 10, 2008, at 5:57 PM, Michael Shea wrote:

> Hm. I don't think that paging will solve the issue I am working on.  
> Basically, I have an API that returns objects which are constructed  
> from Cayenne DB-objects. We have a search() method in this API,  and  
> it needs to support setting a limit on the number of objects  
> returned by the search (not paginating the result, just truncating  
> it).
>
> Currently, I am using setFetchLimit() on a SelectQuery for this.
>
>
> Shea.
>>
>> On 10/09/2008, at 7:44 AM, Michael Shea wrote:
>>
>>> I am wondering about the performance of the  
>>> SelectQuery.setFetchLimit() method in Cayenne.
>>
>> What is the underlying action you are trying to optimise? Could it  
>> be that paging is a better match for giving you good performance in  
>> your situation? In that case Cayenne fetches full objects for the  
>> first page, and only ObjectId for the rest. Which is a lot like  
>> your proposal.
>>
>> Ari Maniatis
>>
>>
>>
>> -------------------------->
>> ish
>> http://www.ish.com.au
>> Level 1, 30 Wilson Street Newtown 2042 Australia
>> phone +61 2 9550 5001   fax +61 2 9550 4001
>> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
>>
>>
>
>

Re: SelectQuery.setFetchLimit() performance

[Michael Shea <mi...@nitido.com>]

Hm. I don't think that paging will solve the issue I am working on. 
Basically, I have an API that returns objects which are constructed from 
Cayenne DB-objects. We have a search() method in this API,  and it needs 
to support setting a limit on the number of objects returned by the 
search (not paginating the result, just truncating it).

Currently, I am using setFetchLimit() on a SelectQuery for this.


Shea.
>
> On 10/09/2008, at 7:44 AM, Michael Shea wrote:
>
>> I am wondering about the performance of the 
>> SelectQuery.setFetchLimit() method in Cayenne.
>
> What is the underlying action you are trying to optimise? Could it be 
> that paging is a better match for giving you good performance in your 
> situation? In that case Cayenne fetches full objects for the first 
> page, and only ObjectId for the rest. Which is a lot like your proposal.
>
> Ari Maniatis
>
>
>
> -------------------------->
> ish
> http://www.ish.com.au
> Level 1, 30 Wilson Street Newtown 2042 Australia
> phone +61 2 9550 5001   fax +61 2 9550 4001
> GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A
>
>

Re: SelectQuery.setFetchLimit() performance

[Aristedes Maniatis <ar...@ish.com.au>]

On 10/09/2008, at 7:44 AM, Michael Shea wrote:

> I am wondering about the performance of the  
> SelectQuery.setFetchLimit() method in Cayenne.

What is the underlying action you are trying to optimise? Could it be  
that paging is a better match for giving you good performance in your  
situation? In that case Cayenne fetches full objects for the first  
page, and only ObjectId for the rest. Which is a lot like your proposal.

Ari Maniatis



-------------------------->
ish
http://www.ish.com.au
Level 1, 30 Wilson Street Newtown 2042 Australia
phone +61 2 9550 5001   fax +61 2 9550 4001
GPG fingerprint CBFB 84B4 738D 4E87 5E5C  5EFA EF6A 7D2E 3E49 102A