Hibernate Query problem with ORER BY, Pagination, Oracle and large datasets

Description

Hibernate has a problem when selecting a small data set out of a large amount of ORDERED data.
Please see the following scenario:

Please see the attached sources, if you add them to an eclipse project edit the database.properties file and also add the correct libraries (hibernate, spring, oracle ....), you should be able to run the JUnit Test CSTest.java

The first test inserts 1.000.000 records into a customer table on the database (you can edit the number of loops to manipulate the number of entered records.
The following three test cases try to select record 990000 to 991000 out of the ORDERED dataset in three different ways.
Here are the results that I got when measuring the processing time (i measured the java app exec time and in contrast the time when executing the "generated" statement on the database itself):

getOrderCustomersWithSpringTemplate1
Generated Statement:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_, this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_, this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ ) where rownum_ <= ? and rownum_ > ?
Processing Time on Database itself: 6101 milliseconds (ms)
Processing Time in Application (including database access): 14149 ms

getOrderCustomersWithSpring1
Generated Statement:
select * from ( select row_.*, rownum rownum_ from ( select customer0_.id as id3_, customer0_.version as version3_, customer0_.name as name3_, customer0_.type as type3_, customer0_.marketstatus as marketst5_3_ from Customer customer0_ order by customer0_.id ) row_ ) where rownum_ <= ? and rownum_ > ?
Processing Time on Database itself: 4023 ms
Processing Time in Application (including database access): 12489 ms

getOrderCustomersWithSpring2
Generated Statement:
select * from ( select row_.*, rownum rownum_ from ( select this_.id as id3_0_, this_.version as version3_0_, this_.name as name3_0_, this_.type as type3_0_, this_.marketstatus as marketst5_3_0_ from Customer this_ order by this_.id asc ) row_ ) where rownum_ <= ? and rownum_ > ?
Processing Time on Database itself: 5210 ms
Processing Time in Application (including database access): 13022 ms

When switching on debugging, it can be seen that Hibernate halts at the following line in any of the above cases (this seems to be the time that is missing between the database processing and the application processing):
2006-12-21 13:58:17,184 [main] DEBUG org.hibernate.jdbc.AbstractBatcher - preparing statement
After some seconds processing starts again.

In contrast, if you comment out the ORDER BY in the Customer Service, the time difference between database exec time and java app exec time is minimal (the problem disappears).
And if you execute one of the above "generated" statements with the following method the problem DOES NOT occur even with the ORDER BY.

public Collection<Customer> getOrderCustomersWithSpring3() {
Collection<Customer> customers = null;
customers = (Collection<Customer>) getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws HibernateException {
Collection<Customer> customers = session.createSQLQuery("...").
addEntity(Customer.class).list();
return customers;

}
});
return customers;
}

Also, if you have a table with only 100.000 records the ORDER BY problem does not occur. This seems to be one of the big problems.
Of course, the statement processing time is less when querying 1.000 records out of 100.000 but the halt in the processing in hibernate when it comes to larger datasets seems to be pretty strange (and wrong) behavior.

Thanks in Advance for checking into this issue!

Environment

Java5
Spring 2
Hibernate 3.1.2
Oracle 10g R2

Attachments

Assignee

Unassigned

Reporter

Ronny Fries

Fix versions

None

Labels

None

backPortable

None

Suitable for new contributors

None

Requires Release Note

None

Pull Request

None

backportDecision

None

Components

Affects versions

Priority

Major
Configure