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] Updated: (CAY-1244) Apply SQL Server TOP Fetch Limit
Date Thu, 18 Jun 2009 02:12:07 GMT

     [ https://issues.apache.org/jira/browse/CAY-1244?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel
]

Malcolm Edgar updated CAY-1244:
-------------------------------

    Attachment: patch.txt

Patch against trunk

> 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
>         Attachments: patch.txt
>
>
> 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