前端之家

  1. 首页
  2. 问答

使用IN子句，SQL偏移总行数变慢 工作流程注意事项批处理模式和默认基数估计器

• 问答

我正在根据其他答案使用以下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
hessler2009 回答：使用IN子句，SQL偏移总行数变慢 工作流程注意事项批处理模式和默认基数估计器

据我所知，除了使用已经提到的#temp表方法之外，还有3种方法可以实现此目的。在下面的测试案例中，我使用了具有6CPU / 16GB RAM的SQL Server 2016 Developer实例，以及一个包含约2500万行的简单表。

方法1：交叉加入 

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

WITH TempResult AS (SELECT
                          id,shortDesc
                    FROM  dbo.TestName
                    WHERE id IN (1,2,3,4,5,6,7,8,9,10))
SELECT
           *,MaxRows
FROM       TempResult
CROSS JOIN (SELECT COUNT(1) AS MaxRows FROM TempResult) AS TheCount
ORDER BY   TempResult.shortDesc OFFSET (@PageNum - 1) * @PageSize ROWS 
FETCH NEXT @PageSize ROWS ONLY;

测试结果1：

enter image description here

方法2：COUNT（*）个OVER（） 

DECLARE
  @PageSize INT = 10,10)
)
SELECT
         *,MaxRows = COUNT(*) OVER()
FROM     TempResult
ORDER BY TempResult.shortDesc OFFSET (@PageNum - 1) * @PageSize ROWS
FETCH NEXT @PageSize ROWS ONLY;

测试结果2：

enter image description here

方法3：第二CTE

测试结果3（使用的T-SQL与问题中的相同）：

enter image description here

结论

最快的方法取决于您的数据结构（和总行数）以及服务器的大小/负载。以我为例，使用COUNT（*）OVER（）被证明是最快的方法。为了找到最适合您的方案，您必须测试最适合您的方案。并且也不排除#table方法还没有;-）

,

与性能相关的问题的第一步将是分析表/索引结构，并检查查询计划。您尚未提供这些信息，所以我将自己整理，然后从那里开始。

我将假设您有一个堆，其中有约1000万行（对我而言为12872738）：

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秒钟，并给出了此查询计划：

Baseline query plan

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

让我们在IN列表中提供大量商品（1-5000个商品中有5000个）。编制计划花了4秒钟：

Large IN list query plan

在查询处理器停止处理之前，我最多可以获取15000个编号，而查询计划没有任何变化（编译过程总共需要6秒钟）。在我的计算机上运行两个查询大约需要5秒钟。

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

让我们看看一些替代方案。我们可能可以将其中一些结合起来。

  1. 我们可以将IN列表缓存在临时表或表变量中。
  2. 我们可以使用窗口函数来计算计数
  3. 我们可以将CTE缓存在临时表或表变量中
  4. 如果在足够高的SQL Server版本上，请使用批处理模式
  5. 更改表上的索引以使其更快。

工作流程注意事项

如果这是用于OLTP工作流程，那么无论我们有多少用户，我们都需要快速的服务。因此，我们希望最大程度地减少重新编译，并且希望在任何可能的地方进行索引查找。如果这是分析或仓储，则重新编译和扫描可能很好。

如果我们需要OLTP，则缓存选项可能不在表格中。临时表将始终强制重新编译，而依赖良好估计的查询中的表变量要求您强制重新编译。替代方法是让应用程序的其他部分维护具有分页计数或过滤器（或两者都有）的持久表，然后对此进行联接。

如果同一个用户可以浏览许多页面，那么即使在OLTP中，缓存掉一部分页面还是值得的，但是请确保您衡量了许多并发用户的影响。

无论工作流程如何，更新索引都可以（除非您的工作流确实会破坏索引维护）。

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

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

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

通过传统的基数估计器和行模式执行，我们几乎总是得到差的基数估计（以及由此产生的计划）。强制使用默认基数估计量有助于第一个，而批处理模式则有助于第二个。

如果您不能更新数据库以使用新的基数估计器批发，则需要为特定查询启用它。为此，可以使用以下查询提示：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
);

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

Batch Mode and NCE 

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' ) );
,

您可以尝试使用以下措辞：

