遊標指令碼效能問題解決與分析 (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

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

接下來，我們使用其他型別的遊標進行測試，從它們的測試結果會發現：

當使用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是動態的而且是真正對錶進行了讀取，從表中取得資料。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8183550/viewspace-705771/，如需轉載，請註明出處，否則將追究法律責任。

遊標指令碼效能問題解決與分析 (2) - Cursor Performance Analysis

相關文章