--以下代码16秒,查两次union的方式
select a.spid,a.notes,a.progtype,a.feevalue/100as feevalue,b.mt_content,b.id ,num1,num2from(
select spid,mtype,num1=isnull(count(distinct mobile),0)from v_mr
where createtime>='2011-11'+'-01'and stat='DELIVRD'
groupby spid,mtype) m
join (
select spid,mtype,num2=isnull(count(distinct mobile),0)from v_mr
where createtime>='2011-10'+'-01'and stat='DELIVRD'
groupby spid,mtype
) n on (m.spid=n.spidand m.mtype=n.mtype)
join esms_limit aon (a.spid=m.spidand a.type=m.mtype)
join esms_spinfo bon (a.spid=b.spid)
--以下代码将近3分钟,为了可读性
select a.spid,a.notes,a.progtype,a.feevalue/100as feevalue,b.mt_content,b.id ,num1,num2from (
select spid,mtype,
num1=isnull(count(distinctcasewhen createtime>='2011-11'+'-01'and stat='DELIVRD'then mobile end),0),
num2=isnull(count(distinctcasewhen createtime>='2011-10'+'-01'and stat='DELIVRD'then mobile end),0)
from v_mr
groupby spid,mtype
) m
join esms_limit aon (a.spid=m.spidand a.type=m.mtype)
join esms_spinfo bon (a.spid=b.spid)
[color=#FF0000]简单说下需求:就是查最近1个月的数据和最近2个月的数据,再和2个配置表连接。
索引spid啊,createtime啊,stat啊之类的上面该建的都建了。
主要是想一条语句让页面显示出来这些数据,这个速度还是等不急。
太长不好看,又不太想用存储过程,不知道还有什么地方可以优化不,求大神指教[/color]
补充下:'2011-10'+'-01'这种方式是为了程序端传参,是用到了索引的,不用纠结这个。
...
spid是公司唯一id
mtype代表类型
select 中除了num1和num2是要分时间段查出来的,其他都是a和b表的东西,可以无视,
a和b表都是配置表,很小,v_mr是数据表,量很大
主要是查num1和num2耗时比较久,再和a、b一连接时间就夸张了
不知道是不是多表连接时表的顺序有什么影响
之前把查num1和num2的放后面,a、b表在前面,时间更夸张。。。
优化一:
--我自己用cte方式写的41秒,晴天大大的也是40秒左右,我的io如下(晴天大大的上面贴了):
SET STATISTICS IO ON
;with cte as(
select DATEPART(mm,createtime) AS mm,spid,mtype,num=isnull(count(distinct mobile),0) from v_mr
where createtime>='2011-10'+'-01' and stat='DELIVRD'
group by DATEPART(mm,createtime),spid,mtype
)
--select * from cte
select a.spid,a.notes,a.progtype,a.feevalue/100 as feevalue,b.mt_content,b.id,num1,num2
from (select spid,mtype,
num1=sum(case when mm>=10 then num else 0 end),
num2=sum(case when mm>=11 then num else 0 end) from cte
group by spid,mtype ) t
join esms_limit a on(a.spid=t.spid and a.type=t.mtype)
join esms_spinfo b on (a.spid=b.spid)
SET STATISTICS IO Off
/*
(34 行受影响)
表 'esms_limit'。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'esms_spinfo'。扫描计数 1,逻辑读取 12 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 103,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq_log'。扫描计数 136,逻辑读取 272 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq'。扫描计数 136,逻辑读取 272 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_log'。扫描计数 136,逻辑读取 1160216 次,物理读取 0 次,预读 2 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms'。扫描计数 136,逻辑读取 101869 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
优化二:
--临时表方式,2秒
SET STATISTICS IO ON
select spid,mtype,num1=isnull(count(distinct mobile),0)
into #m
from v_mr
where createtime>='2011-11'+'-01' and stat='DELIVRD'
group by spid,mtype
select spid,mtype,num2=isnull(count(distinct mobile),0)
into #n
from v_mr
where createtime>='2011-10'+'-01' and stat='DELIVRD'
group by spid,mtype
select a.spid,a.notes,a.progtype,a.feevalue/100 as feevalue,b.mt_content,b.id ,num1,num2
from #m m
join #n n on (m.spid=n.spid and m.mtype=n.mtype)
join esms_limit a on (a.spid=m.spid and a.type=m.mtype)
join esms_spinfo b on (a.spid=b.spid)
SET STATISTICS IO Off
/*
表 'sms_report_esms'。扫描计数 5,逻辑读取 830 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_log'。扫描计数 5,逻辑读取 3234 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq_log'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(31 行受影响)
表 'sms_report_esms'。扫描计数 5,逻辑读取 830 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_log'。扫描计数 5,逻辑读取 8936 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq_log'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
(34 行受影响)
(31 行受影响)
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'esms_spinfo'。扫描计数 1,逻辑读取 12 次,物理读取 0 次,预读 27 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'esms_limit'。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 12 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#n__________________________________________________________________________________________________________________000000001B49'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 '#m__________________________________________________________________________________________________________________000000001B48'。扫描计数 1,逻辑读取 2 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
优化三:
嗯,一个4表视图。。。
再贴个我开始的16秒的,现在查了下要28秒
SET STATISTICS IO ON
select a.spid,a.notes,a.progtype,a.feevalue/100 as feevalue,b.mt_content,b.id ,num1,num2 from (
select spid,mtype,num1=isnull(count(distinct mobile),0) from v_mr
where createtime>='2011-11'+'-01' and stat='DELIVRD'
group by spid,mtype) m
join (
select spid,mtype,num2=isnull(count(distinct mobile),0) from v_mr
where createtime>='2011-10'+'-01' and stat='DELIVRD'
group by spid,mtype
) n on (m.spid=n.spid and m.mtype=n.mtype)
join esms_limit a on (a.spid=m.spid and a.type=m.mtype)
join esms_spinfo b on (a.spid=b.spid)
SET STATISTICS IO Off
/*
(31 行受影响)
表 'esms_limit'。扫描计数 1,逻辑读取 5 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'esms_spinfo'。扫描计数 1,逻辑读取 12 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 109,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq_log'。扫描计数 167,逻辑读取 334 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_dq'。扫描计数 167,逻辑读取 334 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms_log'。扫描计数 167,逻辑读取 649749 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'sms_report_esms'。扫描计数 167,逻辑读取 125753 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。
表 'Worktable'。扫描计数 0,逻辑读取 0 次,物理读取 0 次,预读 0 次,lob 逻辑读取 0 次,lob 物理读取 0 次,lob 预读 0 次。