请教SQL主表取细表汇总的优化问题
0
悬赏园豆：5
[已解决问题]
解决于 2016-08-18 15:00
很基础的一个问题：
主表A，有主键id和其它列
细表B，有外键id、和数量qty
细表的同一id可能有多行，这应该是常见的主细表姿势。
经常需要查询主表+细表的数量汇总，如：
id qty 主表的其它字段 ----------------------------- 1 12 xx 2 5 xx
我知道的有3种写法：
1、在select里聚合
select id, (select sum(qty) from B where id=a.id),xxx from A
2、join聚合好的子表
select a.id,qty from A join(select id,sum(qty) qty from B group by id) b on a.id=b.id
3、join子表，在外部聚合
select a.id,a.xx,sum(qty) from A join B on a.id=b.id group by a.id,a.xx
当数据量大时，几种写法耗时都差不多，想请问有没有更高效的写法？建表存储静态汇总数据的不算。望指教？
最佳答案
1
同求
收获园豆：5
最近看了分区，你可以使用分区表提高性能。
例如，你在ID列上分区，那么多个partition 在 ID列上 同时进行聚合，1，由于每个partition的数据量相对较少，聚合性能高。2，由于多个partition 能够利用multiple cpu进行parallel execution，充分利用server 的cpu。3，另外，建议在ID上建立聚集索引，group operation 就不需要进行extra soring overhead。
希望能帮到你
@悦光阴: 谢谢支招，必要时我试试。
其他回答(2)
0
求同，但是我觉得要是数据量大的话，就不要用join连接查询了吧，因为笛卡尔积的原因，这样会很耗费的
我是T-SQL，join等价inner join，应该不存在笛卡尔积。
0
begin
if object_id('tempdb..#T_a') is not null drop table #T_a
if object_id('tempdb..#T_b') is not null drop table #T_b
select id,xx
into #T_a
from A
select id,sum(qty) as qty
into #T_b
from B group by id
用临时表
select ta.id,ta.xx,tb.qty
from #T_a ta
inner join
#T_b on ta.id=tb.id
drop table #T_a
drop table #T_b
end
谢谢回复。我不觉得这样会提升性能。
@ahdung: 我是之前碰到类似的求汇总数据的需求，然后就是因为数据量太大了，几张表上千万条，其中一张表还是上亿条，查询时间好几分钟，后来改为上面的写法吗，几秒就好了
@wengerr: 本着试了再说的精神，我还是试他一试。但在我这里的情况的确如我所料，没有变快，反而变慢，俩临时表建没建主键的情况都试了，有主键要比没主键要快些，但都比不上直接查询，具体情况如下：
临时表法（无主键）：13秒
临时表法（有主键）：10秒
直接查询法（题中所说的3种写法都差不多）：5秒
我估计你的情况有特别原因，比如原表索引碎片严重、索引规划有问题、计算列、以及查询语句有不当或者其他我想不到的原因，双双弄到临时表再来联接，等于避开了原表的一些问题，所以反而得到了更好的效果。鉴于你直接查询和用临时表居然相差那么巨大（几分钟与几秒），我认为这已经超出了通过调整查询语句能带来的提升，换句话，两种方法的差距不该那么大，既然有那么大，那一定在原表方面有问题，所以假如你的原表很健康，你不用临时表应该还可以得到更快的效果~个人看法。无论如何，很感谢你的回复和分享。
