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 ?
posted 9 years ago
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
// 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?
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.