上次我們在《 游標腳本性能問題解決與分析 》討論過動態游標的執行計劃如何選擇并且介紹了幾種游標的基本知識。本文我們接著研究鍵集游標選擇執行計劃的方式和影響因素。 這這里我們通過一個簡單的實驗來對比測試并且說明結果。 準備如下測試環境 : CREATE T
上次我們在《游標腳本性能問題解決與分析》討論過動態游標的執行計劃如何選擇并且介紹了幾種游標的基本知識。本文我們接著研究鍵集游標選擇執行計劃的方式和影響因素。
這這里我們通過一個簡單的實驗來對比測試并且說明結果。
準備如下測試環境:
CREATE TABLE [dbo].[test_cursor](
[number] [int] IDENTITY(1,1) NOT NULL,
[name] [varchar](500) NULL,
[xtype] [varchar](500) NULL,
[type] [varchar](500) NULL,
[parent_obj] [varchar](500) NULL,
[crdate] [datetime] NULL,
[id] [varchar](500) NULL,
[sysstat] [int] NULL,
CONSTRAINT [PK_test_cursor] PRIMARY KEY CLUSTERED
(
[number] ASC
)WITH (PAD_INDEX? = OFF, STATISTICS_NORECOMPUTE? = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS? = ON, ALLOW_PAGE_LOCKS? = ON) ON [PRIMARY]
) ON [PRIMARY]
反復運行下面的Insert語句15次以構造測試數據：?
insert into test_cursor (name,xtype,type, parent_obj,crdate,id,sysstat) select name,xtype,type, parent_obj,crdate,id,sysstat from AdventureWorks.dbo.sysobjects.
然后，為該表創建如下索引，
create index i_test_cursor_1 on test_cursor (id, crdate) include (number, name,xtype,type,parent_obj,sysstat)
create index i_test_cursor_2 on test_cursor(id,crdate)
執行以下Select語句，我們能得到下面的執行計劃和統計信息:
SELECT * FROM test_cursor WHERE id>’92’ ORDER BY crdate? –index seek on i_test_cursor_1
Table ‘test_cursor’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Rows??????????????????? Executes???????????? StmtText????????????????????????????????????????????????????????????????????????????????????????????????????????????
——————– ——————– —————————————————————————————————————————————
992????????????????????? 1??????????????????????? SELECT * FROM [test_cursor] WHERE [id]>@1????? ORDER BY [crdate] ASC
992????????????????????? 1??????????????????????? |–Sort(ORDER BY:([aa].[dbo].[test_cursor].[crdate] ASC))?????????????????????????????????????????????????????????
992????????????????????? 1????????????????????????|–Index Seek(OBJECT:([aa].[dbo].[test_cursor].[i_test_cursor_1]), SEEK:([aa].[dbo].[test_cursor].[id] > ’92’)
SELECT * FROM test_cursor WHERE id>’92’ ORDER BY number -index seek on i_test_cursor_1
Table ‘test_cursor’. Scan count 1, logical reads 17, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Rows??????????????????? Executes????????????? StmtText???????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??
——————– ——————– —————————————————————————————————————————————
992???????????????????? 1????????????????????????? SELECT * FROM [test_cursor] WHERE [id]>@1 ORDER BY [number] ASC???????????????????????????????????????????????????????????????????????
992???????????????????? 1????????????????????????? |–Sort(ORDER BY:([aa].[dbo].[test_cursor].[number] ASC))???????????????? ????????????
992???????????????????? 1??????????????????????????|–Index Seek(OBJECT:([aa].[dbo].[test_cursor].[i_test_cursor_1]), SEEK:([aa].[dbo].[test_cursor].[id] > ’92’) ORDERED FORWARD)
以上兩個ad-hoc的語句都是使用了我們創建的index? test_cursor迅速的定位和返回相應的行。
