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 >
