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