Pagination Large Result Set setFirst/Max vs. Scrollable

TomAtoms · **Joined:** Tue Sep 04, 2007 2:21 pm **Posts:** 14

Hi All,

I'm working on pagination with some large results sets (1M+) in DB2. My initial thought was that a scrollable result set would perform better than setting the first and max row.

My thinking was that towards the end of the result set (first row = 100K max row = 50) DB2 would be sending me the first 100K rows over the network. A Scrollable result set would let me position a cursor at row 100K and get the next 50 - reducing the network traffic.

What I found was that setting the first and max row actually performed better and that I don't get the first 100K rows over the network.

My question: Is this conclusion correct? When using DB2 does Hibernate use a cursor (or some other magic) under the covers to implement the first and max row? Is there ever a reason to try scrollable result set over first and max row for pagination performance?

Thanks
Tom

TomAtoms · **Joined:** Tue Sep 04, 2007 2:21 pm **Posts:** 14

Answering my own question for future searchers....

Yes, Hibernate does use a scrollable cursor for first / max row queries when the db dialect supports it...I sumized this after reviewing the following classes...

Code:

org.hibernate.loader.Loader

line: 1791
if ( !dialect.supportsLimitOffset() || !useLimit( selection, dialect ) ) {
   advance( rs, selection );
}

/**
 * Advance the cursor to the first required row of the <tt>ResultSet</tt>
 */
private void advance(final ResultSet rs, final RowSelection selection)
      throws SQLException {

   final int firstRow = getFirstRow( selection );
   if ( firstRow != 0 ) {
      if ( getFactory().getSettings().isScrollableResultSetsEnabled() ) {
         // we can go straight to the first required row
         rs.absolute( firstRow );
      }
      else {
         // we need to step through the rows one row at a time (slow)
         for ( int m = 0; m < firstRow; m++ ) rs.next();
      }
   }
}
____________________________________________________________________________________
org.hibernate.dialect.DB2390Dialect

public boolean supportsLimit() {
   return true;
}

public boolean supportsLimitOffset() {
   return false;
}

diduknow · **Joined:** Wed Jun 21, 2006 2:25 pm **Posts:** 15

TomAtoms wrote:

Hi All,
What I found was that setting the first and max row actually performed better and that I don't get the first 100K rows over the network.

My question: Is this conclusion correct?

I think so. On our application we also have a need to paginate large results and found that setting the first and max rows to be far more helpful. I guess the rule of the thumb here would be if the database server can do it so be it.

anand raman

Ishita · **Joined:** Thu Feb 17, 2011 3:03 am **Posts:** 2

Hi all,

I am facing a little problem with my pagination. My framework is Hibernate & backend is MySQL.
Consider the following scenario :

1.We have 45 records in view jobs.
2.We have 2 different (HTTP) OpsHub sessions opened in a browser.
3.Page size is 10 records per page.
4.First user is currently viewing 2nd page, i-e 11-20 records.
5.Second user is also viewing same page as first user & he deletes any record
from the same.
6.Again, second user traverse and moves to 4th page & deletes some record from
4th page.

Issue:

The issue is when first user comes in picture. He is still on the 2nd page
viewing 11-20 records. Now when he do next & views 3rd page, he wont be seeing
the 1 record which was originally on 3rd page but get replaced on 2nd page due
to deletion.
This is happening because on navigation, we are ordering all the records once
again.

How Scrollable cursors can help me out with this?
Pls reply...

hallmit · **Posted:** Thu Feb 17, 2011 10:46 am

Hi Ishita,

I believe you are facing a classical problem known as "query isolation". Because you issue several queries against the database to fetch only rows comprised in one page you aren't aware of what happens in meantime. This problems appears as well for added rows (this behaviour provokes "page drift").

I don't think that scrollables results can help you in a web application because this will imply that the cursor has to keep opened (using SCROLL_INSENSITIVE mode) in order to provide next pages without reflecting changes made by others. I fear it will be very expensive.
Maybe some solutions for your case:
- retain all rows in memory (like ValueList or DisplayTag - not reasonable for a large result)
- create a temp "static view" for each query with all result but fetching by chunks
- loads all id in memory and don't delete rows (only flags them with "deleted"). When user ask for a new page, fetch rows within a page by id (take at look at http://www.ilikespam.com/blog/paging-la ... a-lazylist).
- or try, but is complicated, to adjust the offset of rows dynamically in order to display "pulled" or "drifted" rows correctly.

I'm not convinced that there is a perfect solution. It depends for each use case.