You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cayenne.apache.org by Malcolm Edgar <ma...@gmail.com> on 2009/06/17 02:28:32 UTC
Cayenne Fetch Limit behaviour ?
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
Re: Cayenne Fetch Limit behaviour ?
Posted by Malcolm Edgar <ma...@gmail.com>.
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<an...@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<ro...@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<ro...@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
>>>>>
>>>>>
>>>
>>>
>>
>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Andrus Adamchik <an...@objectstyle.org>.
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<ro...@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<ro...@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
>>>>
>>>>
>>
>>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Malcolm Edgar <ma...@gmail.com>.
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<ro...@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<ro...@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
>>>
>>>
>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Michael Gentry <mg...@masslight.net>.
Well ... welcome back. :-)
On Thu, Jun 18, 2009 at 7:18 AM, Malcolm Edgar<ma...@gmail.com> wrote:
> I think I forgot.
>
> regards Malcolm
Re: Cayenne Fetch Limit behaviour ?
Posted by Malcolm Edgar <ma...@gmail.com>.
I think I forgot.
regards Malcolm
On Thu, Jun 18, 2009 at 8:35 PM, Andrus Adamchik<an...@objectstyle.org> wrote:
> Hi Malcolm,
>
> you are a committer :-). Nothing wrong with asking for a patch review, but
> at the end you can just commit it yourself.
>
> Btw, we do have generic tests for fetch limit, you just need to run them
> against SQLServer:
>
> http://cayenne.apache.org/running-unit-tests.html
>
> Andrus
>
> On Jun 18, 2009, at 1:17 PM, Malcolm Edgar wrote:
>
>> 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<ro...@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<ro...@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
>>>>>
>>>>>
>>>
>>>
>>
>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Andrus Adamchik <an...@objectstyle.org>.
Hi Malcolm,
you are a committer :-). Nothing wrong with asking for a patch review,
but at the end you can just commit it yourself.
Btw, we do have generic tests for fetch limit, you just need to run
them against SQLServer:
http://cayenne.apache.org/running-unit-tests.html
Andrus
On Jun 18, 2009, at 1:17 PM, Malcolm Edgar wrote:
> 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<ro...@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<ro...@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
>>>>
>>>>
>>
>>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Malcolm Edgar <ma...@gmail.com>.
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<ro...@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<ro...@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
>>>
>>>
>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Robert Zeigler <ro...@roxanemy.com>.
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<ro...@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
>>
>>
Re: Cayenne Fetch Limit behaviour ?
Posted by Malcolm Edgar <ma...@gmail.com>.
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<ro...@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
>
>
Re: Cayenne Fetch Limit behaviour ?
Posted by Robert Zeigler <ro...@roxanemy.com>.
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