cayenne-commits mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Malcolm Edgar (JIRA)" <j...@apache.org>
Subject [jira] Created: (CAY-1244) Apply SQL Server TOP Fetch Limit
Date Wed, 17 Jun 2009 14:14:07 GMT
Apply SQL Server TOP Fetch Limit
--------------------------------

                 Key: CAY-1244
                 URL: https://issues.apache.org/jira/browse/CAY-1244
             Project: Cayenne
          Issue Type: Improvement
          Components: Cayenne Core Library
    Affects Versions: 3.0M5
            Reporter: Malcolm Edgar
            Priority: Critical


The Cayenne SQL Server adaptor has does not apply the SelectQuery fetch limit using the SQL
Server TOP expression.  For example:

   SELECT TOP 1000 * FROM customer

See the TOP expression reference at:  http://msdn.microsoft.com/en-us/library/ms189463.aspx

This can result is very large resultset being loaded into memory by Cayenne and leading to
out of memory errors.  This has occurred over the last few days on a JBoss with SQL Server
taking out the entire application server.

While this issue may not technically be a bug, its behaviour is not what you would expect
for a top tier database adaptor. 

A proposed solution is provided below. I will attempt to provide a patch in the next few days.

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);
        }
    }

}

Not the metadata.getFetchOffset() is not applied above, as there is no equivalent function
in SQL Server 2000 & 2005.  Note while SQL Server 2005 has a ROW_COUNT() function, the
SQL looks a little crazy and I have not experience or confidence in its use:

http://msdn.microsoft.com/en-us/library/ms186734.aspx


-- 
This message is automatically generated by JIRA.
-
You can reply to this email to add a comment to the issue online.


Mime
View raw message