0 Replies Latest reply on Jan 9, 2008 5:51 PM by Arnie Morein

    setMaxResults(100) NE set rowcount 100

    Arnie Morein Newbie

      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!