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<[email protected]> 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<[email protected]> 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
