Hibernate (/JPA) server-side paging and MS SQL Server

Question

I implementing a server-side data paging with hibernate / JPA, the underlying database is MS SQL Server 2008.

The SQL is generated like this:

criteria.setFirstResult(pagingParams.getDisplayStart())
.setMaxResults(pagingParams.getDisplayLength());

(The main work lays in creating the appropriate filter / sorting, but that's not relevant here)

What I'm observing is the following SQL:

page (0-20):
select top 20 this_.id as id11_9_,...

page (20-40):
select top 40 this_.id as id11_9_,... 

page (40-60):
select top 60 this_.id as id11_9_,... 

... and so on.

Obviously this (a) will run into serious issues if the underlying resultset gets too big and (b) hasn't much to do with paging at all :-(

Anyone who had the same issue?

Update: It seems as if NHibernate (the .NET implementation of Hibernate) takes advantage of the Row_Number() function of T-SQL. Pity that Hibernate doesn't...

Answer 1

A little bit late response, but it can be helpful so I will post it. Had the exactly same problem and a headache to track it down. Solution is to use org.hibernate.dialect.SQLServer2012Dialect which is included in Hibernate 4.3.0. Generated query becomes (pasting real Hibernate dump without column names and aliases):

WITH query 
     AS (SELECT inner_query.*, 
                Row_number() 
                  OVER ( 
                    ORDER BY CURRENT_TIMESTAMP) AS __hibernate_row_nr__ 
         FROM   (SELECT TOP(?) <COLUMN_NAMES> AS <ALIASES>
FROM <TABLE_NAME>
) inner_query) 
SELECT <ALIASES>
FROM   query 
WHERE  __hibernate_row_nr__ >= ? 
       AND __hibernate_row_nr__ < ?

Notice the usage of inner query and Row_number() function. They finally resolved it!

Answer 2

We also observed this same behavior with Hibernate 3.3 with hibernate.dialect=org.hibernate.dialect.SQLServerDialect (with SQL Server 2008 R2).

My impression is that this performance problem goes away when using the combination of Hibernate >= 3.5, setting hibernate.dialect to org.hibernate.dialect.SQLServer2005Dialect or org.hibernate.dialect.SQLServer2008Dialect, using SQL Server >= 2005 and probably also SQL Server driver JDBC >= 3.0.

Additional links to back up the above impression:

• Hibernate + Oracle already in 2005 supported pagination using row numbers: http://www.theserverside.com/discussions/thread.tss?thread_id=36894. So given that SQL Server >= 2005 has the Row_Number() function, I'm assuming that this is by now supported.
• Someone had a related problem in 2005 already, and upgrading to a newer JDBC SQL Server driver fixed that: https://forum.hibernate.org/viewtopic.php?f=1&t=987463