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 Mon, 25 Jul 2011 10:02:28 GMT
I am not sure if this would fix it. I've just checked out the 3.0
source code, applied the patch, built Cayenne and and ran my code
against it, and am having the same problem.

Could it perhaps be because SQL Server 2008 does not have the offset
feature? Well at least not until SQL Server 2011 (based on
http://stackoverflow.com/questions/187998/row-offset-in-sql-server) or
should Cayenne be falling back to in memory limits and offsets?

Gary

On Sun, Jul 24, 2011 at 8:26 PM, Andrus Adamchik <andrus@objectstyle.org> wrote:
> I just found the Jira that I had in mind when answering your email:
>
>  https://issues.apache.org/jira/browse/CAY-1539
>
> I just committed the patch to trunk. Still suspect your case may be related somehow.
>
> Andrus
>
> On Jul 23, 2011, at 10:56 AM, Gary Jarrel wrote:
>
>> 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