openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Vermeulen (JIRA)" <j...@apache.org>
Subject [jira] [Commented] (OPENJPA-1991) Support ROW_NUMBER pagination on MS SQL Server 2005
Date Fri, 04 Oct 2013 13:36:43 GMT

    [ https://issues.apache.org/jira/browse/OPENJPA-1991?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13786149#comment-13786149
] 

Vermeulen commented on OPENJPA-1991:
------------------------------------

This definitely has my vote. When I use setMaxResults the generated SQL doesn't even use TOP
and fetches all results and filters in memory. A not very optional workaround I can use is
selecting id's first using setMaxResults and then select the entities with these id's. This
means all id's are still fetched, but in my use case performance is acceptable.

> 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 was sent by Atlassian JIRA
(v6.1#6144)

Mime
View raw message