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