WITH TempResult AS(
      SELECT ID,COUNT(*) OVER () as maxrows
      FROM Table
      Where ID in ( 1,2 3,10)
     )

但是，我怀疑您会看到很多性能改进。需要扫描整个表以获得总数。这可能就是性能问题所在。

,

这可能是在黑暗中拍摄的，但是您可以尝试使用临时表而不是 cte 。 尽管性能结果和优先级取决于使用情况，但临时表有时实际上可以证明是更好的，因为它使您能够利用索引和专用统计信息。

INSERT INTO #TempResult 
    SELECT ID,Name
    FROM Table
    WHERE ID in ( 1,10)
,

IN语句是SQL Server查询引擎的一个臭名昭著的障碍。当它变得“庞大”（您的话语）时，即使是简单的查询，它也会减慢速度。以我的经验，包含超过5000项内容的IN语句几乎总是无法接受地减慢任何查询的速度。

将大型IN语句的项目首先转换为临时表或表变量，然后再与该表联接的方法几乎总是更好的选择，如下所示。我测试了一下，发现即使准备了临时表，它的速度也显着提高。我认为IN语句，即使内部查询使用它执行得足够好，也会对组合查询产生不利影响。

DECLARE @ids TABLE (ID int primary key );

-- This must be done in chunks of 1000
INSERT @ids (ID) VALUES
(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),...
...

;WITH TempResult AS
(
    SELECT tbl.ID,tbl.Name
    FROM Table tbl
    JOIN @ids ids ON ids.ID = tbl.ID
),TempCount AS 
(
    SELECT COUNT(*) AS MaxRows FROM TempResult
)
SELECT *
FROM TempResult,TempCount
ORDER BY TempResult.Name
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
,

在使用ROW_NUMBER()过滤表时，您可以尝试 对行进行计数：

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

;WITH 
TempResult AS (
    SELECT ID,ROW_NUMBER() OVER (ORDER BY ID) N
    FROM Table
    Where ID in ( 1,10)
),TempCount AS (
    SELECT TOP 1 N AS MaxRows 
    FROM TempResult
    ORDER BY ID DESC
)
SELECT *
FROM 
    TempResult,TempCount 
ORDER BY 
    TempResult.Name
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
,

CTE非常好，但是连续许多CTE（我认为不是两个，但总体而言）却使我多次表现恐怖。我认为最简单的方法是一次计算行数并将其分配给变量：

moment.fn.businessDiff = function (param,relative) {
  var d1 = this.clone();
  var d2 = param.clone();
  var positive = d1 >= d2;
  var start = d1 < d2 ? d1 : d2;
  var end = d2 > d1 ? d2 : d1;

  var daysBetween = 0;

  // this will return true since it's the same day,and will in fact return 0
  if (start.format('DD/MM/YYYY') === end.format('DD/MM/YYYY')) {
    return daysBetween;
  }

  while (start < end) {
    if (start.isBusinessDay()) {
      daysBetween++;
    }
    start.add(1,'d');
  }

  if (relative) {
    return positive ? daysBetween : -daysBetween;
  }

  return daysBetween;
};
,

我目前无法对此进行测试，但是浏览它后，我感到震惊，因为指定了乘法（交叉联接），如下所示：

FROM TempResult,TempCount    <----- this is what is slow. Removing this and the query is super

可能是问题

简单地写为：

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

WITH TempResult AS(
    SELECT ID,Name
    FROM Table
     Where ID in ( 1,10)
)
SELECT *,(SELECT COUNT(*) FROM TempResult) AS MaxRows
FROM TempResult
ORDER BY TempResult.Name
    OFFSET (@PageNum-1)*@PageSize ROWS
    FETCH NEXT @PageSize ROWS ONLY
,

我过去也遇到过同样的问题，当下划线表中的记录增加时，记录数依赖CTE会导致性能问题。

由于CTE的每一行都在进行计数，因此，一旦记录增加，查询就会变慢。

我更改了实现方式以缩短响应时间。我做了以下-

如果不是CTE，我将查询结果插入到临时表中。 执行一个单独的查询以从临时表中获取计数，并将存储的记录计数结果存储在一个变量中，然后将该变量与主分页查询一起使用。

paginationsqlsql-servertsql
本文链接：https://www.f2er.com/3158329.html

大家都在问