首页 新闻 搜索 专区 学院

请教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

当数据量大时,几种写法耗时都差不多,想请问有没有更高效的写法?建表存储静态汇总数据的不算。望指教?

ahdung的主页 ahdung | 菜鸟二级 | 园豆:206
提问于:2015-12-15 16:37
< >
分享
最佳答案
1

同求

收获园豆:5
悦光阴 | 老鸟四级 |园豆:2239 | 2015-12-15 18:16

 最近看了分区,你可以使用分区表提高性能。

例如,你在ID列上分区,那么多个partition 在 ID列上 同时进行聚合,1,由于每个partition的数据量相对较少,聚合性能高。2,由于多个partition 能够利用multiple cpu进行parallel execution,充分利用server 的cpu。3,另外,建议在ID上建立聚集索引,group operation 就不需要进行extra soring overhead。

希望能帮到你

悦光阴 | 园豆:2239 (老鸟四级) | 2015-12-19 17:18

@悦光阴: 谢谢支招,必要时我试试。

ahdung | 园豆:206 (菜鸟二级) | 2016-08-18 14:59
其他回答(2)
0

 求同,但是我觉得要是数据量大的话,就不要用join连接查询了吧,因为笛卡尔积的原因,这样会很耗费的

木木人啊哦 | 园豆:233 (菜鸟二级) | 2015-12-16 21:41

我是T-SQL,join等价inner join,应该不存在笛卡尔积。

支持(0) 反对(0) ahdung | 园豆:206 (菜鸟二级) | 2015-12-18 17:11
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

Artikel | 园豆:387 (菜鸟二级) | 2015-12-18 11:45

谢谢回复。我不觉得这样会提升性能。

支持(0) 反对(0) ahdung | 园豆:206 (菜鸟二级) | 2015-12-18 14:11

@ahdung: 我是之前碰到类似的求汇总数据的需求,然后就是因为数据量太大了,几张表上千万条,其中一张表还是上亿条,查询时间好几分钟,后来改为上面的写法吗,几秒就好了

支持(0) 反对(0) Artikel | 园豆:387 (菜鸟二级) | 2015-12-18 14:52

@wengerr: 本着试了再说的精神,我还是试他一试。但在我这里的情况的确如我所料,没有变快,反而变慢,俩临时表建没建主键的情况都试了,有主键要比没主键要快些,但都比不上直接查询,具体情况如下:

临时表法(无主键):13秒

临时表法(有主键):10秒

直接查询法(题中所说的3种写法都差不多):5秒

我估计你的情况有特别原因,比如原表索引碎片严重、索引规划有问题、计算列、以及查询语句有不当或者其他我想不到的原因,双双弄到临时表再来联接,等于避开了原表的一些问题,所以反而得到了更好的效果。鉴于你直接查询和用临时表居然相差那么巨大(几分钟与几秒),我认为这已经超出了通过调整查询语句能带来的提升,换句话,两种方法的差距不该那么大,既然有那么大,那一定在原表方面有问题,所以假如你的原表很健康,你不用临时表应该还可以得到更快的效果~个人看法。无论如何,很感谢你的回复和分享。

支持(0) 反对(0) ahdung | 园豆:206 (菜鸟二级) | 2015-12-18 17:05
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册