休眠和SQL Server 2014的春季数据分页的性能问题

Question

我试图在春季使用PagingAndSortingRepository，休眠和SQL Server 2014实现服务器端分页。它工作正常，但东西似乎关闭表现。 页面越远，加载时间就越长。

由休眠记录的查询如下（我已经去除了大部分列名）：

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__ >= ? AND __hibernate_row_nr__ < ? 

根据this answer这是正确的方式分页查询应该像为SQL Server 。

当表格中有400000行时，查询最后一页约需1.2秒，第一页约需60ms。

使用Eclipse的SQL客户端执行查询需要几秒钟，不管我查询哪个页面。

我使用4.3.10.Final版本休眠和1.10.2.RELEASE版本弹簧数据的JPA的。

您是否知道可能导致此类性能问题的原因以及如何解决？

Answer 1

在此查询中，您要求SQL对前N行进行编号，然后返回编号为N-M至N的行。随着N增加，SQL必须扫描并编号更多行，导致成本增加。

为了最大限度地减少编号的成本，在排序列（这里是IncidentID）上提供了一个“瘦”索引。如果IncidentiID是一个集群主键，那么SQL将扫描包含非键列的所有数据页面以执行编号步骤。