8

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...

Community
  • 1
  • 1
Jan Groth
  • 14,039
  • 5
  • 40
  • 55

3 Answers3

9

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!

Miljen Mikic
  • 14,765
  • 8
  • 58
  • 66
4

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:

MarnixKlooster ReinstateMonica
  • 9,640
  • 14
  • 54
  • 108
  • Performance enhanced nicely when using specific dialect according to engine version. Tested on SQL Server 2008 with 70 000 results, execution time reduced by almost 5 times when reaching last page – yodamad Mar 17 '15 at 16:36
2

This is because there is no real paging implementation provided by MSSQL for this version. Indeed there the possibility to use an inner query with Row_Number() but like you mentioned, Hibernate doesn't use this.

I've found that certain persons have modified the SQLServer2008Dialect.java to make it use the Row_Number(). follow this link

EDIT: (I've noticed the source of these files are no longer available)

In MSSQL2012 this problem should be resolved because they implemented paging functionalities. The only problem remaining is that there is no specific dialect for this version of MSSQL. They suggest to use the old one (MSSQL2008) but this will result in the same paging problems.

mahieus
  • 580
  • 3
  • 17