cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Malcolm Edgar <malcolm.ed...@gmail.com>
Subject Re: Cayenne Fetch Limit behaviour ?
Date Thu, 18 Jun 2009 10:17:42 GMT
Hi Robert,

JIRA and patch below. This does not include an automated unit test,
however we have been testing it successfully on our servers.

https://issues.apache.org/jira/browse/CAY-1244

regards Malcolm Edgar

On Wed, Jun 17, 2009 at 12:20 PM, Robert
Zeigler<robert.zeigler@roxanemy.com> wrote:
> setFetchLimit has been around for awhile, you're right. But the 2.0 behavior
> was, for all adapters, if I'm not mistaken, to do an in-memory fetch. 3.0
> added the ability to set the offset, and with it, added the option to set
> the fetch limit at the database level.  But, as mentioned, this behavior
> isn't implemented for all adapters.
>
> Implementation in SelectTranslator:
>
>    /**
>     * Handles appending optional limit and offset clauses. This
> implementation does
>     * nothing, deferring to subclasses to define the LIMIT/OFFSET clause
> syntax.
>     *
>     * @since 3.0
>     */
>    protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
>
>    }
>
> MySQL adapter uses a custom SelectTranslator to do:
>
>    @Override
>    protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
>        int offset = queryMetadata.getFetchOffset();
>        int limit = queryMetadata.getFetchLimit();
>
>        if (offset > 0 || limit > 0) {
>            buffer.append(" LIMIT ");
>
>            // both OFFSET and LIMIT must be present, so come up with
> defaults if one of
>            // them is not set by the user
>            if (limit == 0) {
>                limit = Integer.MAX_VALUE;
>            }
>
>            buffer.append(limit).append(" OFFSET ").append(offset);
>        }
>    }
>
>
> The SQLAdapter, on the other hand, uses the default SelectTranslator
> implementation.
> Feel free to open an issue for SQLServer and supply a patch. :) I would
> write it myself, but don't have access to SQLServer, nor am I particularly
> versed in its dialect of SQL.
> But if you open the issue and supply a patch + tests, I'll be happy to apply
> the patch to the codebase.
>
> Robert
>
> On Jun 16, 2009, at 6/169:06 PM , Malcolm Edgar wrote:
>
>> Fetch limit has been around since Cayenne 2.0, and its not working as
>> I expected.
>>
>> Stepping through the code its performing the limit operation after the
>> query has been performed. For example a table with 100,000 rows will
>> be read into memory even with a fetch limit of 100. Then Cayenne
>> provides a wrapper around the iterator which returns only 100 records.
>>
>> This behaviour really needs to be documented, however more to the
>> point this is not what I would expect from an ORM I would expect it to
>> use the database to set the limit.
>>
>> For example:
>>
>> // mysql
>> select col from tbl limit 20;
>>
>> // Oracle
>> select col from tbl where rownum<=20;
>>
>> // Microsoft SQL
>> select top 20 col from tbl;
>>
>> We are going to have to revisit a bunch of code after figuring this out :(
>>
>> regards Malcolm Edgar
>>
>> On Wed, Jun 17, 2009 at 11:37 AM, Robert
>> Zeigler<robert.zeigler@roxanemy.com> wrote:
>>>
>>> I don't think the behavior changed, per se. Rather, setFetchLimit is a
>>> relatively new feature, and may not be properly supported by all of the
>>> db
>>> adaptors yet.
>>>
>>> Robert
>>>
>>> On Jun 16, 2009, at 6/167:28 PM , Malcolm Edgar wrote:
>>>
>>>> Hi Guys,
>>>>
>>>> On SQL Server we are finding that the setting the Fetch Limit on a
>>>> SelectQuery does not modify the SQL query, to set TOP or SET ROWCOUNT,
>>>> so the database is not limiting the number of rows returned, and it
>>>> appears that Cayenne is limiting the number of rows returned in
>>>> memory?
>>>>
>>>> This is killing our application with OOM errors. Did this behaviour
>>>> change? We are using Cayenne 3.0M5
>>>>
>>>> regards Malcolm Edgar
>>>
>>>
>
>

Mime
View raw message