【IT168 技术】 前两篇文章分别为(游标脚本性能问题详解之案例实践篇)与(游标脚本性能问题详解之分类特点篇)
根据我们更多的分析和测试，以下几种方法都可以解决这个性能问题
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)
*/
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事件类如下表说明：
BinaryData image Resulting cursor type. Values are:
1 = Keyset
2 = Dynamic
4 = Forward only
8 = Static
16 = Fast forward
1 = Keyset
2 = Dynamic
4 = Forward only
8 = Static
16 = Fast forward
也就是说，我们使用方法1和2，实际上等同于我们使用了静态游标。在这个部分之前的测试中，我们已经知道了静态游标可以得到好的执行计划。在语句执行的过程中，游标隐式地转换为了static类型的。
