一条sql语句的优化 (问题整理篇)

--以下代码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 次。