sql - SUBQUERY总体表现与案例总数表现

为了更好的理解，我必须在where子句的基础上做一些列的总和，我在这里实现一个临时表

declare @tbl table(a int ,b int,c int)
insert into @tbl values(1,2,3)
insert into @tbl values(2,2,3)
insert into @tbl values(1,3,1)
insert into @tbl values(1,2,3)
insert into @tbl values(1,2,3)

并且用于求出a，b，c的总和，以a，b，c的值为基础；我正在使用以下查询

 SELECT (
         SELECT SUM(a) from @tbl where a=1
         )AS a ,
          (SELECT SUM(b) from @tbl where b=2

         )AS b ,
          (SELECT SUM(c) from @tbl where c=3

         )AS c

我请一位朋友对这项工作进行单行查询，他建议我遵循以下几行

select sum((case  when a=1 then a  else null end)),
        sum((case  when b=2 then b  else null end)),
        sum((case  when c=3 then c  else null end))
         from @tbl

现在我正在考虑性能，如果我有27列和数百万条记录，它将更快地工作？

或任何其他实现此目的的方法将比这两个方法更好地提高性能

最佳答案

扩展马丁的答案-这取决于您拥有哪些索引以及该列的填充方式（是否为空）。考虑这个例子。

create table tbl (id int identity primary key, a int ,b int,c int, d int)
insert tbl values(1,2,3,null)
insert tbl values(2,null,3,1)
insert tbl values(1,null,1,4)
insert tbl values(1,null,3,5)
insert tbl values(1,null,3,6)
insert tbl select a,b,c,d from tbl --10
insert tbl select a,b,c,d from tbl --20
insert tbl select a,b,c,d from tbl --40
insert tbl select a,b,c,d from tbl --80
insert tbl select a,b,c,d from tbl --160
insert tbl select a,b,c,d from tbl --320
insert tbl select a,b,c,d from tbl --640
insert tbl select a,b,c,d from tbl --1280
insert tbl select a,b,c,d from tbl --2560
insert tbl select a,b,c,d from tbl --5120
insert tbl select a,b,c,d from tbl --10240

b列创建为可为空，并且只有20％为非空。现在，对表（无索引）运行查询。在运行它之前，请确保按Ctrl-M（显示实际执行计划）。在同一批中运行两个查询，即突出显示两个查询的文本并执行。

SELECT (SELECT SUM(a) from tbl where a=1) AS a ,
       (SELECT SUM(b) from tbl where b=2) AS b ,
       (SELECT SUM(c) from tbl where c=3) AS c

select sum((case  when a=1 then a  else null end)),
       sum((case  when b=2 then b  else null end)),
       sum((case  when c=3 then c  else null end))
from tbl

在这里，我不会为您带来图像的麻烦，但是请看一下该计划，该计划将显示针对顶部查询的费用约为75％，针对底部查询的费用为25％。可以预料，75％：25％= 3：1是由于第一个查询准确地通过了3次表。现在创建这三个索引：

create index ix_tbl_a on tbl(a)
create index ix_tbl_b on tbl(b)
create index ix_tbl_c on tbl(c)

然后，重新运行查询批处理（一起）。这次，您会看到大约51％到49％的费用。相当接近。原因是因为稀疏地填充(b)列很容易仅从索引页开始SUM。每个索引页检索的行数比数据页上的聚集索引（将包含所有列）的行数还要多，因此甚至可以帮助其他两列。

当您将其扩展到27列时，如果每一列都被稀疏地填充，并且在这27列的每一列上都有一个索引，则第一种表单的运行速度会更快。一个很大的要求，即使那样，它也可能只会非常快。