cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: setFetchOffset & setFetchLimit issue
Date Thu, 21 Jul 2011 11:48:47 GMT
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