一条sql语句的优化 (问题整理篇)
来源:互联网 发布:网络歌曲陪我去流浪 编辑:程序博客网 时间:2024/06/05 22:43
--以下代码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如下(晴天大大的上面贴了):
SETSTATISTICS IOON
;with cteas(
selectDATEPART(mm,createtime)AS mm,spid,mtype,num=isnull(count(distinct mobile),0)from v_mr
where createtime>='2011-10'+'-01'and stat='DELIVRD'
groupby DATEPART(mm,createtime),spid,mtype
)
--select * from cte
select a.spid,a.notes,a.progtype,a.feevalue/100as feevalue,b.mt_content,b.id,num1,num2
from (select spid,mtype,
num1=sum(casewhen mm>=10then numelse0end),
num2=sum(casewhen mm>=11then numelse0end)from cte
groupby spid,mtype ) t
join esms_limit aon(a.spid=t.spidand a.type=t.mtype)
join esms_spinfo bon (a.spid=b.spid)
SETSTATISTICS IOOff
/*
(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秒
SETSTATISTICS IOON
select spid,mtype,num1=isnull(count(distinct mobile),0)
into #m
from v_mr
where createtime>='2011-11'+'-01'and stat='DELIVRD'
groupby spid,mtype
select spid,mtype,num2=isnull(count(distinct mobile),0)
into #n
from v_mr
where createtime>='2011-10'+'-01'and stat='DELIVRD'
groupby spid,mtype
select a.spid,a.notes,a.progtype,a.feevalue/100as feevalue,b.mt_content,b.id ,num1,num2
from #m m
join #n non (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)
SETSTATISTICS IOOff
/*
表 '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 IOON
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)
SETSTATISTICS IOOff
/*
(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 次。
- 一条sql语句的优化 (问题整理篇)
- 一条SQL语句的优化过程
- 一条SQL语句的优化学习
- 一条经典的SQL语句优化实例
- Oracle下一条SQL语句的优化
- oracle下一条SQL语句的优化过程(比较详细)
- oracle下一条SQL语句的优化过程(比较详细)
- 一条语句的问题:
- SQL语句中一条常见的行合并问题
- 一条SQL语句的设计
- 一条精辟的SQL语句
- 一条笔试的sql语句
- 一条SQL语句的剖析
- sql语句优化问题
- sql语句优化注意的问题
- MySQL的sql语句优化问题
- sql语句的性能优化问题
- oracle11g 一条几百行行的sql语句 优化 需要适可而止才行
- 使用git工具下载android.jar Source Code
- Codeblocks中的第一个程序Helloworld,新建一个控制台项目
- linux根文件系统的挂载过程 2
- pmap学习:系统测试中怎么确定内存泄露(memory leak)
- [Android]Native javah 生成C头文件问题
- 一条sql语句的优化 (问题整理篇)
- 近期情况总结与进阶计划
- 武陟县位于河南省西北部
- C++ 点滴积累(5)
- [Android] Android的音频采集
- 使用JDBC处理大数据
- 一个Python程序员的进化
- MyEclipse的快捷键
- 《创业大败局》一文的启示