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.... - For more information on JIRA, see: http://www.atlassian.com/software/jira