pagination via ScrollableResults or via setFirstResult

[nimo frey]

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]

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

private List<User> fetchUser() {

Query q = session.createQuery("from User u");
q.setMaxResults(500);
q.setFirstResult(0);
}

and this:

// fetching only 50 records, needs ALSO 20 seconds

private List<User> fetchUser() {
Query q = session.createQuery("from User u");
q.setMaxResults(100);
q.setFirstResult(0);
}

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]

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]

I use db2 and hibernate generates this sql:

select
        * 
    from
        ( select
            rownumber() over() as rownumber_,
            user_.ID_USER as ID1_109_,
            user_.NAME as ID2_109_,
            user_.AGE as itemid109_,
        from
            MYSCEME.USER user0_ ) as temp_ 
    where
        rownumber_ <= ?

[nimo frey]

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]

I have also tried it with this scenario.




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

private List<User> fetchUser() {  
 
Query q = session.createQuery("from User u");  
q.setMaxResults(1);  
q.setFirstResult(0);  
}  

strange..

[Paul Sturrock]

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]

There you go. DB2 doesn't have good pagenation support so the query is selecting everything into a temp table then limiting.

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]

MySQL has the Keyword OFFSET and IBM cannot counter with that?

SELECT column FROM table
LIMIT 100 OFFSET 200

IBM has nothing to provide good pagination - it is unbelievable!

[nimo frey]

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]

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]

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]

The main problem is, how to say my cursor the starting point:

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

ScrollableResults s= s.createQuery("from User a ").setFetchSize(100).setFirstResult(10000).setMaxResults(100).setCacheMode(CacheMode.IGNORE).scroll(ScrollMode.FORWARD_ONLY); 

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

ScrollableResults s= s.createQuery("from User a").setCacheMode(CacheMode.IGNORE).scroll(ScrollMode.FORWARD_ONLY);

int scroll=0;
while (s.next() && scroll<100) {

//add to list
...

//clear cache
...

scroll++;
}

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

Any ideas?