SQLTeam.com | Weblogs | Forums

How can i increase the performance of cursor?

sql2012

#1

Hi,
Here i am using cursor and i have 189561 record .In these record i am performing updation in same table and insertion in different table.It take much time .is there any alternate to avoid the cursor .I am giving my code which i wriiten .Please check and give me alternate solution.

//code start

ALTER procedure [dbo].[updateNapsRecon]

as
DECLARE @irisRet_Ref_No varchar(50),
@glRet_Ref_No varchar(50),
@irisRecon_id varchar(50),
@glRecon_id varchar(50),
@iris_sid varchar(50),
@gl_sid varchar(50),
@iris_amt varchar(50),
@gl_amt varchar(50),
@genrateRecon_id varchar(50),
@genrateSid varchar(50),
@qcbRet_Ref_No varchar(50),
@qcb_sid varchar(50),
@qcb_amt varchar(50),
@qcb_recon_id varchar(50),
@irisversion int,
@glversion int,
@qcbversion int,
@ErrorMessage varchar(2000)
,@ErrorSeverity tinyint
,@ErrorState tinyint,
@count int=1

-- Here i create cursor name is c3 according to require condition
DECLARE c4 CURSOR FORWARD_only FOR SELECT i.SID,i.RETR_REF_NO,i.AMT_TRAN ,i.RECON_ID,g.SID,g.RETR_REF_NO,g.TRA_AMT ,g.RECON_ID,q.SID,q.RETR_REF_NO,q.TRANS_AMOUNT ,q.RECON_ID
, i.version,g.version,q.version FROM
IRIS_STG i inner join GL_1015_STG g
on i.RETR_REF_NO=g.RETR_REF_NO inner join QCB_STG q on
q.RETR_REF_NO=i.RETR_REF_NO where
q.TRANS_AMOUNT=i.AMT_TRAN and i.AMT_TRAN=
g.TRA_AMT and i.PAN=q.CARD_NUMBER and
i.SYS_TRACE_AUDIT_NO=g.stan
and q.BIN IN(434141,484823,428246,472839)
AND q.CHANNEL='ATM' AND q.ISS_BANK_CODE=3
AND q.ACQ_BANK_CODE!=3
AND q.TRAN_TYPE IN(10,98,99)
AND (q.RECON_ID IS NULL OR q.RECON_STATUS='AU')

	and  g.AUTHORIZING_CHANNEL IN('9999')
	 AND g.PROC_CODE=01 AND g.ACQUIRING_CHANNEL_ID=28 
	 AND g.TELL_ID=9953 AND (g.RECON_ID IS NULL OR g.RECON_STATUS='AU')

	 and 

	 i.AUTHORIZER IN('9999') 
	AND i.ACQUIRING_CHANNEL_ID=28 AND i.RESP_CODE IN('000','036','037') AND i.PROC_CODE_FIRST_2=01
	AND i.BIN IN(434141,484823,428246,472839) 
	AND (i.RECON_ID IS NULL OR i.RECON_STATUS='AU')

-- open the cursor
OPEN c4;

-- fetch one row column value and store in to @colName,@colType
FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@iris_amt,@irisRecon_id,@gl_sid,@glRet_Ref_No,@gl_amt,@glRecon_id,
@qcb_sid,@qcbRet_Ref_No,@qcb_amt,@qcb_recon_id,
@irisversion,@glversion,@qcbversion;

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN

if @irisRecon_id is null and @glRecon_id is null and @qcb_recon_id is null
begin
begin try
insert into IRIS_AUDIT select * from IRIS_stg where sid=@iris_sid;
insert into GL_1015_AUDIT select * from GL_1015_STG where sid=@gl_sid;
insert into QCB_AUDIT select * from QCB_STG where sid=@qcb_sid;
set @genrateRecon_id=@irisRet_Ref_No+''+@iris_sid;
set @irisversion=@irisversion+1;
set @glversion=@glversion+1;
set @qcbversion=@qcbversion+1;
update IRIS_STG set RECON_ID=cast(@genrateRecon_id as bigint),RECON_STATUS='AM' ,VERSION=@irisversion where sid=@iris_sid and AMT_TRAN=@iris_amt;
update GL_1015_STG set RECON_ID=cast(@genrateRecon_id as bigint),RECON_STATUS='AM' ,VERSION=@glversion where sid=@gl_sid and TRA_AMT=@gl_amt;
update QCB_STG set RECON_ID=cast(@genrateRecon_id as bigint),RECON_STATUS='AM' ,VERSION=@qcbversion where sid=@qcb_sid and TRANS_AMOUNT=@qcb_amt;
print @count
set @count=@count+1

end try
begin catch
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState)

BREAK

end catch
end

-- fetch one row column value and store in to @colName,@colType inside loop

FETCH NEXT FROM c4 INTO @iris_sid,@irisRet_Ref_No,@iris_amt,@irisRecon_id,@gl_sid,@glRet_Ref_No,@gl_amt,@glRecon_id,
@qcb_sid,@qcbRet_Ref_No,@qcb_amt,@qcb_recon_id,@irisversion,@glversion,@qcbversion;

END
--close while loo body

-- close the cursor c3
CLOSE c4;

--DEALLOCATE cursor c3 that mean every execution time it will treate as new one
DEALLOCATE c4;

//code end

thanks...........


#2

I would start by checking the performance of the SELECT that builds the data that it will cursor over. How many rows does it return? Add SET STATISTICS IO ON; SET STATISTICS TIME ON; to the top of your script, run the entire thing and then show us the output that's produced by STATISTICS commands.

I would also look at the performance of the UPDATEs. Are the WHERE clauses indexed?

I am sure you could rewrite this to be set-based, but I don't have time to work on that. Maybe someone else can help with that.


#3

Instead of using cursor copy you can copy ids and other relevant data into temp table using output clause while doing an update. And then you can perform insert based on information in this temp table.

Here is the idea ...

create table #ids (id int)

update ATable set <...>
output inserted.* into #ids
from ATable t1
join OtherTable t2
on t1.id = t2.id

select * from #ids

I hope it does make sense


#4

Hi leonids2005 ,

thanks to given solution

set @genrateRecon_id=@irisRet_Ref_No+''+@iris_sid;
update IRIS_STG set RECON_ID=cast(@genrateRecon_id as bigint),

here i generate recon_id dynamically based on iris_id and iris_retr_ref_no .
Is it possible in above discussion ,if it possible then please explain me this one in brief.

thanks & regards,
shivam


#5

Hi TaraKizer,
thanks

Can i update every row with different recon_id in set based statement.


#6

Hi

I am not sure I fully understand your question.

In yuor solution you use cursor to iterate through a list of records and perform updates/inserts during these iterations.

what I suggest that instead of using cursor you create a temporary table which is populated with some ids and probably other relevant information using a query you used to iterate in cursor. Then you can use this temporary table to perform required update/inserts.