[jboss-user] [Persistence, JBoss/CMP, Hibernate, Database] - setMaxResults(100) NE set rowcount 100

[arnieOag]

I have a database with 23,350,000+ records, 842,960 of which are for the city of Austin.

When I issue the following SQL through my IDE:


  | set rowcount 100
  | select * from state_card_holders where phys_adrs_city = 'AUSTIN'
  | 

The database responds in less than a second with the top 100 records.

When I run my Hibernate code:


  | session = sessFac.openSession();
  | searchCriteria = session.createCriteria(StateCardHolders.class)
  | .add(Restrictions.eq(fieldName, fieldValue))
  | .setMaxResults(100)
  | addOrder(Order.asc("nameLast")).addOrder(Order.asc("nameFirst"))
  | .list();
  | 

It takes 3 minutes. Clearly something is amiss and I suspect that the setMaxResults statement isn't resulting in a set rowcount 100 being issued by Hibernate, but rather its scanning the entire result set of 800+ records and discarding all but the first 100.

Which takes 3 minutes.

Is there not a way to make this more efficient? How do I tell Hibernate to tell Sybase to only return the top 100 records?

I could always revert back to using a Stored Procedure, but would rather not.

Thanks!


View the original post : http://www.jboss.com/index.html?module=bb&op=viewtopic&p=4118450#4118450

Reply to the post : http://www.jboss.com/index.html?module=bb&op=posting&mode=reply&p=4118450