title: Hibernate Pagination using HQL
body: <p><strong>Hibernate Pagination Issue</strong></p>
  
  <p>I have an issue which is related to Hibernate Pagination and to some extent this has been explained in </p>
  
  <p><a href="http://www.mysqlperformanceblog.com/2008/09/24/four-ways-to-optimize-paginated-displays/" rel="nofollow noreferrer">Mysql Pagination Optimization</a></p>
  
  <p><a href="https://stackoverflow.com/questions/2826319/using-hibernates-scrollableresults-to-slowly-read-90-million-records">Using Hibernate&#39;s ScrollableResults to slowly read 90 million records</a></p>
  
  <p><a href="https://stackoverflow.com/questions/6348143/hibernate-hql-pagination">Hibernate - HQL pagination</a></p>
  
  <p><a href="https://stackoverflow.com/questions/1030924/issues-with-pagination-and-sorting">Issues with Pagination and Sorting</a></p>
  
  <p><a href="https://stackoverflow.com/questions/489360/hql-row-identifier-for-pagination">Hibernate Row Pagination</a></p>
  
  <p><strong><em>Details</em></strong></p>
  
  <p><strong>HQL Query from Application:</strong></p>
  
  <pre><code>Query q = session.createQuery("from RequestDao r order by r.id desc");
              q.setFirstResult(0);
              q.setMaxResults(50);
  </code></pre>
  
  <p>Query returns 3 million records and for pagination we are setting only 50 of those records, pagination page is very slow because on every refresh we are calling the query which get 3 millions records and out of those we only set 50 records. </p>
  
  <p>My main question is </p>
  
  <blockquote>
    <p>Does HQL always goes and hits database or does it go and hit session or memory to look for the data and if it goes everytime to hit database and get resultset then it is very proper from performance point of view, what would be best solutions to improve it?</p>
  </blockquote>
  
  <p>Using HQL in hibernate is there a way we can query database and get only 50 records out first and then get other records as required by the user. This challenge is really bogging down application and so what would be best way to solve this problem?</p>
  
  <p><strong>HQL Query generated in logs</strong></p>
  
  <pre><code>from com.delta.dao.RequestDao r order by r.id desc
  </code></pre>
  
  <p><strong>Hibernate Generated Query</strong></p>
  
  <pre><code>select
      getrequest0_.ID as ID24_,
      getrequest0_.TIME as START3_24_,
      getrequest0_.STAT as STATUS24_,
      getrequest0_.SUM as SUMMARY24_,
      getrequest0_.OUTNAME as OUTPUT7_24_,
      getrequest0_.INPNAME as INPUT8_24_,
      getrequest0_.REQUEST_DATE as requestT9_24_,
      getrequest0_.PARENT_ID as PARENT10_24_,
      getrequest0_.INTER_TYPE as INTERPO60_24_,
      getrequest0_.OPEN_INT as OPEN61_24_,
      getrequest0_.SOURCE_TYPE as SOURCE62_24_,
      getrequest0_.TARGET_TYPE as TARGET20_24_,
      getrequest0_.SOURCE as SOURCE14_24_,
      getrequest0_.COPY_DATA as COPY16_24_,
      getrequest0_.CURVE as GENERATE63_24_,
      getrequest0_.TITLE as TITLE24_,
      getrequest0_.TIME_ID as TIMESERIES12_24_,
      getrequest0_.TASK_NAME as TASK51_24_ 
  from
      REQUEST getrequest0_ 
  where
      getrequest0_.KIND='csv' 
  order by
      getrequest0_.ID desc
  </code></pre>
  
  <p>Here is the <strong>Explain Plan</strong> for the query:</p>
  
  <hr>
  
  <pre>
   | id | select_type | table        | type | possible_keys  | key        | key_len | ref          |  rows    | filtered | Extra       | 
   |  1 | SIMPLE      | getrequest0_ | ref  | TR_KIND_ID     | TR_KIND_ID | 6       | const        | 1703018  |   100.00 | Using where |
  </pre>
  
  <hr>
  
  <p><strong><em>Additional information: Query run time with and without order by clause on 50 records limit</em></strong></p>
  
  <hr>
  
  <p>If i run query <code>with order</code> clause then query takes <strong><em>0.0012s</em></strong> with setting <code>LIMIT 50</code> and <code>without order</code> clause, same query takes <strong><em>0.0032s</em></strong> with same <code>LIMIT 50</code>. </p>
  
  <hr>
  
  <p>Also how can we find if:</p>
  
  <ol>
  <li>Particular HQL Query is hitting database and not cache or getting information from session?</li>
  <li>Is it true that HQL Query will always go and hit database to get result out and Criteria would go and hit session or cache and get results from it?</li>
  <li><p>Also in my below mentioned query:</p>
  
  <pre><code>a) Query q = session.createQuery("from RequestDao r order by r.id desc");
  b) q.setFirstResult(0);
  c) q.setMaxResults(50);
  </code></pre></li>
  </ol>
  
  <p>at a, is it true that we get result from database and store it in memory or where if not and at this time we have 3 million results in result set and then at b and c we set offset value and limit so on page we would only see 50 results so now where are remaining 3 million records and on our second call to this query do we again go and hit database and get 3 million records and put them in memory and then at c again we set 50 records and go on an on. </p>
  
  <p>This issue is not clear to me and so would highly appreciate if someone can provide clear and detailed explanation as how this is working and what would be best solution for this problem.</p>
  
  <p><strong>Update</strong></p>
  
  <p>As it turns out, issue am having is not related to display of records on the page but i have filter on that page and on every request am getting all drop down values again from database and there are some funky things going on in there that is causing rise in page load time. </p>
  
  <p>I am making multiple nested hibernate queries to database and getting results back, what would be an optimal solution for this problem?</p>