sql – 条件聚合性能

发布时间：2020-12-12 06:51:42 所属栏目：MsSql教程来源：网络整理

导读：我们有以下数据 IF OBJECT_ID('dbo.LogTable','U') IS NOT NULL DROP TABLE dbo.LogTable SELECT TOP 100000 DATEADD(day,( ABS(CHECKSUM(NEWID())) % 65530 ),0) datesent INTO [LogTable] FROM sys.sysobjects CROSS JOIN sys.all_columns 我想计算行数，去

我们有以下数据

IF OBJECT_ID('dbo.LogTable','U') IS NOT NULL  DROP TABLE dbo.LogTable

 SELECT TOP 100000 DATEADD(day,( ABS(CHECKSUM(NEWID())) % 65530 ),0) datesent 
 INTO [LogTable]
 FROM    sys.sysobjects
 CROSS JOIN sys.all_columns

我想计算行数，去年行数和最近十年行数。这可以使用条件聚合查询或使用子查询来实现，如下所示

-- conditional aggregation query
SELECT
    COUNT(*) AS all_cnt,SUM(CASE WHEN datesent > DATEADD(year,-1,GETDATE())
             THEN 1 ELSE 0 END) AS last_year_cnt,-10,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable


-- subqueries
SELECT
(
    SELECT count(*) FROM LogTable 
) all_cnt,(
    SELECT count(*) FROM LogTable WHERE datesent > DATEADD(year,GETDATE())
) last_year_cnt,GETDATE())
) last_ten_year_cnt

如果您执行查询并查看查询计划，那么您会看到类似的内容

显然，第一个解决方案有更好的查询计划，成本估算甚至SQL命令看起来更简洁和花哨。但是，如果使用SET STATISTICS TIME ON测量查询的CPU时间，我会得到以下结果(我已经测量了几次，结果大致相同)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,elapsed time = 41 ms.

(1 row(s) affected)

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 31 ms,elapsed time = 26 ms.
SQL Server parse and compile time: 
   CPU time = 0 ms,elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,elapsed time = 0 ms.

因此，第二种解决方案比使用条件聚合的解决方案具有稍好(或相同)的性能。如果我们在datesent属性上创建索引，差异就会变得更明显。

CREATE INDEX ix_logtable_datesent ON dbo.LogTable(DateSent)

然后第二个解决方案开始使用Index Seek而不是Table Scan，它的查询CPU时间性能在我的计算机上降至16ms。

我的问题是两个：(1)为什么条件聚合解决方案至少在没有索引的情况下不胜过子查询解决方案，(2)是否有可能为条件聚合解决方案创建’索引'(或重写条件聚合查询)为了避免扫描，或者如果我们关注性能，条件聚合通常是不合适的？

旁注：我可以说，这种情况对条件聚合非常乐观，因为我们选择所有行的数量总是导致使用扫描的解决方案。如果不需要所有行的数量，则具有子查询的索引解决方案没有扫描，而具有条件聚合的解决方案无论如何都必须执行扫描。

编辑

弗拉基米尔·巴拉诺夫基本上回答了第一个问题(非常感谢你)。但是，第二个问题仍然存在。我可以在StackOverflow上看到使用条件聚合解决方案的答案，它们吸引了很多关注，被认为是最优雅和最清晰的解决方案(有时被认为是最有效的解决方案)。因此，我将稍微概括一下这个问题：

你能举个例子，条件聚合特别优于子查询解决方案吗？

为简单起见，我们假设物理访问不存在(数据在缓冲区缓存中)，因为今天的数据库服务器仍然将大部分数据保留在内存中。

解决方法

简短的摘要

>子查询方法的性能取决于数据分布。
>条件聚合的性能不依赖于数据分布。

子查询方法可以比条件聚合更快或更慢，它取决于数据分布。

当然，如果表具有合适的索引，则子查询可能会从中受益，因为索引将仅允许扫描表的相关部分而不是完整扫描。拥有合适的索引不太可能显着有利于条件聚合方法，因为它无论如何都会扫描整个索引。唯一的好处是，如果索引比表更窄，并且引擎必须将更少的页面读入内存。

了解这一点，您可以决定选择哪种方法。

第一次测试

我做了一个更大的测试表，有5M行。桌子上没有索引。
我使用SQL Sentry Plan Explorer测量了IO和CPU统计信息。我使用SQL Server 2014 SP1-CU7(12.0.4459.0)Express 64位进行这些测试。

