Hibernate pagination : ScrollableResult vs. setMaxResult() + setFirstResult()

3
votes

I've been using ORM frameworks for a while but I am rather new to Hibernate, though.

Suppose you have a query (is it a Query or a Criteria, does not matter) that retrieves a great result set and that you want to paginate though it. Would you rather use the setMaxResult() and setFirstResult() methods combo, or a ScrollableResult?

Which is the best approach regarding the performances (execution time AND memory consumption)?

2
javahibernate

2 Answers

2
votes

If you are implementing a Web application that serves separate pages of results in separate request-response cycles, then there's no way you can use ScrollableResult to any advantage. Use setFirst/Max/Result. However, this can be a real performance killer, depending on the exact query and the total size of the result. Especially if the poor db must sort the whole result set every time so it can calculate what are the 100-110th records.

1
votes

We had the same questions the other day, and settled for setMaxResult(..) and setFirstResult(..). The problems are two:

  • ScrollableResult may execute one query for each call to next() if your jdbc driver or database are not handling it properly. This was the case with us (MySQL)
  • it is hibernate-specific, rather than JPA standard.