title: Best way to implement pagination in java
body: <p>I am using hibernate api's <code>query.setMaxResults(maxResults)</code> &amp; <code>query.setFirstResult(firstResult)</code> methods to implement pagination. <code>MS SQLServer 2012 sp1</code> is the DB that I am using for the project.</p>
  
  <p>Following is the sample query:</p>
  
  <pre><code>SELECT a.c1, b.c2, b.c3 FROM Person as a WITH(NOLOCK), Address as b WITH(NOLOCK) where &lt;condition&gt; ORDER BY a.c1
  </code></pre>
  
  <p>How does the <code>query.setMaxResults(maxResults)</code> &amp; <code>query.setFirstResult(firstResult)</code> methods work? Will it fetch all the data from DB and then filter it from the <code>result-set</code> based on first and max parameter? Or will it fetch only the number of result based on <code>max-result</code> value from the <code>first-result</code> index?  </p>
  
  <p>Users feel that the application is taking some time to update the UI with data. So which is the recommended way to implement pagination? Do I need to go with stored procedure or some other means to improve the performance? Kindly advice.</p>
  
  <p><strong>update:</strong>
  When user selects page 1 following query is executed:</p>
  
  <pre><code>SELECT top 200 a.c1, b.c2, b.c3 FROM Person as a WITH(NOLOCK), Address as b WITH(NOLOCK) where &lt;condition&gt; ORDER BY a.c1 - executed with 2502540 ms
  </code></pre>
  
  <p>When user selects page 30 following query is executed:</p>
  
  <pre><code>SELECT top 6000 a.c1, b.c2, b.c3 FROM Person as a WITH(NOLOCK), Address as b WITH(NOLOCK) where &lt;condition&gt; ORDER BY a.c1 - executed with 34220186 ms
  </code></pre>
  
  <p>So its clear that time is proportional to page number. Is there a way to maintain a constant execution speed regardless of page number? Since we always fetch same number of records.</p>
  
  <p>Thanks in advance.</p>