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
_______________________________________________
jboss-user mailing list
[email protected]
https://lists.jboss.org/mailman/listinfo/jboss-user
