求一个比较简单的SQL语句, 主从表数据汇总问题,由于数据量较大,需要考虑到执行效率.

Question

具体情况如下:
有一个主表,tblSOngInfo 记录着歌曲信息,fSongCode 是主键.(数据量约六万条)有一个歌曲点选表: tblSongSelect 记录的主要信息有: fSongCode, fWeekTop, fMonthTop, fTotalTop.
这三个字段的函义分别是:歌曲编号, 周排行,月排行,总排行.应用程序在每次运行时,需要得到每首歌的排行信息..排行信息是从一个点歌明细表中生成的. 点歌明细表为:tblSongTop(fSongCode, fSelectDate),由于周排行,月排行,都与时间有关,因此每点一首歌,只能把信息保存到明细里, 而不能直接更新 tblSongSelect表.
明细表的数据量很大,每天以一万条记录的速度增加. 历史数据至少保存两个月..那么程序每次运行时,都要根据 tblSongInfo 和 tblSongTop 生成 tblSongSelect 表.我写的生成语句如下:
insert into tblSongSelect select * ,NULL as fWeekTop, Null as fMonthTop, NULL as fTotalTop from tblSongInfo --把基本信息从歌曲信息表中拷贝一份.update tblSongSelect set --更新排行记录
fWeekTop = (select count(*) from tblSongTop S where S.fSongCode = tblSongSelect.fSOngCode and S.fSelectDate> getdate() - 7),  --生成周排行
fMonthTop = (select count(*) from tblSongTop S where S.fSongCode = tblSongSelect.fSOngCode and S.fSelectDate> getdate() - 30), --生成月排行
fTotalTop = (select count(*) from tblSongTop S where S.fSongCode = tblSongSelect.fSOngCode) --生成总排行如果tblSongTop 里只有几十条数据,还行,可以执行出来,
可是现在测试时,tblSongTop 里有七万条数据,执行了十分钟,还没有结果.各位有没有一个好的方法和建议啊?

Answer 1

tblSongTop 有索引吗? 
7w条数据好像很少的说 当然考率你的机器硬件配置就不好说了

Answer 2

谢谢楼上, 更多地是想向各位请教: 我采用的这种方式,是不是存在着问题?1. 删除排行表里的所有数据.
delete from tblSongSelect2. 把歌曲基础信息复制到排行表里,排行信息置为空.
insert   into   tblSongSelect   select   *   ,NULL   as   fWeekTop,   Null   as   fMonthTop,   NULL   as   fTotalTop   from   tblSongInfo   --把基本信息从歌曲信息表中拷贝一份. 3. 从点歌信息表中,分别统计周,月,总排行.
update   tblSongSelect   set   --更新排行记录 
fWeekTop   =   (select   count(*)   from   tblSongTop   S   where   S.fSongCode   =   tblSongSelect.fSOngCode   and   S.fSelectDate>   getdate()   -   7),     --生成周排行 
fMonthTop   =   (select   count(*)   from   tblSongTop   S   where   S.fSongCode   =   tblSongSelect.fSOngCode   and   S.fSelectDate>   getdate()   -   30),   --生成月排行 
fTotalTop   =   (select   count(*)   from   tblSongTop   S   where   S.fSongCode   =   tblSongSelect.fSOngCode)   --生成总排行 还有没有更好的处理方式,比如,把第2 第3 点,直接用一个SQL语句搞定?(想这样搞,却没有搞定)

Answer 3

insert into tblSongSelect   
select       *       
,sum(case when datediff(day,fSelectDate,getdate()) < 7   then 1 else 0 end)  as       fWeekTop     
,sum(case when datediff(day,fSelectDate,getdate()) < 30 then 1 else 0 end) as       fMonthTop      
, count(1)  as       fTotalTop 
from       tblSongInfo  a inner join tblSongTop b on a.fSongCode = b.fSOngCode  

Answer 4

insert into tblSongSelect   
select       *       
,sum(case when datediff(day,fSelectDate,getdate()) < 7   then 1 else 0 end)  as       fWeekTop     
,sum(case when datediff(day,fSelectDate,getdate()) < 30 then 1 else 0 end) as       fMonthTop      
, count(1)  as       fTotalTop 
from       tblSongInfo  a inner join tblSongTop b on a.fSongCode = b.fSOngCode  

Answer 5

--怀疑楼主的统计有问题，出现了笛卡儿乘积，可尝试下分开处理如下：
--PS：我统计过30W的数据，也是做联合查询，好像没有那么慢的。--3.   从点歌信息表中,分别统计周,月,总排行. 
update       tblSongSelect       set       --更新排行记录   
fWeekTop       =       (select       count(*)       from       tblSongTop)
where       tblSongTop.fSongCode       =       tblSongSelect.fSOngCode       and
tblSongTop.fSelectDate>       getdate()       -       7      --生成周排行
update       tblSongSelect       set       --更新排行记录   
fMonthTop       =       (select       count(*)       from       tblSongTop)
where       tblSongTop.fSongCode       =       tblSongSelect.fSOngCode       and
tblSongTop.fSelectDate>       getdate()       -       30     --生成月排行update       tblSongSelect       set       --更新排行记录   
fTotalTop       =       (select       count(*)       from       tblSongTop)
where       tblSongTop.fSongCode       =       tblSongSelect.fSOngCode      --生成总排行

Answer 6

insert into tblSongSelect   
select       *       
,sum(case when datediff(day,isnull(b.fSelectDate,0),getdate()) < 7   then 1 else 0 end)  as       fWeekTop     
,sum(case when datediff(day,isnull(b.fSelectDate,0),getdate()) < 30 then 1 else 0 end) as       fMonthTop      
, count(1)  as       fTotalTop 
from       tblSongInfo  a left join tblSongTop b on a.fSongCode = b.fSOngCode 
group by ???? 

Answer 7

应该是update 语句出现了问题, 向个朋友请教了这个问题,基础表有六万条数据,明细表有七十万条数据,查询速度也不过五秒.--删除原有表中的数据
delete from tblSongInfoExport --导入歌曲信息表的原有数据
insert into tblSongInfoExport
select ta.*,tb.WeekTop,tc.MonthTop,td.TotalTop 
  from tblSongInfo ta,
 (select fSongCode,Count(*) WeekTop from tblSongTop where fOrderTime > getDate() -7 group by fSongCode)  tb,
 (select fSongCode,Count(*) MonthTop from tblSongTop where fOrderTime > getDate() -30 group by fSongCode)  tc,
 (select fSongCode,Count(*) TotalTop from tblSongTop  group by fSongCode)  td
where ta.fSongCode *= tb.fSongCode
  and ta.fSongCode *= tc.fSongCode
  and ta.fSongCode *= td.fSongCode具体表结构与提问里的一不样, 只是有点不明白,我代码里,采用Update 方式,为什么会有这么慢呢?是哪里出现了问题??