Entitymanager Paging

amnon · **Joined:** Sat Oct 20, 2007 8:28 am **Posts:** 28

Hi ,
i've searched the forum but couldn't find any refrence to it .
as i udnerstand when using the paging mechanism of :
q = em.cretequerry
q.setmax
q.setfrstrslt

and using this mechanism for paging (from the documents i understand this is the way E.M is supposed to implement paging) i've noticed hibernate selects the entire results using TOP before up untill the last one it needs and itterates over them in memory ?? this seems to be very memory inefficent .
i've tried to implment it using ROW_NUMBER() but it's not as "pretty" , is there a better way to utilize the paging mechanisim and not load such a big bulk into memory when all u need is 50 rows (i have tables of thousands of rows and i only need 50 at a time and it's very likely user will sort on a single column and jump to page 56 ?
the question is asked using sql server syntax but i want to stay db independent .

hardy.ferentschik · **Posted:** Thu Jun 12, 2008 4:54 am

Hi,

the syntax is something along this lines (from the documentation):

Code:

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

Note that there is no standard way in SQL to express pagination. Hibernate tries to use the appropriate queries depending on the db. I am not sure how this looks like for SQLServer.

Why do you think that all rows up to 'maxresult - 1' get selected?

--hardy

kernel77 · **Joined:** Fri Jun 15, 2007 6:32 am **Posts:** 23

hardy.ferentschik wrote:

Hi,

the syntax is something along this lines (from the documentation):

Code:

Query q = sess.createQuery("from DomesticCat cat");
q.setFirstResult(20);
q.setMaxResults(10);
List cats = q.list();

Note that there is no standard way in SQL to express pagination. Hibernate tries to use the appropriate queries depending on the db. I am not sure how this looks like for SQLServer.

Why do you think that all rows up to 'maxresult - 1' get selected?

--hardy

there's no cross db way to do it but it seems it's an important enough requirment to be delt in each db optimaly .
i'm printing the queries and debugging and from what i've seen a on sql server it uses a top query with maxresult+firstresult and itterates over the previous records .

hardy.ferentschik · **Posted:** Fri Jun 13, 2008 4:17 am

Hi again,

I checked the code for the SQLServer dialect and as you say it is just limiting the query with TOP. SQLServer does not support a limit functionality. If you would switch to another database which supports it (PostgreSQL, MySQL, Oracle, ...) you would get the expected results.

How would your approach with ROW_NUMBER() look like? Note though that ROW_NUMBER() is a function new in SQLServer 2005. Currently there is only one single SQLServerDialect in Hibernate. If your approach is feasible one would have to split the dialects in order to integrate this approach. Maybe you want to continue this discussion on the Hibernate Core forum since it is not entity manager related?

Last but not least you could look into using scrollable resultsets. Have a look at these wiki pages:
- http://www.hibernate.org/314.html
- http://www.hibernate.org/248.html

If you are using the jTDS driver for SQLServer you might get a real performance boost.

--Hardy

amnon · **Joined:** Sat Oct 20, 2007 8:28 am **Posts:** 28

i've handled this issue before with hibernate specific solution like you suggested and they work very good but now i'm trying to stay vendor independent and not ask for the delegate .
this is why i asked it in E.M forum .

sanne.grinovero · **Posted:** Sat Jun 14, 2008 4:23 am

Quote:

i've handled this issue before with hibernate specific solution

I've had the same problem, unfortunately with SQL server 2000 (not-negotiable client requirement), and we couldn't find something better that using scrollables, and/or Lucene by using Search (depending on the kind of search).

How did you handle this before?
Are cursors mandatory?
Do you think it is possible to achieve on sql server 2000 too?