You are viewing a plain text version of this content. The canonical link for it is here.
Posted to dev@openjpa.apache.org by "Maia Kozheva (JIRA)" <ji...@apache.org> on 2011/05/03 09:18:03 UTC
[jira] [Created] (OPENJPA-1991) Support ROW_NUMBER pagination on MS
SQL Server 2005
Support ROW_NUMBER pagination on MS SQL Server 2005
---------------------------------------------------
Key: OPENJPA-1991
URL: https://issues.apache.org/jira/browse/OPENJPA-1991
Project: OpenJPA
Issue Type: Improvement
Components: jdbc
Affects Versions: 2.1.0
Reporter: Maia Kozheva
The current implementation of SQLServerDictionary in OpenJPA only supports pagination based on TOP (offset+limit), which fetches more rows than necessary and thus degrades performance in comparison to DBMSes that support both limit and offset specification, like MySQL.
SQL Server 2005 supports a ROW_NUMBER function that can be used to restrict the result list to the necessary slice, taking both limit and offset into account. I would like OpenJPA to support this syntax.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira
[jira] [Updated] (OPENJPA-1991) Support ROW_NUMBER pagination on MS
SQL Server 2005
Posted by "Maia Kozheva (JIRA)" <ji...@apache.org>.
[ https://issues.apache.org/jira/browse/OPENJPA-1991?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]
Maia Kozheva updated OPENJPA-1991:
----------------------------------
Attachment: SQLServer2005Dictionary.java
Attached is a custom class that we use in our project for this. It is based on existing code in OpenJPA's OracleDictionary (which aliases joined columns for subselect), but adjusted to the SQL Server syntax. It falls back to the TOP N syntax if the SQL Server version is too low to support ROW_NUMBER, or if offset == 0.
> Support ROW_NUMBER pagination on MS SQL Server 2005
> ---------------------------------------------------
>
> Key: OPENJPA-1991
> URL: https://issues.apache.org/jira/browse/OPENJPA-1991
> Project: OpenJPA
> Issue Type: Improvement
> Components: jdbc
> Affects Versions: 2.1.0
> Reporter: Maia Kozheva
> Attachments: SQLServer2005Dictionary.java
>
>
> The current implementation of SQLServerDictionary in OpenJPA only supports pagination based on TOP (offset+limit), which fetches more rows than necessary and thus degrades performance in comparison to DBMSes that support both limit and offset specification, like MySQL.
> SQL Server 2005 supports a ROW_NUMBER function that can be used to restrict the result list to the necessary slice, taking both limit and offset into account. I would like OpenJPA to support this syntax.
--
This message is automatically generated by JIRA.
For more information on JIRA, see: http://www.atlassian.com/software/jira