游标脚本性能问题详解之游标分类特点篇-技术开发专区

游标脚本性能问题详解之游标分类特点篇

作者：胡铭娅编辑：胡铭娅 2011-04-06 00:05 IT168网站原创

　　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]))

第1页：游标脚本中的SQL语句抽取第2页：通过T-SQL语句打开一个游标第3页：分析两大游标类型执行计划不同

关注我们