ITPub博客

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

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

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

第二部分:游标的分类及特点

从上面两个脚本执行情况的对比中可以看出,游标的选择对语句执行的性能具有一定的影响。

SQL Server联机丛书上列出了不止十种游标类型,但是所有游标都可以被划到两大类别:

1.                  通过从首次得到结果的临时拷贝映像静态进行

2.                  每次fetch都通过动态进行且真正查阅表

STATICKEYSETREAD_ONLYFAST_FORWARD属于第一大类,FORWARD_ONLYDYNAMICOPTIMISTIC属于第二大类。

下面我们来进行一定的比较分析,并学习如何使用各种游标。在进行这部分之前,我们要引入另一个set statistics的方法:set statistics profile on

这个option会帮助我们打印出文本格式的执行计划和每一布的执行统计信息。这个部分的执行语句执行计划都是通过这个option打印的。

1.首先,我们把游标脚本中的SQL语句抽取出来直接运行而不使用游标:

SELECT       T1.*

FROM         dbo.S_AUDIT_ITEM T1           

LEFTOUTERJOINdbo.S_USER T2

ONT1.USER_ID=T2.PAR_ROW_ID   

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

ORDERBY       T1.OPERATION_DTDESC 

Table 'S_USER'. Scan count 1, logical reads 260, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 15, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

执行情况如下:逻辑读15次,使用的是索引查找(index seek

执行计划为:

 

Rows

Executes

StmtText

--------  --------------- ---------------------------------------------------------

4

1

SELECT T1.* FROM dbo.S_AUDIT_ITEM T1

 

 

 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 DESC           1   1   0     NULL        NULL

4

1

 |--Sort(ORDER BY:([T1].[OPERATION_DT] DESC))

4

1

 |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_

4

1

   |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1002], [T1].[ROW_ID]) OPTIMIZED)

4

1

  | |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3]AS [T1]), SEEK

4

1

  | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [

66908

4

     |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))

 

2.下面通过T-SQL语句打开一个游标。注意,这里创建的游标为dynamic类型,因为新声明的游标默认类型为dynamic。。本文开头使用的存储过程是调用API游标的写法,这里是用T-SQL语句打开游标,两种写法使用的游标类型和执行的语句是完全一样的。

declare@CONFLICT_IDint

declarecurTestcursor

FOR

   SELECT

       T1.CONFLICT_ID

   FROM         dbo.S_AUDIT_ITEM T1           

   LEFTOUTERJOINdbo.S_USER T2

   ONT1.USER_ID=T2.PAR_ROW_ID   

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

   ORDERBY       T1.OPERATION_DT 

 

OPENcurTest

FETCHNEXTFROMcurTest

INTO@CONFLICT_ID

CLOSEcurTest

deallocatecurTest

 

执行情况为:逻辑读明显增多,使用索引扫描(index scan

Table 'Worktable'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.              

Table 'S_USER'. Scan count 1, logical reads 64, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                

Table 'S_AUDIT_ITEM'. Scan count 1, logical reads 3026834,physical reads 1292, read-ahead reads 5574, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

执行计划如下:

 

 

Rows

Executes

StmtText

--------  --------------- ---------------------------------------------------------

1

1

FETCH NEXT FROM curTest

 

 

INTO @CONFLICT_ID

1

1

|--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as

1

1

   |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))

1

1

     |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as

1

1

        |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))

1007751

1

          | |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS  

1

1007751

          | |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS

16401

1

                |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))

 

接下来,我们使用其他类型的游标进行测试,从它们的测试结果会发现:

当使用STATICKEYSETREAD_ONLYFAST_FORWARD类型的游标,可以得到理想的执行计划(索引S_AUDIT_ITEM_M3上使用索引查找)。

但是,如果使用其他第二类游标类型,得到的执行计划就不甚理想了(索引S_AUDIT_ITEM_M4上使用索引扫描)。

 

从上面的测试,我们知道STATICKEYSETREAD_ONLYFAST_FORWARD游标可以带给我们同样的理想结果。那么,这些游标有什么共同点?

 

我们可以分析一下两大游标类型执行计划的不同:

1.      STATICKEYSETREAD_ONLYFAST_FORWARD类型游标的执行计划:

Executes             StmtText                                                                                                            

-------------------- --------------------------------------------------------------------------------------------------------------------

1                    OPEN curTest                                                                                                        

1                      |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[CONFLICT_ID] as

1                           |--Sequence Project(DEFINE:([Expr1008]=i4_row_number))                                                      

1                                |--Segment                                                                                             

1                                     |--Sort(ORDER BY:([T1].[OPERATION_DT] ASC))                                                       

1                                          |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[U

1                                               |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]) OPTIMIZED)     

1                                               |    |--Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M3] AS [T1]),

4                                               |    |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1]

4                                               |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))                              

                                                                                                   

Executes             StmtText                                                                                        StmtId      NodeId 

-------------------- ----------------------------------------------------------------------------------------------- ----------- --------

1                   FETCH NEXT FROM curTest INTO @CONFLICT_ID                       2           1        

1                     |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0)))ORDERED FORWARD)  2           2      

 

2.      dynamic类型游标的执行计划

Executes   StmtText                                                                                                                   

---------------------------------------------------------------------------------------------------------------------------------------

1         FETCH NEXT FROM curTest                                                                                                    

                                                                                                                                       

1            |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [testcursor].[dbo].[S_AUDIT_ITEM].[ROW_ID] as [T1].[ROW_ID]

1                 |--Compute Scalar(DEFINE:([Expr1008]=CWT_ROWID()))                                                                  

1                      |--Nested Loops(Left Outer Join, WHERE:([testcursor].[dbo].[S_AUDIT_ITEM].[USER_ID] as [T1].[USER_ID]=[testcurso

1                           |--Nested Loops(Inner Join, OUTER REFERENCES:([Uniq1004], [T1].[ROW_ID]))                                 

1                           |    |--Index Scan(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_M4] AS [T1]), ORDERED BACKWARD)

1007751                     |    |--Clustered Index Seek(OBJECT:([testcursor].[dbo].[S_AUDIT_ITEM].[S_AUDIT_ITEM_P1] AS [T1]), SEEK:([T

1                           |--Table Scan(OBJECT:([testcursor].[dbo].[S_USER] AS [T2]))                                                

 

比较一下两个执行计划的FETCH NEXT部分(SQL Server在游标打开阶段不会读取表):在第一个执行计划中,FETCH是直接从临时对象CWT中得到行,然后从CWT.ROWID中找到相应范围。而在第二个计划中,FETCH是动态的而且是真正对表进行了读取,从表中取得数据。

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

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

注册时间:2010-04-03

  • 博文量
    477
  • 访问量
    1466885