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

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

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

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

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

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

 STATIC、KEYSET、READ_ONLY和FAST_FORWARD属于第一大类，FORWARD_ONLY、DYNAMIC和OPTIMISTIC属于第二大类。

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

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

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

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 

 

执行情况如下：逻辑读15次，使用的是索引查找（index seek）

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.

执行计划为：

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 4 | |--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_ID int

declare curTest cursor

FOR

    SELECT

       T1.CONFLICT_ID

    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

OPEN curTest

FETCH NEXT FROM curTest

INTO @CONFLICT_ID

CLOSE curTest

deallocate curTest

执行情况为：逻辑读明显增多，使用索引扫描（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]))

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

当使用STATIC、KEYSET、READ_ONLY、FAST_FORWARD类型的游标，可以得到理想的执行计划（索引S_AUDIT_ITEM_M3上使用索引查找）。

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

从上面的测试，我们知道STATIC、KEYSET、READ_ONLY及FAST_FORWARD游标可以带给我们同样的理想结果。那么，这些游标有什么共同点？

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

1. STATIC、KEYSET、READ_ONLY、FAST_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是动态的而且是真正对表进行了读取，从表中取得数据。