Detailed explanation of performance issues of cursor scripts (1)

Instances with performance impact of the cursor type. The following two cursor scripts create and execute two types of cursor: dynamic and fast forward only.

Knowledge supplement:

Operations in a relational database work for the entire row set. The row set returned by the SELECT statement includes all rows that meet the conditions in the WHERE clause of the statement. The complete row set returned by the statement is called the result set. The application does not always take the entire result set as a unit for effective processing. These applications require a mechanism to process one or a small number of rows at a time. A cursor not only provides this mechanism, but also an extension of the result set.

The cursor expands the result set processing by performing the following operations:

Unsatisfactory cursor type: (dynamic Cursor)

 
 

  
  
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 
 
 

Ideal cursor type (fast forward only cursor)

 
 

  
  
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 
 
 

Note: For cursor-related stored procedures in the script, see: http://jtds.sourceforge.net/apiCursors.html#_sp_cursorprepexec

I. how to interpret the cursor type

 
 

  
  
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 convert to hexadecimal 1002 and the corresponding cursor type is Parameterized query + Dynamic cursor.

@ P5 = 4112 convert to hexadecimal 1010 and the corresponding cursor type is Parameterized query + Fast forward-only cursor.

The problem is that in the cursor type on the left, the script execution time is much longer than the cursor type on the right.