【IT168 专稿】游标类型对性能影响的实例。下面的两个游标脚本分别创建并执行了dynamic和fast forward only两种类型的游标。
不理想的游标类型：(dynamic游标)
declare @p1 int set @p1=NULL
declare @p2 int set @p2=0
declare @p5 int set @p5=4098
declare @p6 int set @p6=8193
declare @p7 int set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',
N'
SELECT T1.CONFLICT_ID
FROM dbo.S_AUDIT_ITEM T1
LEFT OUTER JOIN dbo.S_USER T2
ON T1.USER_ID = T2.PAR_ROW_ID
WHERE ((T1.BC_BASE_TBL = @P1)
AND (T1.RECORD_ID = @P2))
ORDER BY T1.OPERATION_DT DESC
OPTION (FAST 40)
',
@p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'
print 'fetch'
exec sp_cursorfetch @p2,2,4,1
exec sp_cursorclose @p2
declare @p2 int set @p2=0
declare @p5 int set @p5=4098
declare @p6 int set @p6=8193
declare @p7 int set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',
N'
SELECT T1.CONFLICT_ID
FROM dbo.S_AUDIT_ITEM T1
LEFT OUTER JOIN dbo.S_USER T2
ON T1.USER_ID = T2.PAR_ROW_ID
WHERE ((T1.BC_BASE_TBL = @P1)
AND (T1.RECORD_ID = @P2))
ORDER BY T1.OPERATION_DT DESC
OPTION (FAST 40)
',
@p5 output,@p6 output,@p7 output,'1-10350J','S_PARTY'
print 'fetch'
exec sp_cursorfetch @p2,2,4,1
exec sp_cursorclose @p2
理想的游标类型(fast forward only游标)
declare @p1 int set @p1=NULL
declare @p2 int set @p2=0
declare @p5 int set @p5=4112
declare @p6 int set @p6=8193
declare @p7 int set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',
N'
SELECT T1.CONFLICT_ID
FROM dbo.S_AUDIT_ITEM T1
LEFT OUTER JOIN dbo.S_USER T2
ON T1.USER_ID = T2.PAR_ROW_ID
WHERE ((T1.BC_BASE_TBL = @P1)
AND (T1.RECORD_ID = @P2))
ORDER BY T1.OPERATION_DT DESC
OPTION (FAST 40)
',
@p5 output,@p6 output,@p7 output,'S_SRV_REQ','1-WUQTM6'
select @p1, @p2, @p5, @p6, @p7
print '2'
exec sp_cursorfetch @p2,2,1,1
print '3'
exec sp_cursorclose @p2
declare @p2 int set @p2=0
declare @p5 int set @p5=4112
declare @p6 int set @p6=8193
declare @p7 int set @p7=0
exec sp_cursorprepexec @p1 output,@p2 output,N'@P1 varchar(30),@P2 varchar(15)',
N'
SELECT T1.CONFLICT_ID
FROM dbo.S_AUDIT_ITEM T1
LEFT OUTER JOIN dbo.S_USER T2
ON T1.USER_ID = T2.PAR_ROW_ID
WHERE ((T1.BC_BASE_TBL = @P1)
AND (T1.RECORD_ID = @P2))
ORDER BY T1.OPERATION_DT DESC
OPTION (FAST 40)
',
@p5 output,@p6 output,@p7 output,'S_SRV_REQ','1-WUQTM6'
select @p1, @p2, @p5, @p6, @p7
print '2'
exec sp_cursorfetch @p2,2,1,1
print '3'
exec sp_cursorclose @p2
注：脚本中用到的和游标有关的存储过程，请参考：http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec
如何解读游标的类型
sp_cursorprepexec [@handle =] statement_handle OUTPUT,
[@cursor =] cursor_handle OUTPUT,
[@paramdef =] N'parameter_name data_type, [,...n]'
[@stmt =] N'stmt',
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]
@scrollopt
[@cursor =] cursor_handle OUTPUT,
[@paramdef =] N'parameter_name data_type, [,...n]'
[@stmt =] N'stmt',
[, [@scrollopt =] scroll_options OUTPUT]
[, [@ccopt =] concurrency_options OUTPUT]
[, [@rowcount =] rowcount OUTPUT]
@scrollopt
▲
