title: Performance issues of Spring data paging with hibernate and SQL Server 2014
body: <p>I'm trying to implement a server-side paging in spring using a <code>PagingAndSortingRepository</code>, hibernate and SQL Server 2014. It is working fine, but something seems to be off with the performance. 
  The further the page is, the longer time it takes to load it.</p>
  
  <p>The query logged by the hibernate is the following (I've removed most of the column names):</p>
  
  <pre><code>WITH query 
     AS (SELECT inner_query.*,
        ROW_NUMBER() 
           OVER (
              ORDER BY CURRENT_TIMESTAMP) as __hibernate_row_nr__ 
     FROM ( select TOP(?) incidentli0_.IncidentId as Incident1_18_ 
  FROM IncidentList incidentli0_ order by incidentli0_.IncidentId desc 
  ) inner_query ) 
  SELECT Incident1_18_
  FROM query
  WHERE __hibernate_row_nr__ &gt;= ? AND __hibernate_row_nr__ &lt; ?
  </code></pre>
  
  <p>According to <a href="https://stackoverflow.com/a/21629055/6311325">this answer</a> that is the correct way the paging query should look like for the SQL Server.</p>
  
  <p>When there are 400 000 rows in the table, the query takes around 1.2 sec  for the last page and around 60ms for the first page.</p>
  
  <p>Executing the query with Eclipse's SQL client takes a couple of ms no mather what page am I querying for.</p>
  
  <p>I'm using <em>4.3.10.Final</em> version of hibernate and the <em>1.10.2.RELEASE</em> version of spring-data-jpa.</p>
  
  <p>Do you know what may be causing such performance issues, and how to  fix it?</p>