上次我們?cè)凇?游標(biāo)腳本性能問題解決與分析 》討論過動(dòng)態(tài)游標(biāo)的執(zhí)行計(jì)劃如何選擇并且介紹了幾種游標(biāo)的基本知識(shí)。本文我們接著研究鍵集游標(biāo)選擇執(zhí)行計(jì)劃的方式和影響因素。 這這里我們通過一個(gè)簡(jiǎn)單的實(shí)驗(yàn)來對(duì)比測(cè)試并且說明結(jié)果。 準(zhǔn)備如下測(cè)試環(huán)境 : CREATE T

上次我們?cè)凇队螛?biāo)腳本性能問題解決與分析》討論過動(dòng)態(tài)游標(biāo)的執(zhí)行計(jì)劃如何選擇并且介紹了幾種游標(biāo)的基本知識(shí)。本文我們接著研究鍵集游標(biāo)選擇執(zhí)行計(jì)劃的方式和影響因素。

這這里我們通過一個(gè)簡(jiǎn)單的實(shí)驗(yàn)來對(duì)比測(cè)試并且說明結(jié)果。

準(zhǔn)備如下測(cè)試環(huán)境:

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]

反復(fù)運(yùn)行下面的Insert語句15次以構(gòu)造測(cè)試數(shù)據(jù)：

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.

然后，為該表創(chuàng)建如下索引，

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)

執(zhí)行以下Select語句，我們能得到下面的執(zhí)行計(jì)劃和統(tǒng)計(jì)信息:

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)

以上兩個(gè)ad-hoc的語句都是使用了我們創(chuàng)建的index test_cursor迅速的定位和返回相應(yīng)的行。

聲明：本網(wǎng)頁內(nèi)容旨在傳播知識(shí)，若有侵權(quán)等問題請(qǐng)及時(shí)與本網(wǎng)聯(lián)系，我們將在第一時(shí)間刪除處理。TEL:177 7030 7066 E-MAIL:11247931@qq.com