[hibernate-issues] [Hibernate-JIRA] Created: (HHH-2349) Hibernate Query problem with ORER BY, Pagination, Oracle and large datasets

[Ronny Fries (JIRA)]

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

         Key: HHH-2349
         URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-2349
     Project: Hibernate3
        Type: Bug

  Components: query-sql  
    Versions: 3.1.2    
 Environment: Java5
Spring 2
Hibernate 3.1.2
Oracle 10g R2
    Reporter: Ronny Fries
 Attachments: custtest.zip

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!

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://opensource.atlassian.com/projects/hibernate/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira