SQL中键集游标选择执行计划的方式和影响因素

上次我们在《 游标脚本性能问题解决与分析 》讨论过动态游标的执行计划如何选择并且介绍了几种游标的基本知识。本文我们接着研究键集游标选择执行计划的方式和影响因素。 这这里我们通过一个简单的实验来对比测试并且说明结果。 准备如下测试环境 : 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迅速的定位和返回相应的行。