SQL 偏移总行计数与 IN 子句一起变慢

共11个回答, 标签: sql sql-server tsql pagination

这个赏金已经结束。这个问题的答案有资格获得 100 的声誉奖金。赏金宽限期在 2 小时内结束。了解更多. 泰伦斯杰克逊想要吸引更多关注对于这个问题: 开始赏金,因为必须有一个更好的方法来获得分页偏移查询中的总行数。

我使用这个基于另一个答案的 sql,但是当包括一个巨大的 in 子句时,得到总计数。如果我删除总数,那么查询需要不到 1 秒。有没有更好更有效的方法来获得总行数?我看到的答案是基于 2013 年的 sql 查询

声明
@ PageSize INT = 10,
@ PageNum INT = 1;

以 TempResult 为 (
选择 ID 、名称
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
),TempCount 为 (
从 TempResult 中选择 COUNT (*) 作为 MaxRows
)
选择 *
从 TempResult,
TempCount <----- 这就是慢。删除这个和查询是超级快
TempResult.Name 订购
偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行
第1个答案

据我所知,除了使用已经提到的 # temp 表方法之外,还有 3 种方法可以实现这一点。在下面的测试用例中,我使用了一个具有 6CPU/16gb RAM 的 SQL Server 2016 开发人员实例和一个包含约 25m 行的简单表。

方法 1: 交叉连接

声明
@ PageSize INT = 10
,@ PageNum INT = 1;

以 TempResult 为 (选择
Id
,ShortDesc
来自 dbo.TestName
其中 id 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
选择
*,MaxRows
从 TempResult
交叉连接 (从 TempResult 选择 COUNT (1) 作为 MaxRows) 作为计数
按 TempResult.shortDesc 偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行;

测试结果 1:

enter image description here

方法 2: 计数 (*) 超过 ()

声明
@ PageSize INT = 10
,@ PageNum INT = 1;

以 TempResult 为 (选择
Id
,ShortDesc
来自 dbo.TestName
其中 id 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
)
选择
*,MaxRows = COUNT (*) OVER ()
从 TempResult
按 TempResult.shortDesc 偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行;

测试结果 2:

enter image description here

方法 3: 第 2 CTE

测试结果 3 (使用 T-SQL 与问题中相同):

enter image description here

结论

最快的方法取决于您的数据结构 (和总行数) 以及服务器大小/负载。在我的情况下,使用 COUNT (*) OVER () 被证明是最快的方法。为了找到最适合你的,你必须测试最适合你的场景。也不排除 # table 方法;-)

第2个答案

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

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

声明 @ MaxRowCount bigint = 10000000,
@ Offset bigint = 0;

删除表如果存在 # ExampleTable;
创建表 # ExampleTable
(
ID bigint 不为空,
名称 varchar (50) 整理 DATABASE_DEFAULT 不为空
);

而 @ Offset <@ MaxRowCount
开始
插入到 # ExampleTable
(ID 、名称)
选择 ROW_NUMBER () OVER (ORDER BY (SELECT NULL)),
ROW_NUMBER () OVER (ORDER BY (选择空))
从 master.dbo.spt_values SV
交叉应用 master.dbo.spt_values SV2;
设置 @ Offset = @ Offset ROWCOUNT_BIG ();
结束;

如果我运行提供的查询#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 中,缓存其中的一部分也可能是值得的,但是要确保测量许多并发用户的影响。

不管工作流程如何,更新索引可能是可以的 (除非你的工作流程真的会扰乱你的索引维护)。

不管工作流程如何,批处理模式将是你的朋友。

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

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

我们 pretty 一贯得到可怜的基数估计 (和由此产生的计划) 与传统的基数估计和行模式执行。强制默认基数估计有助于第一,

第3个答案

你可以试着这样说:

以 TempResult 为 (
选择 ID 、名称、计数 (*) 超过 () 作为最大行
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
)

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

第4个答案

这可能是在黑暗中拍摄的,但你可以尝试使用临时表而不是一个Cte。 尽管一个比另一个的性能结果和偏好取决于用例到用例,但临时表有时实际上可以证明更好,因为它使您能够利用指数和专用统计数据。

插入 # TempResult
选择 ID 、名称
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
第5个答案

IN statement is a notorious hurdle for the SQL Server query engine. When it gets "massive" (your words) it slows down even simple queries. In my experience, IN包含 5000 多个项目的语句几乎总是令人无法接受地减慢任何查询。

它几乎总是更好地转换一个大的项目IN statement into a temp table or table variable first and then join with this table, as below. I tested this and found it's significantly faster, even with the preparation of the temp table. I think that the IN语句,即使内部查询的性能足够好,也会对组合查询产生不利影响。

声明 @ ids 表 (ID int primary key);

-这必须在 1000 块中完成
插入 @ ID (ID) 值
(1) 、 (2) 、 (3) 、 (4) 、 (5) 、 (6) 、 (7) 、 (8) 、 (9) 、 (10) 、...
...

; 以 TempResult 为
(
选择 tbl.ID,tbl.Name
从表 tbl
在 ids.ID = tbl.ID 上加入 @ ids
),
TempCount AS
(
从 TempResult 中选择 COUNT (*) 作为 MaxRows
)
选择 *
从 TempResult,
TempCount
TempResult.Name 订购
偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行
第6个答案

你可以试着数行虽然使用过滤表ROW_NUMBER():

声明
@ PageSize INT = 10,
@ PageNum INT = 1;

; 与
TempResult AS (
选择 ID 、名称、 row _ number (),而不是 (按 ID 排序) N
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
),
TempCount AS (
选择前 1n 作为最大行
从 TempResult
通过 ID DESC 订购
)
选择 *
从
TempResult,
TempCount
按顺序排列
TempResult.Name
偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行
第7个答案

Cte 非常好,但是有许多连续的 cte (我认为两个不多,但总的来说) 让我多次表现恐怖。我认为最简单的方法是计算一次行数,并将其分配给变量:

声明
@ PageSize INT = 10,
@ PageNum INT = 1,
@ MaxRows bigint = (从表中选择计数 (1),其中 ID 位于 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10));

