游标脚本性能问题解决与分析 (3) - Cursor Performance Analysis

第三部分、几种解决方法殊途同归

根据我们更多的分析和测试，以下几种方法都可以解决这个性能问题

1.        使用top 10

2.        使用with (INDEX=S_AUDIT_ITEM_M3)

3.        除去ORDER BY

4.        添加索引 ANZ_Custom_Audit_item_01

具体实现为：

declare @CONFLICT_ID int

declare curTest cursor

Dynamic

TYPE_WARNING

FOR

    SELECT  --top 10 /* 解决方案1 */

       T1.CONFLICT_ID

    FROM dbo.S_AUDIT_ITEM T1    -- with (INDEX=S_AUDIT_ITEM_M3) /* 解决方案2 */

    LEFT OUTER JOIN dbo.S_USER T2

    ON T1.USER_ID = T2.PAR_ROW_ID  

   WHERE      T1.BC_BASE_TBL = 'S_PARTY'AND   T1.RECORD_ID ='1-10350J' 

   ORDER BY       T1.OPERATION_DT    /*解决方案3 - Fast query when removed */

OPEN curTest

FETCH NEXT FROM curTest

INTO @CONFLICT_ID

CLOSE curTest

deallocate curTest

/* 解决方案4 */

/*

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

(

    [RECORD_ID] ASC,

    [BC_BASE_TBL] ASC,

    [OPERATION_DT] DESC

) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90)

*/

对于上述四种解决方案：

1．解决方案1和2可以由同一原因说明：

我们在定于游标的时候添加TYPE_WARNING来深入研究这个问题。添加TYPE_WARNING后再次执行语句，出现警告信息：The created cursor is not of the requested type（创建的游标不是所需的类型）。这个信息说明，语句实际执行的时候，游标类型发生了变化，不再是我们定义的动态游标了。为了跟踪游标类型的转换，我们打开Profiler Trace并把所有游标对象下面的事件都添加上，再次执行语句，看到profiler trace里面抓取了一个CursorImplictConversion。

这里的CursorImplictConversion事件类如下表说明：

也就是说，我们使用方法1和2，实际上等同于我们使用了静态游标。在这个部分之前的测试中，我们已经知道了静态游标可以得到好的执行计划。在语句执行的过程中，游标隐式地转换为了static类型的。

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 的定义：

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

)

为什么在动态游标的情况下，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游标类型隐式转换进行了解释：

从这个表格中的内容来看，只有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。