Hi There!

I have got a problem with Hibernate when selecting a small data set out of a large amount of ordered data.
Please see the following scenario:

Java 5
Spring 2
Hibernate Version: 3.1.2
Oracle 10g

Database Table: Customer (with 1.000.000 records)

I want to select records 990.000 to 991.000 (1.000 records) out of the ordered table.


Customer Object:
@Entity
public class Customer extends AbstractPersistentObject {

private static final long serialVersionUID = 7252605547210663022L;

private String name;

private String type;

private int marketstatus;

/**
* Returns marketstatus
* @return Returns the marketstatus.
*/
public int getMarketstatus() {
return marketstatus;
}

/**
* Set marketstatus
* @param marketstatus The marketstatus to set.
*/
public void setMarketstatus(int marketstatus) {
this.marketstatus = marketstatus;
}

/**
* Returns name
* @return Returns the name.
*/
public String getName() {
return name;
}

/**
* Set name
* @param name The name to set.
*/
public void setName(String name) {
this.name = name;
}

/**
* Returns type
* @return Returns the type.
*/
public String getType() {
return type;
}

/**
* Set type
* @param type The type to set.
*/
public void setType(String type) {
this.type = type;
}

}

AbstractPersistentObject only defines an ID and Version field (should not be relevant here).

Configured Hibernate with Spring.

Implemented a Service with the following Methods (just tried out some different methods of getting the data WITHOUT an ORDER BY):

public Collection<Customer> getOrderCustomersWithSpringTemplate1() {
return getHibernateTemplate().
findByCriteria(DetachedCriteria.forClass(Customer.class),
990000, 1000);
}

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_ ) row_ ) where rownum_ <= ? and rownum_ > ?
Processing Time on Database itself: 282 milliseconds (ms)
Processing Time in Application: 912 milliseconds (ms)



public Collection<Customer> getOrderCustomersWithSpring1() {
Collection<Customer> customers = null;
customers = (Collection<Customer>)
getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws
HibernateException {
Query query = getSession().createQuery("from Customer");
query.setMaxResults(1000);
query.setFirstResult(991000);
return query.list();
}
});
return customers;
}

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_ ) row_ ) where rownum_ <= ? and rownum_ > ?
Processing Time on Database itself: 282 ms
Processing Time in Application: 973 ms




public Collection<Customer> getOrderCustomersWithSpring2() {
Collection<Customer> customers = null;
customers = (Collection<Customer>)
getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws
HibernateException {
Criteria criteria= getSession().createCriteria(Customer.class);
//criteria.addOrder(Order.asc("id"));
criteria.setFirstResult(990000);
criteria.setMaxResults(1000);
return criteria.list();
}
});
return customers;
}

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_ ) row_ ) where rownum_ <= ? and rownum_ > ?
Processing Time on Database itself: 281 ms
Processing Time in Application: 556 ms



Now I tried the same thing with the ordered data (as only that makes sense) and HERE IS THE PROBLEM. I implemented a Service with the following Methods (just tried out some different methods of getting the data WITH an ORDER BY):

public Collection<Customer> getOrderCustomersWithSpringTemplate1() {
return getHibernateTemplate().
findByCriteria(DetachedCriteria.forClass(Customer.class).addOrder(Order.asc("id")),
990000, 1000);
}

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: 14149 milliseconds (ms)



public Collection<Customer> getOrderCustomersWithSpring1() {
Collection<Customer> customers = null;
customers = (Collection<Customer>)
getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws
HibernateException {
Query query =
getSession().createQuery("from Customer order by id");
query.setMaxResults(1000);
query.setFirstResult(991000);
return query.list();
}
});
return customers;
}

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: 12489 ms




public Collection<Customer> getOrderCustomersWithSpring2() {
Collection<Customer> customers = null;
customers = (Collection<Customer>)
getHibernateTemplate().executeFind(new HibernateCallback() {
public Object doInHibernate(Session session) throws
HibernateException {
Criteria criteria= getSession().createCriteria(Customer.class);
criteria.addOrder(Order.asc("id"));
criteria.setFirstResult(990000);
criteria.setMaxResults(1000);
return criteria.list();
}
});
return customers;
}

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: 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.

Can anyone tell me what the problem is with large datasets and the ORDER BY function?

By the way, this slow processing does not occur if the table has only 100.000 records, it must have something to do with large datasets!


Thanks in advance

RF

This seems like an Oracle related question to me, not a Hibernate question. I'm willing to bet if you run the SQL directly without Hibernate you'll see the same poor performance.

Anyways, have you tried:

1. Increasing your sort area size.
2. Adding an index to the column being sorted on.

Hi Sneal!

Like the measurements (processing time at the database) in my post show, the processing on the database (this is actually the time when I execute the statement on the database) only takes HALF the TIME than running the query via Hibernate.

And:
1) Increasing the sort area size does not work in my case (sorting all data is a requirement).
2) I already have an index on the ID column.

Any other tips?

Thanks in advance!

RF