Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

pagination via ScrollableResults or via setFirstResult

 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have a few questions about pagination via ORM (Hibernate/JSF/EJB3):

Should I use ScrollableResults (if supported by the database)
or should I prefere using pagination via .setFirstResult/.setMaxResult.

What is better (performance and the like) ?

According to the hibernate-manual (chapter 10.4.1 Scrollable iteration):

Note that an open database connection (and cursor) is required for this functionality, use setMaxResult()/set-
FirstResult() if you need offline pagination functionality.


When do I need offline pagination?

Does anyone know, how the pagination of this forum is made? Via ScrollableResults ?
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using .setFirstResult/.setMaxResult and found out that the time having to wait to fetch the data and view this data in my h:datatable
remains the same in these scenarios:


// fetching 500 records, needs 20 seconds


and this:

// fetching only 50 records, needs ALSO 20 seconds



Fetching 100, 150, 200, 400, 1000 or even 10000 records needs also approx. 20 seconds!! Normally, the smaller the fetch-size, the shorter to wait.
But this in not in my case. Strange..
Does anyone know, why?

How can I improve this?
Even
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What database/driver are you using? Not all provide mechanisms for Hibernate to use to provide paging, so worst case scenario Hibernate will have to select every record then limit.
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use db2 and hibernate generates this sql:


 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I know that db2 supports ScrollableResults (I have tried it and it worked), but I do not use ScrollableResults , I use .setFirstResult/.setMaxResult for paging.
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have also tried it with this scenario.




// fetching 1 (!) record, needs 20 seconds


strange..
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There you go. DB2 doesn't have good pagenation support so the query is selecting everything into a temp table then limiting.

Normally, I would expect ScrollableResults to be slower, since a lot more will be going on to make the results scrollable. setFirstResult/setLastResult gets the database to do some of the work so should be quicker - at least, the Hibernate part should.
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator


But why Hibernate does not use the SQL-Keyword LIMIT ? DB2 does not support good pagenation?

That s really pitty, a shame for db2. So MySQL provides better pagination? Gosh, MySQL can more than DB2, a shame for db2:-)


Normally, I would expect "ScrollableResults" to be slower, since a lot more will be going on to make the results scrollable.


When I use ScrollMode.FORWARD_ONLY and only attemp records within a range via "resultSet.get(index)"?


setFirstResult/setLastResult gets the database to do some of the work so should be quicker - at least, the Hibernate part should.


What shall I do, I cannot wait 20 seconds only for fetching one records. How can I improve the pagination?
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
MySQL has the Keyword OFFSET and IBM cannot counter with that?



IBM has nothing to provide good pagination - it is unbelievable!
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have found this:

http://devenphillips.blogspot.com/2008/07/emulating-limit-and-offset-on-db2-for.html as an alternative to OFFSET.

hmm..but I have to use native SQL instead of HQL and want to avoid this.

Are there any ways to do that with HQL?
 
Paul Sturrock
Bartender
Posts: 10336
Eclipse IDE Hibernate Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

But why Hibernate does not use the SQL-Keyword LIMIT

LIMIT is not a SQL keyword. It is a MySQL keyword. So Hibernate can only use it with MySQL.

DB2 is not alone in not having good pagination support.


I have found this:

http://devenphillips.blogspot.com/2008/07/emulatin...mit-and-offset-on-db2-for.html as an alternative to OFFSET.

hmm..but I have to use native SQL instead of HQL and want to avoid this.

Are there any ways to do that with HQL?

No. But you could do it by defining the same logic as a procedure and calling that from Hibernate. It still means you have a DB2 dependency in your code however.
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Okay, but would this procedure makes sense?

The procedure contains the rownumber()-function, too. So I guess, it would not make any difference using q.setMaxResults(), q.setFirstResult().

Don't you think so?
 
nimo frey
Ranch Hand
Posts: 580
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The main problem is, how to say my cursor the starting point:

The setFirstResult(10000) and setMaxResults(100) slows the ScrollableResults considerably down:


When I use this instead, I can substitue the setMaxResults(100) and the ScrollableResults is really fast.



But the main problem is: To start the cursor at a particualar row without using the time-cost-function setFirstResult().

Any ideas?
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic