Geek Answers Handbook

Hibernate (/ JPA) server side and MS SQL Server

I implement on the server

with hibernate / JPA, MS SQL Server 2008 core database.

SQL is created as follows:

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

(The main work is to create the appropriate filter / sort, but this is not relevant here)

What I am 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 encounter serious problems if the basic set of results becomes too large and (b) is not particularly relevant to swapping at all: - (

Anyone who had the same problem?

Update . NHibernate (an implementation of Hibernate.NET) seems to take advantage of the Row_Number() function of T-SQL. Too bad Hibernate doesn't ...

+7
source share
3 answers

A bit late reply, but it may be useful, so I will post it. Had the exact same problem and headache to track it. The solution is to use org.hibernate.dialect.SQLServer2012Dialect , which is included in Hibernate 4.3.0. The generated query becomes (inserting a 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__ < ?

Note the use of internal query and Row_number() . Finally they decided!

+8
source

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

My impression is that this performance issue disappears when using the combination Hibernate> = 3.5, setting hibernate.dialect to org.hibernate.dialect.SQLServer2005Dialect or org.hibernate.dialect.SQLServer2008Dialect using SQL Server> = 2005 and possibly also a driver SQL Server JDBC> = 3.0.

Additional links for backing up the above experience:

+4
source

This is because for this version there is no real swap implementation provided by MSSQL. In fact, it is possible to use an internal query with Row_Number (), but, as you mentioned, Hibernate does not use this.

I found that some people modified SQLServer2008Dialect.java to use Row_Number (). follow this link

EDIT: (I noticed that the source of these files is no longer available)

In MSSQL2012, this problem should be solved because they implemented swap functions. Only one problem remains: there is no special dialect for this version of MSSQL. They suggest using the old (MSSQL2008), but this will lead to the same swap problems.

+2
source

More articles:


All Articles