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

最新推荐文章于 2019-01-18 16:35:09 发布

cuge2009

最新推荐文章于 2019-01-18 16:35:09 发布

阅读量95

点赞数

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

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

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, NLINE = 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和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 ，因此这个语句可以快速的缩小数据筛选范围，并且提供动态游标需要滚动的列排序。