Hibernate ScrollableResults Do Not Return The Whole Set of Results

Question

Some of the queries we run have 100'000+ results and it takes forever to load them and then send them to the client. So I'm using ScrollableResults to have a paged results feature. But we're topping at roughly 50k results (never exactly the same amount of results).

I'm on an Oracle9i database, using the Oracle 10 drivers and Hibernate is configured to use the Oracle9 dialect. I tried with the latest JDBC driver (ojdbc6.jar) and the problem was reproduced.

We also followed some advice and added an ordering clause, but the problem was reproduced.

Here is a code snippet that illustrates what we do:

final int pageSize = 50;
Criteria crit = sess.createCriteria(ABC.class);
crit.add(Restrictions.eq("property", value));
crit.setFetchSize(pageSize);
crit.addOrder(Order.asc("property"));
ScrollableResults sr = crit.scroll();
...
...
ArrayList page = new ArrayList(pageSize);
do{
  for (Object entry : page)
    sess.evict(entry); //to avoid having our memory just explode out of proportion
  page.clear();
  for (int i =0 ; i < pageSize && ! metLastRow;  i++){
    if (sr.next())
      page.add(sr.get(0));
    else 
      metLastRow = true;
  }
  metLastRow = metLastRow?metLastRow:sr.isLast();

  sendToClient(page);
}while(!metLastRow);

So, why is it that I get the result set to tell me its at the end when it should be having so much more results?

Answer 1

Your code snippet is missing important pieces, like the definitions of resultSet and page. But I wonder anyway, shouldn't the line

if (resultSet.next())

be rather

if (sr.next())

?

As a side note, AFAIK cleaning up superfluous objects from the persistence context could be achieved simply by calling

session.flush();
session.clear();

instead of looping through the collection of object to evict each separately. (Of course, this requires that the query is executed in its own independent session.)

Update: OK, next round of guesses :-)

Can you actually check what rows are sent to the client and compare that against the result of the equivalent SQL query directly against the DB? It would be good to know whether this code retrieves (and sends to the client all rows up to a certain limit, or only some rows (like every 2nd) from the whole resultset, or ... that could shed some light on the root cause.

Another thing you could try is

crit.setFirstResults(0).setMaxResults(200000);