sql-server-2005 - 子查询或左连接组哪个更快？

Question

我必须在我的应用程序中用总计列显示运行总计..​​....所以我使用了以下查询来查找运行总计..​​....我发现两者都按照我的需要工作。在一个我使用左连接与 group by 和另一个我使用子查询。

现在我的问题是，当我的数据每天增长数千时，哪一个更快，如果数据将限制在 1000 或 2000 行，那么哪一个更好......还有任何其他方法比这两个更快？ ??

declare @tmp table(ind int identity(1,1),col1 int)
insert into @tmp
select 2
union
select 4
union
select 7
union 

select 5
union
select 8
union 
select 10



SELECT t1.col1,sum( t2.col1)
FROM @tmp AS t1 LEFT JOIN @tmp t2 ON t1.ind>=t2.ind
group by t1.ind,t1.col1


select t1.col1,(select sum(col1) from  @tmp as t2 where t2.ind<=t1.ind)
from @tmp as t1

Answer 1

在 SQL Server 中计算运行总数的一个很好的资源是 this document由 Itzik Ben Gan 提交给 SQL Server 团队，作为他获得 OVER 的事件的一部分子句从其最初的 SQL Server 2005 实现进一步扩展。在其中，他展示了一旦​​您进入数万行游标，如何执行基于集合的解决方案。 SQL Server 2012 确实扩展了 OVER子句使这种查询更加容易。

SELECT col1,
       SUM(col1) OVER (ORDER BY ind ROWS UNBOUNDED PRECEDING)
FROM   @tmp 

正如您在 SQL Server 2005 上一样，但是这对您不可用。

亚当·马卡尼克 shows here如何使用 CLR 来提高标准 TSQL 游标的性能。

对于这个表定义

CREATE TABLE RunningTotals
(
ind int identity(1,1) primary key,
col1 int
)

我使用 ALLOW_SNAPSHOT_ISOLATION ON 在数据库中创建了包含 2,000 和 10,000 行的表还有一个关闭了这个设置(这是因为我的初始结果是在一个数据库中，这个设置导致了结果的一个令人费解的方面)。

所有表的聚集索引只有 1 个根页。每个的叶页数如下所示。

+-------------------------------+-----------+------------+
|                               | 2,000 row | 10,000 row |
+-------------------------------+-----------+------------+
| ALLOW_SNAPSHOT_ISOLATION OFF  |         5 |         22 |
| ALLOW_SNAPSHOT_ISOLATION ON   |         8 |         39 |
+-------------------------------+-----------+------------+

我测试了以下案例(链接显示执行计划)

Left Join and Group By

相关子查询2000 row plan , 10000 row plan

CTE from Mikael's (updated) answer

CTE below

包含额外 CTE 选项的原因是为了提供一个 CTE 解决方案，如果 ind 仍然有效。列不能保证顺序。

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
DECLARE @col1 int, @sumcol1 bigint;

WITH    RecursiveCTE
AS      (
        SELECT TOP 1 ind, col1, CAST(col1 AS BIGINT) AS Total
        FROM RunningTotals
        ORDER BY ind
        UNION   ALL
        SELECT  R.ind, R.col1, R.Total
        FROM    (
                SELECT  T.*,
                        T.col1 + Total AS Total,
                        rn = ROW_NUMBER() OVER (ORDER BY T.ind)
                FROM    RunningTotals T
                JOIN    RecursiveCTE R
                        ON  R.ind < T.ind
                ) R
        WHERE   R.rn = 1
        )
SELECT  @col1 =col1, @sumcol1=Total
FROM    RecursiveCTE
OPTION  (MAXRECURSION 0);

所有的查询都有 CAST(col1 AS BIGINT)添加以避免在运行时出现溢出错误。此外，对于所有这些，我将结果分配给变量如上所述，以消除从考虑发送回结果所花费的时间。

结果

