cayenne-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Andrus Adamchik <and...@objectstyle.org>
Subject Re: Cayenne Fetch Limit behaviour ?
Date Wed, 17 Jun 2009 11:51:02 GMT
Robert is absolutely right - we have implementations for some  
adapters, and the rest are doing in-memory ResultSet truncation which  
is certainly not too efficient. I guess the craziest SQL that we had  
to generate to date was for Oracle in OracleSelectTranslator.  
SQLServer version should also be doable.

Andrus


On Jun 17, 2009, at 5:37 AM, Malcolm Edgar wrote:

> Thanks Robert,
>
> I will look at writing a patch.  SQL Server syntax is a little
> different from other databases in that the limit is set after the
> select.
>
> select TOP 20 customer_id, first_name from customer;
>
> 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