cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Gary Jarrel <garyjar...@gmail.com>
Subject Re: setFetchOffset & setFetchLimit issue
Date Sat, 23 Jul 2011 07:56:07 GMT
I will do some more research to try to narrow down why this is
happening tomorrow and then post a Jira.

I also removed DISTINCT and any ordering and query parameters and the
result is still the same.

Gary

On Thu, Jul 21, 2011 at 9:48 PM, Andrus Adamchik <andrus@objectstyle.org> wrote:
> Hi Gary,
>
> the use of the API seems correct. Not sure what's the deal here. Could be a bug. (wonder
if DISTINCT combination with offset/limit is causing this, as there is some in-memory processing
involved). Could you please open a Jira and include the contents of the prototype query.
>
> Andrus
>
>
> On Jul 20, 2011, at 5:52 PM, Gary Jarrel wrote:
>> Hi Guys,
>>
>> I have an issue with using setFetchOffset & setFetchLimit together
>> with SQL Server 2008 R2.
>>
>> Basically the code looks something like this:
>>
>> SelectQuery query = getPrototypeQuery().queryWithParameters(params, true);
>> query.setFetchOffset(offset);
>> query.setFetchLimit(limit);
>> List<ConsentForm> result = getDataContext().performQuery(query);
>>
>> I would have thought that given for example offset 10 and limit 20 I
>> could get a subset of the data in the database. Yet the generated code
>> that I get from the logs is as follows keeping in mind I am using
>> ordering and distinct:
>>
>> Page 1: Offset 0 Limit 25
>> SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder,
>> t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress,
>> t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE
>> t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind:
>> 1->CustomerID:8]
>> === returned 25 rows. - took 26 ms
>>
>> The above works as expected however the code appears strange.
>>
>> Page 2: Offset 25 Limit 25
>> SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder,
>> t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress,
>> t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE
>> t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind:
>> 1->CustomerID:8]
>> === returned 0 rows. - took 20 ms
>>
>> This already stops working
>>
>> Page: 3: Offset 50 Limit 25
>> SELECT DISTINCT TOP 25 t0.DateSigned, t0.CustomerName, t0.DocsFolder,
>> t0.ExpiryDate, t0.CustomerID, t0.CreatedDate, t0.LocationAddress,
>> t0.RecordID, UPPER(t0.DateSigned) FROM dbo.ConsentForms t0 WHERE
>> t0.CustomerID = ? ORDER BY UPPER(t0.DateSigned) [bind:
>> 1->CustomerID:8]
>> === returned 0 rows. - took 20 ms.
>>
>> Basically the first page appears to be working fine, but nothing afterwards.
>>
>> I am not certain if I am using the combination of setFetchOffset and
>> setFetchLimit in a correct manner!
>>
>> Any assistance would be appreciated. Thank you!
>>
>> Gary
>>
>
>

Mime
View raw message