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!
