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 (including database access): 912 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 (including database access): 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 (including database access): 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 (including database access): 14149 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 (including database access): 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 (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.

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

Hi,

I'm having exactly the same problem.

The problem comes from this subselect, that appear when you use setMaxResults function.

When you do this on q dqtqbqse it tqke qges:
select * from ( select field from table order by field) where rownum_ <= ? and rownum_ > ?

but if you just do this it is ok:
select field from table where rownum_ <= ? and rownum_ > ? order by field

So how can we make hibernate generating this statments instead of the first one?

Thanks a lot by advance!

hello!

first
select field from table where rownum <= ? order by field
order only on the returned result! so that's not good.

So I think that you just need an index in the database on the field you want to order by.