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 Wed, 17 Jun 2009 12:44:45 GMT
What appears to work well for SQL Server is:

public class SQLServerSelectTranslator extends SelectTranslator {
	
    @Override
    protected void appendLimitAndOffsetClauses(StringBuilder buffer) {
        QueryMetadata metadata = getQuery().getMetaData(getEntityResolver());

        int limit = metadata.getFetchLimit();

        if (limit > 0) {
            buffer.replace(0, 6, "SELECT TOP " + limit);
        }
    }

}

Note this does not perform a fetchOffset, still looking to see how to
do with with SQL Server.

regards Malcolm Edgar

On Wed, Jun 17, 2009 at 9:51 PM, Andrus Adamchik<andrus@objectstyle.org> wrote:
> 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