游标脚本性能问题详解之案例实践篇(1)

游标脚本性能问题详解之案例实践篇(1)

游标类型对性能影响的实例。下面的两个游标脚本分别创建并执行了dynamic和fast forward only两种类型的游标。

知识补充：

关系数据库中的操作会对整个行集起作用。由 SELECT 语句返回的行集包括满足该语句的 WHERE 子句中条件的所有行。这种由语句返回的完整行集称为结果集。应用程序并不总能将整个结果集作为一个单元来有效地处理。这些应用程序需要一种机制以便每次处理一行或一小部分行。游标不仅可提供这种机制，而且是对结果集的一种扩展。

游标通过执行以下操作来扩展结果集处理：

不理想的游标类型：(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 

理想的游标类型(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 

注：脚本中用到的和游标有关的存储过程，请参考：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  

[@ccopt

@p5=4098 转成16进制就是1002，对应的游标类型为Parameterized query + Dynamic cursor

@p5=4112 转成16进制就是1010，对应的游标类型为Parameterized query + Fast forward-only cursor

问题的现象是，左边的游标类型下，该脚本执行时间远大于右边的游标类型。