zping

SQL SERVER，ORACLE数据库分析,设计,研究,优化,重构等

统计

常用链接

留言簿

友情衔接

阅读排行榜

评论排行榜

SQL 语句优化--IN语句优化案例

    今天客户系统升级，通过DMVs性能分析查了一下，升级后发现一个语句执行时间比较长，执行语句要好几秒钟，调出语句如下：

select distinct field003 from ufi2j0n11179717502375 where 
field003 not in ('','40288135120d660501120de2f8870140','40288135120d660501120de4b9ee014b',
'40288135120d660501120de9c3ba016c','40288135120d660501120df0460c01b2','40288135120d660501120df1dc2d01d3') and   requestid in (select requestid from ufi8s6u81179717475734 where field001 in (select requestid from  uft3a6h61176948132312 where field066 is not  null and  field197 between convert(datetime, '2008-08-16') and convert(datetime,'2008-09-15')) )

   后来看了一下，这几表的数据

-- 表 dbo.uft3a6h61176948132312 ： 988行
-- 表：dbo.ufi2j0n11179717502375  ：713行
-- 表： dbo.ufi8s6u81179717475734 ：  273行

   发现这三张表都没有超过1千行数据，建立索引意义不大，为何如此慢，看看执行计划：

 

  分析：发现是表dbo.uft3a6h61176948132312 访问开销最大，但表中数据不到一千行。执行看看结果：

(5 行受影响)
表 'uft3a6h61176948132312'。扫描计数 1，逻辑读取 27161 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'ufi8s6u81179717475734'。扫描计数 1，逻辑读取 37 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'ufi2j0n11179717502375'。扫描计数 1，逻辑读取 46 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

    这里发现表uft3a6h61176948132312的访问有近3万次IO。 一开始以为是in的缘故，将in换成exists结果也是一样,这时考虑用inner join来重新写sql语句，语句如下：

select distinct a.field003 from ufi2j0n11179717502375  a
inner join ufi8s6u81179717475734 b on a.requestid=b.requestid
inner join  uft3a6h61176948132312 c on b.field001=c.requestid
where a.field003 not in ('','40288135120d660501120de2f8870140','40288135120d660501120de4b9ee014b','40288135120d660501120de9c3ba016c','40288135120d660501120df0460c01b2','40288135120d660501120df1dc2d01d3') and   c.field066 is not  null and  c.field197 between 
convert(datetime, '2008-08-16') and convert(datetime,'2008-09-15')

  查看执行计划：

  分析：这时发现执行计划发生了变化，最外层的表变成了dbo.ufi2j0n11179717502375，执行结果如下：

(5 行受影响)
表 'Worktable'。扫描计数 0，逻辑读取 0 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'ufi2j0n11179717502375'。扫描计数 1，逻辑读取 46 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'ufi8s6u81179717475734'。扫描计数 1，逻辑读取 37 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
表 'uft3a6h61176948132312'。扫描计数 1，逻辑读取 421 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。

   这时发现整个IO次数比先前少了很多。

  总结：

      根据这两个执行计划分析，sql server 2005优化器对于in语句没有正确选择联结算法，错误的采用了采用了”嵌套循环算法“。

      根据嵌套循环算法IO次数：421*（其他两个表的关联匹配行数）≈27163次 （访问表“uft3a6h61176948132312”IO次数），而这时由于返回的行数比较多，又没有建立索引，这时最佳的算法是使用“hash联结算法“

 热爱祖国  http://www.s256.com
0
0
(请您对文章做出评价)
« 上一篇：sql server性能分析--定时收集系统运行情况
» 下一篇：SQL Server 查看数据库基本信息

posted on 2008-09-15 20:15 zping 阅读(4205) 评论(18)  编辑 收藏 所属分类: SQL 技巧和优化

评论

#1楼 2008-09-15 21:06 北京奥运      

DMVs性能分析
是什么东西，请介绍下   回复  引用  查看    

#2楼 2008-09-15 21:27 犇牛牛      

不错 还是尽量不用in语句
要仔细研究下哈希匹配和嵌套循环

另外你们客户用的是SQL2005 是正版的吗？呵呵   回复  引用  查看    

#3楼 2008-09-15 21:32 天龙之吻      

理论上来说，这样的in语句属于相关子查询，自然采用嵌套循环算法。
而且还嵌套了2次，效率低是可以想象的。
嵌套循环算法破坏了数据库中以"集合"来处理数据的基本原则。
除非不得已，采用同等条件的join语句效率要高很多。
  回复  引用  查看    

#4楼[楼主] 2008-09-15 21:34 zping.com      

@北京奥运
DMV就是SQL Server 2005中的动态管理视图。   回复  引用  查看    

#5楼 2008-09-15 21:41 希冀      

好帖,顶一下!^_^   回复  引用  查看    

#6楼 2008-09-15 21:58 TestSql[未注册用户]

www.zping.com
楼主的个人站很久没有更新了吧？还有漏洞哦。   回复  引用    

#7楼 2008-09-15 22:25 狼Robot      

@TestSql
楼上真牛.   回复  引用  查看    

#8楼[楼主] 2008-09-15 22:30 zping.com      

@TestSql
网站是4年前做的，问题很多，但没时间改，也没有在学校的热情了，一直在外面到处流浪，没时间去更新。现在想把网站重新改版一下，但一直没时间。
  回复  引用  查看    

#9楼 2008-09-15 23:47 OK_008      

第1条语句就写得非常的冗余了，何谈性能呢！？？？？
而且，这里
c.field197 between convert(datetime, '2008-08-16') and convert(datetime,'2008-09-15')

不妨，试试看看这样的写法：

c.field197 >='20080816' And c.field197 <'20080916'




   回复  引用  查看    

#10楼 2008-09-16 08:38 Tracy Chuang[未注册用户]

@OK_008
楼主的写法没有问题，按照OK_008的建议，会造成数据表列的类型转换，这样反而更慢。。。   回复  引用    

#11楼 2008-09-16 09:03 有容乃大      

博主的截图是用什么工具，什么时候给介绍一下。

-----------------------------------------------
发布.net项目开发工具(V3.0 )：
http://www.cnblogs.com/mrhgw/archive/2008/08/06/1261664.html
http://www.mrhgw.cn   回复  引用  查看    

#12楼[楼主] 2008-09-16 09:15 zping.com      

@有容乃大
截图工具用的是HYPERSNAP 6   回复  引用  查看    

#13楼 2008-09-16 09:38 看看[未注册用户]

DMV and DMFs

sys.dm_exec_cached_plans
sys.dm_exec_plan_attributes
sys.dm_exec_sql_text   回复  引用    

#14楼 2008-09-16 11:08 深蓝      

这些表的命名真是奇怪，不是给人看的。什么系统要这样设计表啊？为什么要这样做呢？   回复  引用  查看    

#15楼[楼主] 2008-09-16 11:21 zping.com      

@深蓝
这个就只能是微软的人才知道啊！   回复  引用  查看    

#16楼 2008-09-25 16:09 柳永法[未注册用户]

DMV就是SQL Server 2005中的动态管理视图


我怎么看里面的都跟表似的，而不是像你发布的这样，是图的，能不能给介绍一下这方面的文章？   回复  引用    

#17楼 2008-09-26 04:51 啊合[未注册用户]

一般我们喜欢用临时表，连接太多，字段太长，自己都不知道什么意思了。   回复  引用    

#18楼 2009-09-08 22:42 邀月      

感谢楼主分享！   回复  引用  查看