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.
*
*..ince 3.0
*/
protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
}
MySQL adapter uses a custom SelectTranslator to do:
..verride
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.zeigle..oxanemy.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
>>
>>
This archive was generated by hypermail 2.0.0 : Tue Jun 16 2009 - 22:21:18 EDT