SUBQUERY总性能与案例总和性能

Question

我必须根据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的和ob的值的基础；我正在使用以下查询

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个专栏和数百万条记录，性能会更快吗？

或任何其他实现这一点的方法，这将比这两种方法更好地提高性能

Answer 1

扩展Martin的答案-这取决于您有什么索引以及列的填充方式(是否可以为null)。考虑这个例子。

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被创建为可以为null，并且只有20%不为null。现在，对表(不带索引)运行查询。在运行它之前，请确保按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来说非常容易。即使是其他2列，也可以通过每个索引页检索比数据页上的聚集索引(将包含所有列)更多的行来获得帮助。

当您将其扩展到27列时，如果每列都是稀疏的，并且如果您在27列中的每列上都有一个索引，则第一个表单可能会运行得更快。这是一个很大的要求，即使到那时，它也可能只会稍微快一点。

Answer 2

第二个选项对表进行单次传递；第一个传球多次。就性能而言，在大多数情况下，第二种选择应该更优。

Answer 3

这取决于您有什么索引。

如果a、b和c都被索引，那么原始版本可能会快得多。尤其是如果表中有很大一部分不符合任何标准。

如果您根本没有有用的索引，那么可以选择三次扫描而不是一次扫描，因此CASE版本应该更快。