ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 游标脚本性能问题解决与分析 (3) - Cursor Performance Analysis

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

原创 Linux操作系统 作者:edwardking888 时间:2011-08-25 13:38:36 0 删除 编辑

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

 

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

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解决方案12可以由同一原因说明:

我们在定于游标的时候添加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

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

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

解决方案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
)

在分析34之前,我们检查一下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 bycolumn和同样的排序顺序的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 bycolumn所需要的顺序。

profiler trace里面我们发现,游标类型进行了转变,语句使用索引S_AUDIT_ITEM_M3去访问tableS_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 byindex,并且这个index也包含了其他有效的可以最大缩小数据反问的where 条件中所使用的column


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8183550/viewspace-705772/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-04-03

  • 博文量
    477
  • 访问量
    1436686