技术开发 频道

游标脚本性能问题详解之解决方案篇

 

CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M3] ON [dbo].[S_AUDIT_ITEM]

  (

  [RECORD_ID]
ASC,

  [BUSCOMP_NAME] DESC,

  [OPERATION_DT]
ASC

  )

  CREATE NONCLUSTERED INDEX [S_AUDIT_ITEM_M4]
ON [dbo].[S_AUDIT_ITEM]

  (

  [OPERATION_DT] DESC

  )

  2.解决方案3和4也可以用同一个原因来说明。

  解决方案3去掉了ORDER BY语句,从使用索引扫描(index scan)变成了索引查找(index seek),不用转换游标类型就解决了该问题。

  对于解决方案4,新建的索引是一个cover index,

  所以如果我们创建下面的索引,一样可以解决这个性能问题。

  

CREATE NONCLUSTERED INDEX [ANZ_Custom_Audit_item_01] ON [dbo].[S_AUDIT_ITEM]

  (

  [RECORD_ID]
ASC,

  [BC_BASE_TBL]
ASC,

  [OPERATION_DT] DESC

  )

  在分析3和4之前,我们检查一下index 的定义:

    为什么在动态游标的情况下,SQL Server选择这个不好的索引呢?在语句中有这个一个排序的子句:ORDER BY T1.OPERATION_DT desc

  如果我们使用动态游标的时候,又同时指定了order by的顺序,那么这个动态游标一定要保证其滚动的顺序和位置。而动态游标又是动态从表上获取数据,因此动态游标+order by必须要在一个包含了order by的column和同样的排序顺序的index上滚动,这就是为什么SQL Server 坚持在动态游标下使用index S_AUDIT_ITEM_M4,即使SQL Server知道使用index S_AUDIT_ITEM_M4会导致不好的性能。

  所以如果我们移掉order by子句,SQL Server就不再坚持使用index S_AUDIT_ITEM_M4。或者我们创建新的cover index,这个index包含了语句所使用的所有column,并且包含了order by 的列OPERATION_DT ,因此这个语句可以快速的缩小数据筛选范围,并且提供动态游标需要滚动的列排序。

  一个有趣的测试

  最后,让我们做一个有趣的测试:

  仍旧使用上面的游标脚本,移除索引ANZ_Custom_Audit_item_01和索引S_AUDIT_ITEM_M4,会发生什么现象?这时语句中还是包含了order by,但是表上没有任何index提供了order by的column所需要的顺序。

  从profiler trace里面我们发现,游标类型进行了转变,语句使用索引S_AUDIT_ITEM_M3去访问table S_AUDIT_ITEM:创建的游标类型不是所需类型。动态游标变成了静态游标。

  下表对SQL Server游标类型隐式转换进行了解释:

  

Step Conversion triggered by Forward-only Keyset-driven Dynamic Go to step

  1查询中FROM从句没有查阅任何表变Static变Static变Static完成

  2查询包括:集合了GROUP BY UNION DISTINCT的选择列表变Static变Static.变Static完成

  3查询产生了一个内部工作表,比如,ORDER BY的列没有被索引覆盖到变 keyset. 变keyset.至5

  4查询在链接服务器(linked server)中查阅远程表变 keyset. 变 keyset.至5

  5查询查阅了至少一个没有唯一索引的表,仅适用于T
-SQL游标。 变 static. 完成

  从这个表格中的内容来看,只有Forward_only, Keyset_driven 和Dynamic游标会发生类型转换。

  根据表格内容,回过来分析我们上面的脚本,它发生变化的步骤为:步骤3+步骤5,动态游标由于条件3,转成了keyset游标,然后又由于条件5,变成了静态游标。

  到此处,我们在这个问题中提供了4种解决问题的方法,并逐一分析了每种方法能够解决问题的原因。这个性能问题其实可以总结为两句话:

  1. 尽量使用Fast Forward only游标。

  2. 如果必须使用dynamic游标,而定义游标的语句又有order by,保证table上有一个具有可以用来order by的index,并且这个index也包含了其他有效的可以最大缩小数据反问的where 条件中所使用的column。

0
相关文章