Chinaunix首页 | 论坛 | 博客
  • 博客访问: 167014
  • 博文数量: 103
  • 博客积分: 2251
  • 博客等级: 大尉
  • 技术积分: 1095
  • 用 户 组: 普通用户
  • 注册时间: 2007-04-03 11:15
文章分类

全部博文(103)

文章存档

2011年(103)

分类: 数据库开发技术

2011-05-11 16:56:49

继续调优,今天上午分析了以下一条处理时间达40秒的SQL语句
select *
  from table
 where T_table_ID in 
  (
   select  distinct s.t_table_id
     from
     ( 
      select distinct a.t_table_id,a.bt
        from  
        (select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) a, 
        (select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) b 
       where b.bbt like a.bbt and a.t_table_id<>b.t_table_id 
         and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
         and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
         and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
         and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
         -- order by a.bt 
      union all 
      select distinct a.t_table_id,a.bt
        from  
        (select right(bt,5) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) a, 
        (select distinct right(bt,5) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0) b 
       where b.bbt like a.bbt and a.t_table_id<>b.t_table_id  
         and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
         and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
         and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
         and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
         and a.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' 
         and b.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' 
       ) s  
   )order by bt 
基本上可以认为是对同一张表的反复操作,而且语句中夹杂了太多的全表扫描
SQLServer的执行计划我个人认为图形化界面固然是好,但是有些时候对于量化的I/O,CPU,COST输出却很不直观,此外像该SQL这样的执行计划,估计1600*1200的整个屏幕都无法显示,可以认为基本是没法看的

只能将SQL分解成若干小SQL,逐步找到瓶颈所在,例如
select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
这两个语句的执行都非常快,并且结果集也比较小,但是两条语句合并后并加上相关条件就非常缓慢。
干脆直接构建两个临时表,反正都是全表扫描,用两个临时表做相互的join,测试之后发现只需要1秒
再构建下面的两个SQL临时表,也做同样的测试
最后再全部合并到一起进行测试,发现也就是2~3秒
实际上还可以再优化一些临时表的构建,但效果达到了也就不愿意尝试了

也尝试过用CTE,不过似乎效果不佳
以下为优化后的SQL样例
/*
with temp1 as
(select left(bt,4) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),
temp2 as
(select distinct left(bt,4) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),
temp3 as
(select left(bt,5) as bbt,* from table where fsrq>getdate()-1 and gkbz=1 and scbz=0),
temp4 as
(select distinct left(bt,5) as bbt,t_table_id from table where fsrq>getdate()-1 and gkbz=1 and scbz=0)
*/
print convert(varchar,getdate(),9)
select left(bt,4) as bbt,* into #temp1 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct left(bt,4) as bbt,t_table_id into #temp2 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select right(bt,5) as bbt,* into #temp3 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select distinct right(bt,5) as bbt,t_table_id into #temp4 from table where fsrq>getdate()-1 and gkbz=1 and scbz=0
select
(select ms from xtclb where dm=lmxz and lb in (130,131) ) as '栏目选择',
 bt,mtly,czy
  from table
 where T_table_ID in 
  (
   select  distinct s.t_table_id
     from
     ( 
      select distinct a.t_table_id,a.bt
        from  
        #temp1 a, 
        #temp2 b 
       where b.bbt like a.bbt and a.t_table_id<>b.t_table_id 
         and a.bbt not in ('aaaa','bbbb','cccc','dddd','eeee','ffff')
         and b.bbt not in ('aaaa','bbbb','cccc','dddd','eeee','ffff')
      union all 
      select distinct a.t_table_id,a.bt
        from  
        #temp3 a, 
        #temp4 b 
       where b.bbt like a.bbt and a.t_table_id<>b.t_table_id  
         and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
         and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
         and a.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' 
         and a.bbt not like '%aaaa%' and a.bbt not like '%bbbb%' and a.bbt not like '%cccc%'
         and a.bbt not like '%dddd%' and a.bbt not like '%eeee%' and a.bbt not like '%ffff%'
         and b.bbt not like '%'+(select right(convert(varchar(10),getdate()-1,20),2)+')') +'%' 
       ) s  
   )order by bt 
   --OPTION (loop join);
   --34
print convert(varchar,getdate(),9)  
/*
drop table #temp1  
drop table #temp2
drop table #temp3
drop table #temp4
*/

阅读(946) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~