【IT168 专稿】前文：游标脚本性能问题详解之案例实践片篇
从上篇文章两个脚本执行情况的对比中可以看出，游标的选择对语句执行的性能具有一定的影响。
在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
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.
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]))
-------------------- -------------------- ---------------------------------------------------------------------------------------------------------
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]))