实际上，您的原始查询的行为与您所描述的相同，即即使读取次数高3倍，子查询也会更快。

在没有索引的表上尝试几次后，我重写了条件聚合并添加了变量以保存DATEADD表达式的值。

整体时间明显加快。

然后我用COUNT替换了SUM，它再次变得快一点。

毕竟，条件聚合变得和子查询一样快。

加热缓存(CPU = 375)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

子查询(CPU = 1031)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt,GETDATE())
) last_ten_year_cnt
OPTION (RECOMPILE);

原始条件聚合(CPU = 1641)

SELECT -- conditional original
    COUNT(*) AS all_cnt,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

带变量的条件聚合(CPU = 1078)

DECLARE @VarYear1 datetime = DATEADD(year,GETDATE());
DECLARE @VarYear10 datetime = DATEADD(year,GETDATE());

SELECT -- conditional variables
    COUNT(*) AS all_cnt,SUM(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE 0 END) AS last_year_cnt,SUM(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

使用变量和COUNT而不是SUM进行条件聚合(CPU = 1062)

SELECT -- conditional variable,count,not sum
    COUNT(*) AS all_cnt,COUNT(CASE WHEN datesent > @VarYear1
             THEN 1 ELSE NULL END) AS last_year_cnt,COUNT(CASE WHEN datesent > @VarYear10
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

根据这些结果，我的猜测是CASE为每一行调用了DATEADD，而WHERE足够聪明，可以计算一次。加COUNT比SUM更有效。

最后，条件聚合只比子查询稍慢(1062对1031)，可能是因为WHERE本身比CASE更有效，而且WHERE过滤掉了很多行，因此COUNT必须处理更少的行。

在实践中，我会使用条件聚合，因为我认为读取次数更重要。如果您的表很小以适应并保留在缓冲池中，那么对于最终用户来说任何查询都会很快。但是，如果表大于可用内存，那么我预计从磁盘读取会显着减慢子查询。

第二次测试

另一方面，尽早过滤行也很重要。

这是测试的一个细微变化，它证明了这一点。在这里，我将阈值设置为GETDATE()100年，以确保没有行满足过滤条件。

加热缓存(CPU = 344)

SELECT -- warm cache
    COUNT(*) AS all_cnt
FROM LogTable
OPTION (RECOMPILE);

子查询(CPU = 500)

SELECT -- subqueries
(
    SELECT count(*) FROM LogTable 
) all_cnt,100,GETDATE())
) last_year_cnt
OPTION (RECOMPILE);

原始条件聚合(CPU = 937)

SELECT -- conditional original
    COUNT(*) AS all_cnt,GETDATE())
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

带变量的条件聚合(CPU = 750)

DECLARE @VarYear100 datetime = DATEADD(year,SUM(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE 0 END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

使用变量和COUNT而不是SUM进行条件聚合(CPU = 750)

SELECT -- conditional variable,COUNT(CASE WHEN datesent > @VarYear100
             THEN 1 ELSE NULL END) AS last_ten_year_cnt
FROM LogTable
OPTION (RECOMPILE);

下面是一个包含子查询的计划。您可以看到第0个行进入第二个子查询中的Stream Aggregate，所有这些行都在Table Scan步骤中被过滤掉了。

结果，子查询再次更快。

第三次测试

在这里，我更改了之前测试的过滤条件：all>被替换为<。因此，条件COUNT计算所有行而不是无。惊喜，惊喜！条件聚合查询花费相同的750毫秒，而子查询变为813而不是500。

这是子查询的计划：

Could you give me an example,where conditional aggregation notably
outperforms the subquery solution?

这里是。子查询方法的性能取决于数据分布。条件聚合的性能不依赖于数据分布。

子查询方法可以比条件聚合更快或更慢，它取决于数据分布。

了解这一点，您可以决定选择哪种方法。

奖金详情

如果将鼠标悬停在“表扫描”操作符上，则可以看到不同变体的“实际数据大小”。

>简单COUNT(*)：

>条件聚合：

>测试2中的子查询：

>测试3中的子查询：

现在很明显，性能差异可能是由于流经计划的数据量的差异造成的。

如果是简单的COUNT(*)，则没有输出列表(不需要列值)，数据大小最小(43MB)。

在条件聚合的情况下，这个数量在测试2和3之间不会改变，它总是72MB。输出列表有一列datesent。

对于子查询，此数量确实会根据数据分布而更改。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!