以 TempResult 为 (
选择 ID 、名称
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
)
选择 *
从 TempResult,
@ MaxRows TempCount <----- 这是慢的。删除这个和查询是超级快
TempResult.Name 订购
偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行
第8个答案

我现在不能测试这个,但是在浏览时,我发现指定一个乘法 (交叉连接),如下所示:

从 TempResult,
TempCount <----- 这就是慢。删除这个,查询是超级的

可能是问题

当简单地写为:

声明
@ PageSize INT = 10,
@ PageNum INT = 1;

以 TempResult 为 (
选择 ID 、名称
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)
)
选择 *,(从 TempResult 中选择 COUNT (*)) 作为 MaxRows
从 TempResult
TempResult.Name 订购
偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行
第9个答案

恕我直言,在这样的问题总是真正的查询有助于识别所有不良因素。

毫无疑问Count(*)是不良因素之一。

另一个重要的不良因素是Order By Name

Order by varchar column , Order by Wide column, Order by Non Index列总是很慢。

如果页面大小增加,那么它将影响性能。

所以,看看你的要求是否可以改变。

我在该表中 3 列有一个索引,有一个连接 查询。该连接表在同一列上也有一个索引 被加入。

没有看到真正的查询,以及连接是如何完成的,什么也不能说。

哪个表有多少行?加入 Resultset 后有多少行?

你可以试试这个

声明
@ PageSize INT = 10,
@ PageNum INT = 1;

声明 @ MaxRows int
创建表 # TempResult (tid int identity (1,1) 主键,id int,名称 varchar (50))
插入 # TempResult (id,名称)
选择 ID 、名称
从表
其中 ID 在 (1 、 2 、 3 、 4 、 5 、 6 、 7 、 8 、 9 、 10)

从 # TempResult 订单中选择 TOP @ MaxRows = tid

-或

-从 # TempResult 中选择 @ MaxRows = max (tid)

选择 id 、名称
从 # TempResult,
@ MaxRows 作为 TempCount
TempResult.Name 订购
偏移 (@ PageNum-1) * @ PageSize 行
仅获取 NEXT @ PageSize 行

你可以把有更多行的表的记录放在#TempResult

然后在最后选择你可以join the small table, if Total Rows in temp table is correct回答。

不知道真正的场景,不能说Index to do in #TempResult

必须有一个更好的方法来获得分页偏移的计数 查询

如果总数是从单个表,那么,

从 sys.dm_db_partition_stats 中选择 SUM (s.row_count)
其中 s.[object_id] = OBJECT_ID (“表”)
和 s.index_id <2

使用Hint通常是危险的想法。如果查询没有执行正常,那么其他已经在这里讨论的因素 (无论我们有什么信息) 通常是原因。

是想到使用的专家HINT in few of their queries.HINT是一种黑客。

第10个答案

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

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

我已经改变了实现,以提高响应时间。我做了以下-

相反,如果 CTE,我已经在临时表中插入了我的查询结果。 执行一个单独的查询,从临时表获取计数,并将记录计数结果存储在一个变量中,然后将此变量与主分页查询一起使用。

第11个答案

结果描述了规划者在制定有效的装饰计划方面有困难。你可以自己做。

到目前为止,我所看到的最佳实践是将total calculation and page获取两个独立的 sql,然后将它们组合在后端服务的下游。

所以要问的问题是: 你为什么打算这样做?为什么不两次传球呢?使用单一通行证有什么好处?

相关问题

如何防止 PHP 中的 SQL 注入? 何时在 MySQL 中使用单引号、双引号和回刻度 让父母和孩子的树文件夹结构在我的 sql< 8 and no CTEs 8="" and="" no=""></ 8 and no CTEs> SQL 偏移总行计数与 IN 子句一起变慢 查询执行非常缓慢,有任何方法可以进一步改进它吗? 错误: TCP 提供程序: 错误代码 0x2746。在 linux 下通过终端进行 Sql 设置时 无 WHILE 循环查询 SQL 中 condtion