SUBQUERY 总性能与案例总和性能 [英] SUBQUERY total performance vs case sum performance

问题描述

我必须根据 where 子句对某些列进行求和，以便更好地理解我在这里实施临时表

I have to do sum of some columns basis on where clause for better understanding i am implementing a temporary table here

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 基于 a,b,c 的值；我正在使用以下查询

and for finding sum of a,b,c ob basis of value of a,b,c ; i am using following query

 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

我让我的一位朋友对这项工作进行单行查询，他建议我按以下几行

I ask one of my friend to make a single line query for this work and he suggest me following lines

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 列和数百万条记录，性能会更快吗?

Now i am thinking about performance which will work faster if i have 27 columns and millions of records ?

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

推荐答案

扩展 Martin 的答案 - 这取决于您拥有哪些索引以及该列的填充方式(可空与否).考虑这个例子.

Expanding on Martin's answer - it depends on what indexes you have and how populated the column is (nullable or not). Consider this example.

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(显示实际执行计划).在同一批次中运行两个查询，即突出显示两个查询的文本并执行.

Column b is created nullable and is only 20% non-null. Now, run your queries against the table (with no indexes). Before you run it, make sure to press Ctrl-M (show actual execution plan). Run both queries in the same batch, i.e. highlight the text of both queries and execute.

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 次.现在创建这三个索引:

I won't bore you with images here but look at the plan which will show a cost of about 75% against the top query and 25% against the bottom. That's expected, 75%:25% = 3:1 which is due to the first query passing through the table 3 times exactly. Now create these three indexes:

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 列.

Then, rerun the query batch (both together). This time, you'll see a cost of about 51% to 49%. Quite close. The reason is because the (b) column being sparsely populated is very easy to SUM from the index pages alone. Even the other 2 columns are helped by retrieving more rows per index page than the clustered index on the data pages (which will contain all columns).

当您将其扩展到 27 列时，如果每一列都很少填充并且如果您在 27 列中的每一列上都有一个索引，则第一个表单可以运行得更快.一个很大的问题，即便如此，它也可能只会稍微快一点.

When you expand this to 27 columns, the first form could run faster if each column is sparsely populated and if you have an index on each of the 27 columns. A big ask, and even then, it will probably only be very marginally faster.

这篇关于SUBQUERY 总性能与案例总和性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！