Blog - Title

键集游标对性能的影响

键集游标对性能的影响

Rate This
  • Comments 1

 

 上次我们在《游标脚本性能问题解决与分析》讨论过动态游标的执行计划如何选择并且介绍了几种游标的基本知识。本文我们接着研究键集游标选择执行计划的方式和影响因素。

这这里我们通过一个简单的实验来对比测试并且说明结果。

准备如下测试环境:

 

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迅速的定位和返回相应的行

 

然后,我们通过键集API游标的方式来执行相同的查询:

 

语句一:

declare @p1 int  set @p1=180150029 

declare @p3 int  set @p3=1 

declare @p4 int  set @p4=16388 

declare @p5 int  set @p5=180058 

exec sp_cursoropen @p1 output,N'SELECT * FROM test_cursor WHERE id>''92'' ORDER BY number',@p3 output,@p4 output,@p5 output  select @p1, @p3, @p4, @p5

 

Table 'Worktable'. Scan count 0, logical reads 1999, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                             

Table 'test_cursor'. Scan count 1, logical reads 229, 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                                                                                 

0                         0                        |--Compute Scalar(DEFINE:([Expr1007]=NULL, [Expr1008]=NULL, [Expr1009]=NULL, [Expr1010]=NULL, [Expr1011]=NULL, [Expr1012]=NULL, [Expr1013]=NULL

992                     1                        |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [aa].[dbo].[test_cursor].[number],[CWT].[CHECKSUM1] = [Chk1002],[CWT].[ROWI

0                         0                        |--Compute Scalar(DEFINE:([Expr1006]=(1)))  

992                     1                        |--Sequence Project(DEFINE:([Expr1005]=i4_row_number))                                                                         

992                   1                          |--Segment     

992                   1                          |--Clustered Index Scan(OBJECT:([aa].[dbo].[test_cursor].[PK_test_cursor]),  WHERE:([aa].[dbo].[test_cursor].[id]>[@1]

 

语句二: 

declare @p1 int  set @p1=180150029 

declare @p3 int  set @p3=1 

declare @p4 int  set @p4=16388 

declare @p5 int  set @p5=180058 

exec sp_cursoropen @p1 output,N'SELECT * FROM test_cursor WHERE id>''92'' ORDER BY crdate',@p3 output,@p4 output,@p5 output  select @p1, @p3, @p4, @p5

 

Table 'Worktable'. Scan count 0, logical reads 1999, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                                                                              

Table 'test_cursor'. Scan count 1, logical reads 229, 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                                                                                                            

0                          0                        |--Compute Scalar(DEFINE:([Expr1007]=NULL, [Expr1008]=NULL, [Expr1009]=NULL, [Expr1010]=NULL, [Expr1011]=NULL, [Expr1012]=NULL, [Expr1013]=NULL, [Expr1014]=NULL, [Expr1015]=NULL))            

992                      1                        |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [aa].[dbo].[test_cursor].[number],[CWT].[CHECKSUM1] = [Chk1002],[CWT].[ROWID] = [Expr1005],[CWT].[ROWSTATUS] = [Expr1006]))

0                          0                        |--Compute Scalar(DEFINE:([Expr1006]=(1)))   

992                      1                        |--Sequence Project(DEFINE:([Expr1005]=i4_row_number))            

992                      1                        |--Segment                                   

992                      1                        |--Sort(ORDER BY:([aa].[dbo].[test_cursor].[crdate] ASC)) 

992                     1                         |--Clustered Index Scan(OBJECT:([aa].[dbo].[test_cursor].[PK_test_cursor]), WHERE:([aa].[dbo].[test_cursor].[id]>[@1]))                                           

  

在这里我们发现两个语句的执行计划都改变为使用了聚集索引扫描,这里的聚集索引扫描就是表扫描。因此,这个语句实际的执行效率下降了。

这是因为对于键集游标的执行,它选择的索引的索引键必须包含主键或者唯一键所在的列,并且这个索引包含的列会被插入到一个临时结果集(CWT)中。

 

最后,我们再来测试使用T-SQL的游标的结果。

动态游标:

语句一:

DECLARE vendor_cursor CURSOR DYNAMIC    FOR

SELECT * FROM test_cursor WHERE id>'92' ORDER BY number

OPEN vendor_cursor;

 

我们发现,并没有执行计划产生,这是因为动态游标在声明和打开的过程中,并不会做任何事。

FETCH NEXT FROM vendor_cursor

 

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 'test_cursor'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                                           

Rows                  Executes             StmtText                                                                                                                                                      

-------------------- -------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------

1                        1                           FETCH NEXT FROM vendor_cursor                                                                                                                                

1                       1                           |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [aa].[dbo].[test_cursor].[number],[CWT].[CHECKSUM1] = [Chk1002],[CWT].[ROWID] = [Expr1005]))

1                       1                           |--Compute Scalar(DEFINE:([Expr1005]=CWT_ROWID()))                             

1                       1                           |--Clustered Index Scan(OBJECT:([aa].[dbo].[test_cursor].[PK_test_cursor]),  WHERE:([aa].[dbo].[test_cursor].[id]>'92') ORDERED FORWARD)  

 

 语句在第一次fetch的时候开始执行。这里动态游标也选择的cluster index。在另一篇文章<游标性能问题解决与分析>中我详细阐述过为什么这种情况下动态游标会选择cluster index。这里简单的说明,动态游标定于的语句中order by的列必须包含在该语句所使用的index中。这里我们的index定义没有包含number列,因此我们创建的index没有办法被动态游标选择。

 

语句二:

DECLARE vendor_cursor CURSOR DYNAMIC   TYPE_WARNING FOR

SELECT * FROM test_cursor WHERE id>'95' ORDER BY crdate

 

当我们用这条语句定义cursor的时候,我们发现SQL Server返回了这句话:

The created cursor is not of the requested type.

 

我们收集profiler trace,我们发现该游标被转换成了键值游标。

 

在我们执行open cursor的时候,这里就输出了执行计划如下:

 

Table 'Worktable'. Scan count 0, logical reads 1998, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                                  

Table 'test_cursor'. Scan count 1, logical reads 229, 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                         OPEN vendor_cursor;                  

992                     1                         |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [aa].[dbo].[test_cursor].[number],[CWT].[CHECKSUM1] = [Chk1002],[CWT].[ROWID] = [Expr

0                        0                          |--Compute Scalar(DEFINE:([Expr1006]=(1)))  

992                    1                          |--Sequence Project(DEFINE:([Expr1005]=i4_row_number))                      

992                    1                          |--Segment          

992                    1                          |--Sort(ORDER BY:([aa].[dbo].[test_cursor].[crdate] ASC))                               

992                   1                           |--Clustered Index Scan(OBJECT:([aa].[dbo].[test_cursor].[PK_test_cursor]), WHERE:([aa].[dbo].[test_cursor].[id]>'92'))   

 

动态游标会按照下面的方式转换成键值游标:

 

Step

Conversion triggered by

Forward-only

Keyset-driven

Dynamic

Go to step

1

查询中FROM从句没有查阅任何表

Static

Static

Static

完成

2

查询包括:集合了GROUP BY UNION DISTINCT的选择列表

Static

Static.

Static

完成

3

查询产生了一个内部工作表,比如,ORDER BY的列没有被索引覆盖到

keyset.

 

keyset.

5

4

查询在链接服务器(linked server)中查阅远程表

keyset.

 

keyset.

5

5

查询查阅了至少一个没有唯一索引的表,仅适用于T-SQL游标。

 

static.

 

完成

 

在这里,我们定义的动态游标遵循了原则3 order by的列没有被索引覆盖,因此转换为键值游标。但是我们回头检查索引的定义: create index i_test_cursor_2 on test_cursor(id,crdate)

这个索引是包含了order by的列crdate这是什么原因呢?

 

接下来我增加了一个新的索引:create index i_test_cursor_3 on test_cursor(crdate,id)

 

增加了这个索引以后,动态游标就没有被转换为键值游标了。

 

执行计划在执行了fetch cursor才输出:

 

Table 'Worktable'. Scan count 0, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical

Table 'test_cursor'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physic

Rows   Executes  StmtText                                                                                               

------ --------- --------------------------------------------------------------------------------------------------------

1      1         FETCH NEXT FROM vendor_cursor                                                                          

1      1           |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [AdventureWorks].[dbo].[test_cursor].[n

1      1                |--Compute Scalar(DEFINE:([Expr1005]=CWT_ROWID()))                                              

1      1                     |--Nested Loops(Inner Join, OUTER REFERENCES:([AdventureWorks].[dbo].[test_cursor].[number])

1      1                          |--Index Scan(OBJECT:([AdventureWorks].[dbo].[test_cursor].[i_test_cursor_3]),  WHERE:(

1      1                          |--Clustered Index Seek(OBJECT:([AdventureWorks].[dbo].[test_cursor].[PK_test_cursor]),

 

这里选择了我新增加的index,并且逻辑读远远小于做表扫描的执行计划。因此,如果对着两个语句统一使用动态游标,代价就是要新增加一个索引。

 

静态游标的结果:

语句一:

DECLARE vendor_cursor CURSOR static        TYPE_WARNING FOR

SELECT * FROM test_cursor WHERE id>'95' ORDER BY crdate

OPEN vendor_cursor;

fetch vendor_cursor

 

Table 'Worktable'. Scan count 0, logical reads 585, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physi

Table 'test_cursor'. Scan count 1, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physi

Rows    Executes   StmtText                                                                                            

------- ---------- -----------------------------------------------------------------------------------------------------

285     1          OPEN vendor_cursor;                                                                                 

285     1            |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [AdventureWorks].[dbo].[test_cursor]

285     1                 |--Sequence Project(DEFINE:([Expr1005]=i4_row_number))                                       

285     1                      |--Segment                                                                               

285     1                           |--Sort(ORDER BY:([AdventureWorks].[dbo].[test_cursor].[crdate] ASC))              

285     1                                |--Index Seek(OBJECT:([AdventureWorks].[dbo].[test_cursor].[i_test_cursor_1]),

                                                                                                                        

(6 row(s) affected)                                                                                                     

                                                                                                                                                                                                                                           

(1 row(s) affected)                                                                                                    

                                                                                                                        

Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physica

Rows    Executes   StmtText                                                                                        StmtI

------- ---------- ----------------------------------------------------------------------------------------------- -----

1       1          fetch vendor_cursor                                                                             2   

1       1            |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD)  2   

 

这里的执行计划很完美,就是我们希望看到的index seek

 

语句二: 

DECLARE vendor_cursor CURSOR static     FOR

SELECT * FROM test_cursor  WHERE id>'92' ORDER BY number

 

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor

 

Table 'Worktable'. Scan count 0, logical reads 2034, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                                         

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                        OPEN vendor_cursor;       

992                      1                        |--Clustered Index Insert(OBJECT:(CWT), SET:([CWT].[COLUMN0] = [aa].[dbo].[test_cursor].[number],[CWT].[COLUMN1] = [aa].[dbo].[test_cursor].[name],[CWT].[C

992                      1                        |--Sequence Project(DEFINE:([Expr1005]=i4_row_number))            

992                      1                        |--Segment                          

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)       

 

number      name                                                                                                                        

-----------   ----------------------------------------------------------------------------------------------------------------------------

40              sysxmlplacement                                                                                                                                                                                                          (1 row(s) affected)  

                                                                                                                       

              

Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob r

Rows                    Executes             StmtText                                                         

-------------------- -------------------- ----------------------------------------------------------------------------------------------

1                         1                         FETCH NEXT FROM vendor_cursor                    

1                         1                        |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD)

 

静态游标在这里选择了我们希望它使用的index seek的方式访问表,并且得到了逻辑读较低的结果。但是静态游标将所有的结果集预先写入了CWT的临时表。这样也带来了额外的开销。

 

以下是尝试“FAST_FORWARD”游标的结果,这种类型的游标对于两个语句都选择了逻辑读最低的执行方式:

 

语句一: 

DECLARE vendor_cursor CURSOR FAST_FORWARD      FOR

SELECT * FROM test_cursor  WHERE id>'92' ORDER BY crdate

 

OPEN vendor_cursor;

FETCH NEXT FROM vendor_cursor

 

Table 'Worktable'. Scan count 0, logical reads 2034, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                                  

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                        OPEN vendor_cursor;                      

992                      1                        |--Clustered Index Insert(OBJECT:(CWT), SET:([STREAM].[COLUMN0] = [aa].[dbo].[test_cursor].[number],[STREAM].[COLUMN1] = [aa].[dbo].[test_cursor].[n

992                      1                        |--Sequence Project(DEFINE:([I4Rank1003]=i4_row_number))   

992                      1                        |--Segment         

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') ORDERED FORWARD)

 

number    name                                                                                                                         

----------- ------------------------------------------------------------------------------------------------------------------------------

44            sysbinsubobjs                                                                                        

(1 row(s) affected)     

   

Table 'Worktable'. Scan count 0, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob rea

Rows                   Executes              StmtText                                                                                       

-------------------- -------------------- -----------------------------------------------------------------------------------------------

1                        1                          FETCH NEXT FROM vendor_cursor         

1                        1                         |--Clustered Index Seek(OBJECT:(CWT), SEEK:([CWT].[ROWID]=FETCH_RANGE((0))) ORDERED FORWARD)  

 

在这里fast_forward 游标选择了静态执行计划,我们可以看到这里的执行计划跟静态游标是一样的。

 

语句二:

DECLARE vendor_cursor CURSOR FAST_FORWARD      FOR

SELECT * FROM test_cursor  WHERE id>'92' ORDER BY number

 

OPEN vendor_cursor;

No execution plan printed.

 

FETCH NEXT FROM vendor_cursor

 

number     name                                                                                                                                                                    

----------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------

40            sysxmlplacement                                                                                                                                                         

(1 row(s) affected)     

                                                                                             

Table 'test_cursor'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.                       

Rows                   Executes              StmtText                                                                                                                                 

-------------------- -------------------- ------------------------------------------------------------------------------------------------------------------------------------------

1                        1                          FETCH NEXT FROM vendor_cursor        

1                        1                         |--Clustered Index Scan(OBJECT:([aa].[dbo].[test_cursor].[PK_test_cursor]),  WHERE:([aa].[dbo].[test_cursor].[id]>'92') ORDERED FORWARD)

 

在这条语句上fast_forward游标选择了动态执行计划,即跟动态游标在语句二上一样的执行计划。在这条语句上,动态执行计划是效率最高成本最低的

 

由此可见“Fast_Forward”游标在执行以上查询时,可以灵活的在动态和静态执行计划中选择性能最好的。因此,得出的结论是:在使用过程中,尽可能的使用“FAST_FORWARD”游标

 

 

Leave a Comment
  • Please add 4 and 1 and type the answer here:
  • Post
  • 这里的SQL脚本不能高亮显示吗?那样可读性会更好一些.

Page 1 of 1 (1 items)