游標腳本性能問題詳解之游標分類特點篇
從上篇游標腳本性能問題詳解之案例實踐篇兩個腳本執(zhí)行情況的對比中可以看出，游標的選擇對語句執(zhí)行的性能具有一定的影響。
在SQL Server聯(lián)機叢書上列出了不止十種游標類型，但是所有游標都可以被劃到兩大類別：
1. 通過從***得到結(jié)果的臨時拷貝映像靜態(tài)進行
2. 每次fetch都通過動態(tài)進行且真正查閱表
STATIC、KEYSET、READ_ONLY和FAST_FORWARD屬于***大類，F(xiàn)ORWARD_ONLY、DYNAMIC和OPTIMISTIC屬于第二大類。
下面我們來進行一定的比較分析，并學(xué)習如何使用各種游標。在進行這部分之前，我們要引入另一個set statistics的方法： set statistics profile on
這個option會幫助我們打印出文本格式的執(zhí)行計劃和每一布的執(zhí)行統(tǒng)計信息。這個部分的執(zhí)行語句執(zhí)行計劃都是通過這個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
執(zhí)行情況如下：邏輯讀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.
執(zhí)行計劃為：
- 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語句打開一個游標。注意，這里創(chuàng)建的游標為dynamic類型，因為新聲明的游標默認類型為dynamic。。本文開頭使用的存儲過程是調(diào)用API游標的寫法，這里是用T-SQL語句打開游標，兩種寫法使用的游標類型和執(zhí)行的語句是完全一樣的。
- 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
執(zhí)行情況為：邏輯讀明顯增多，使用索引掃描(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.
執(zhí)行計劃如下：
- 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]))
接下來，我們使用其他類型的游標進行測試，從它們的測試結(jié)果會發(fā)現(xiàn)：
當使用STATIC、KEYSET、READ_ONLY、FAST_FORWARD類型的游標，可以得到理想的執(zhí)行計劃(索引S_AUDIT_ITEM_M3上使用索引查找)。
但是，如果使用其他第二類游標類型，得到的執(zhí)行計劃就不甚理想了(索引S_AUDIT_ITEM_M4上使用索引掃描)。
從上面的測試，我們知道STATIC、KEYSET、READ_ONLY及FAST_FORWARD游標可以帶給我們同樣的理想結(jié)果。那么，這些游標有什么共同點?
我們可以分析一下兩大游標類型執(zhí)行計劃的不同：
1. STATIC、KEYSET、READ_ONLY、FAST_FORWARD類型游標的執(zhí)行計劃：
- 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類型游標的執(zhí)行計劃
- 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]))
比較一下兩個執(zhí)行計劃的FETCH NEXT部分(SQL Server在游標打開階段不會讀取表)：在***個執(zhí)行計劃中，F(xiàn)ETCH是直接從臨時對象CWT中得到行，然后從CWT.ROWID中找到相應(yīng)范圍。而在第二個計劃中，F(xiàn)ETCH是動態(tài)的而且是真正對表進行了讀取，從表中取得數(shù)據(jù)。
