[CAY-1244] Apply SQL Server TOP Fetch Limit

• Type: Improvement
• Status: Closed
• Priority: Critical
• Resolution: Fixed
• Affects Version/s: 3.0M5
• Component/s: Core Library
• Assignee: Malcolm Edgar
• Reporter: Malcolm Edgar
• Created: 17/Jun/09 14:13
• Updated: 11/Nov/09 08:23
• Resolved: 11/Nov/09 08:23

Description

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