+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
|                  |          |        |          Base Table        |         Work Table         |     Time        |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
|                  | Snapshot | Rows   | Scan count | logical reads | Scan count | logical reads | cpu   | elapsed |
| Group By         | On       | 2,000  | 2001       | 12709         |            |               | 1469  | 1250    |
|                  | On       | 10,000 | 10001      | 216678        |            |               | 30906 | 30963   |
|                  | Off      | 2,000  | 2001       | 9251          |            |               | 1140  | 1160    |
|                  | Off      | 10,000 | 10001      | 130089        |            |               | 29906 | 28306   |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| Sub Query        | On       | 2,000  | 2001       | 12709         |            |               | 844   | 823     |
|                  | On       | 10,000 | 2          | 82            | 10000      | 165025        | 24672 | 24535   |
|                  | Off      | 2,000  | 2001       | 9251          |            |               | 766   | 999     |
|                  | Off      | 10,000 | 2          | 48            | 10000      | 165025        | 25188 | 23880   |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| CTE No Gaps      | On       | 2,000  | 0          | 4002          | 2          | 12001         | 78    | 101     |
|                  | On       | 10,000 | 0          | 20002         | 2          | 60001         | 344   | 342     |
|                  | Off      | 2,000  | 0          | 4002          | 2          | 12001         | 62    | 253     |
|                  | Off      | 10,000 | 0          | 20002         | 2          | 60001         | 281   | 326     |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+
| CTE Alllows Gaps | On       | 2,000  | 2001       | 4009          | 2          | 12001         | 47    | 75      |
|                  | On       | 10,000 | 10001      | 20040         | 2          | 60001         | 312   | 413     |
|                  | Off      | 2,000  | 2001       | 4006          | 2          | 12001         | 94    | 90      |
|                  | Off      | 10,000 | 10001      | 20023         | 2          | 60001         | 313   | 349     |
+------------------+----------+--------+------------+---------------+------------+---------------+-------+---------+

相关子查询和 GROUP BY版本使用由 RunningTotals 上的聚集索引扫描驱动的“三角”嵌套循环连接。表 ( T1 ) 并且，对于该扫描返回的每一行，返回到表 ( T2 ) 自加入 T2.ind<=T1.ind .

这意味着重复处理相同的行。当 T1.ind=1000行被处理，自连接检索所有行并用 ind <= 1000 求和。 , 然后是下一行 T1.ind=1001再次检索相同的 1000 行，并与另一行相加，依此类推。

对于 2,000 行的表，此类操作的总数为 2,001,000，对于 10k 行，通常为 50,005,000 或更多 (n² + n) / 2 这显然呈指数增长。

在 2,000 行的情况下， GROUP BY 之间的主要区别子查询版本是前者在连接后具有流聚合，因此有三列输入其中( T1.ind 、 T2.col1 、 T2.col1 )和一个 GROUP BY T1.ind 的属性(property)而后者计算为标量聚合，连接前的流聚合只有 T2.col1喂入它并没有 GROUP BY属性设置。可以看出，这种更简单的安排在减少 CPU 时间方面具有可衡量的好处。

对于 10,000 行的情况，子查询计划存在额外差异。它添加了一个 eager spool复制所有 ind,cast(col1 as bigint)值转换为 tempdb .在快照隔离的情况下，这比聚集索引结构更紧凑，最终效果是将读取次数减少了约 25%(因为基表为版本信息保留了相当多的空白空间)，当这个选项关闭时，它会变得不那么紧凑(大概是由于 bigint 与 int 的差异)并且更多的读取结果。这减少了子查询和按版本分组之间的差距，但子查询仍然获胜。

然而，明显的赢家是递归 CTE。对于“无间隙”版本，来自基表的逻辑读取现在是 2 x (n + 1)反射(reflect) n index 寻找 2 级索引以检索所有行以及末尾的附加行，该行不返回任何内容并终止递归。然而，这仍然意味着要处理 22 个页表需要 20,002 次读取!

递归 CTE 版本的逻辑工作表读取量非常高。似乎每个源行读取 6 次工作表。这些来自存储前一行输出的索引假脱机，然后在下一次迭代中再次读取(Umachandar Jayachandran here 对此进行了很好的解释)。尽管数字很高，这仍然是表现最好的。