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.zeig...@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.zeig...@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 >>> >>> > >
