使用 IN 子句时 SQL Offset 总行数变慢

Question

我正在使用基于另一个答案的以下 SQL 代码。但是，当包含大量 in 子句时，获取总数需要太长时间。如果我删除总计数，则查询时间不到 1 秒。有没有更有效的方法来获得总行数？我看到的答案基于 2013 SQL 查询。

DECLARE 
    @PageSize INT = 10, 
    @PageNum  INT = 1;

WITH TempResult AS(
    SELECT ID, Name
    FROM Table
     Where ID in ( 1 ,2 3, 4, 5, 6, 7, 8, 9 ,10)
), TempCount AS (
    SELECT COUNT(*) AS MaxRows FROM TempResult
)
SELECT *
FROM TempResult, 
 TempCount    <----- this is what is slow. Removing this and the query is super fast
ORDER BY TempResult.Name
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY

Answer 1

性能相关问题的第一步将是分析您的表/索引结构，并审查查询计划。你还没有提供这些信息，所以我要自己编造，然后从那里开始。

我将假设您有一个堆，大约有 10M 行(对我来说是 12,872,738):

DECLARE @MaxRowCount bigint = 10000000,
        @Offset      bigint = 0;

DROP TABLE IF EXISTS #ExampleTable;
CREATE TABLE #ExampleTable
(
  ID   bigint      NOT NULL,
  Name varchar(50) COLLATE DATABASE_DEFAULT NOT NULL
);

WHILE @Offset < @MaxRowCount
BEGIN
  INSERT INTO #ExampleTable
  ( ID, Name )
    SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL )),
           ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL ))
      FROM master.dbo.spt_values SV
        CROSS APPLY master.dbo.spt_values SV2;
  SET @Offset = @Offset + ROWCOUNT_BIG();
END;

如果我运行通过 #ExampleTable 提供的查询，大约需要 4 秒，并给我这个查询计划:



无论如何，这都不是一个很好的查询计划，但也算不上糟糕。使用实时查询统计数据运行显示基数估计最多相差 1，这很好。

让我们在 IN 中提供大量项目列表(从 1 到 5000 的 5000 个项目)。编译计划花了 4 秒钟:



在查询处理器停止处理之前，我可以获得多达 15000 个项目，查询计划没有变化(编译总共需要 6 秒)。在我的机器上运行这两个查询需要大约 5 秒的时间。

这对于分析工作负载或数据仓库来说可能很好，但对于 OLTP 之类的查询，我们肯定已经超出了我们的理想时间限制。

让我们看看一些替代方案。我们可能可以结合使用其中的一些。

我们可以缓存 IN列出临时表或表变量。

我们可以使用窗口函数来计算计数

我们可以在临时表或表变量中缓存我们的 CTE

如果在足够高的 SQL Server 版本上，请使用批处理模式

更改表上的索引以使其更快。

工作流程注意事项

如果这是针对 OLTP 工作流，那么无论我们有多少用户，我们都需要快速的东西。因此，我们希望尽量减少重新编译，并且希望尽可能地进行索引查找。如果这是分析或仓储，那么重新编译和扫描可能没问题。

如果我们想要 OLTP，那么缓存选项可能不在表中。临时表将始终强制重新编译，并且依赖于良好估计的查询中的表变量需要您强制重新编译。另一种方法是让应用程序的其他部分维护一个具有分页计数或过滤器(或两者)的持久表，然后将此查询连接到它。

如果同一个用户会查看许多页面，那么即使在 OLTP 中缓存其中的一部分可能仍然值得，但请确保您衡量许多并发用户的影响。

不管工作流程如何，更新索引可能没问题(除非您的工作流程真的会干扰索引维护)。

无论工作流程如何，批处理模式都将是您的 friend 。

无论工作流程如何，窗口函数(尤其是索引和/或批处理模式)可能会更好。

批处理模式和默认基数估计器

使用传统的基数估计器和行模式执行，我们始终得到较差的基数估计(和结果计划)。强制使用默认基数估计器有助于第一个，批处理模式有助于第二个。

如果您无法更新数据库以批量使用新的基数估计器，那么您需要为特定查询启用它。为此，您可以使用以下查询提示: OPTION( USE HINT( 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' ) )得到第一个。对于第二个，向 CCI 添加一个连接(不需要返回数据): LEFT OUTER JOIN dbo.EmptyCciForRowstoreBatchmode ON 1 = 0 - 这使 SQL Server 能够选择批处理模式优化。这些建议假定 SQL Server 版本足够新。

CCI 是什么并不重要；我们喜欢保留一个空的以保持一致性，看起来像这样:

CREATE TABLE dbo.EmptyCciForRowstoreBatchmode
(
  __zzDoNotUse int NULL,
  INDEX CCI CLUSTERED COLUMNSTORE
);

我可以在不修改表格的情况下获得的最佳计划是同时使用它们。使用与以前相同的数据，这在 <1 秒内运行。

WITH TempResult AS
(
  SELECT ID,
         Name,
         COUNT( * ) OVER ( ) MaxRows
    FROM #ExampleTable
    WHERE ID IN ( <<really long LIST>> )
)
  SELECT TempResult.ID,
         TempResult.Name,
         TempResult.MaxRows
    FROM TempResult
      LEFT OUTER JOIN dbo.EmptyCciForRowstoreBatchmode ON 1 = 0
    ORDER BY TempResult.Name OFFSET ( @PageNum - 1 ) * @PageSize ROWS FETCH NEXT @PageSize ROWS ONLY
    OPTION( USE HINT( 'FORCE_DEFAULT_CARDINALITY_ESTIMATION' ) );