How to convert a slow processing cursor? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to convert a slow processing cursor?

Author

Topic

pras2007
Posting Yak Master

216 Posts

Posted - 2009-10-11 : 09:36:40

Hello All,

Does anybody know how to convert this cursor that has two scenarios into a faster operations that does not use a cursor? The cursor code is shown below:

declare @processdate datetime -- current as of date
declare @RMS money -- current RMS
declare @inslayer varchar(25)-- current ins_layer
declare @claimnumber varchar(10)-- current claim Number
declare @ipc money -- current IPC
declare @xpc money -- current EPC
declare @tpc money -- current TPC

declare @prevprocessdate datetime -- previous processdate
declare @prevRMS money -- previous RMS
declare @previnslayer varchar(25)-- previous ins_layer
declare @previpc money -- current IPC
declare @prevxpc money -- current EPC
declare @prevtpc money -- current TPC

declare @blntmp BIT

--Cursor to hold all records
DECLARE ClaimCursor CURSOR LOCAL FORWARD_ONLY FAST_FORWARD FOR
SELECT
A.processdate,
C.RMS_claim_id,
B.i_level,
A.IPC,
A.EPC,
A.TPC
FROM
FFSR_table AS A
INNER join DPR_Table AS B ON A.DM_Key = B.DP_Key
INNER JOIN DCR_Table AS C ON C.DM_Key = A.DM_Key
ORDER BY C.RMS_claim_id, B.i_level,a.Processdate

--Open Cursor
OPEN ClaimCursor
--Get First Record
FETCH NEXT FROM ClaimCursor INTO @processdate,@RMS,@inslayer,@ipc,@xpc,@tpc

SET @blntmp = 1

WHILE @@FETCH_STATUS = 0
BEGIN

IF @blntmp=1 -- The First Record
BEGIN
-- Set the first Record
SET @prevprocessdate = @processdate
SET @prevRMS=@RMS
SET @previnslayer = @inslayer
SET @blntmp=0
SET @previpc =@ipc
SET @prevxpc =@xpc
SET @prevtpc =@tpc
END
ELSE

BEGIN
IF @prevRMS=@RMS
BEGIN
IF @previnslayer = @inslayer
AND @previpc=@ipc
AND @prevxpc=@xpc
AND @prevtpc=@tpc
BEGIN
-- Execute SCENARIO 1
print 'Scenario 1'
UPDATE FFSR_table
SET
IPC_Chg= @ipc,
EPC_Chg= @xpc,
TPC_Chg= @tpc
FROM
FFSR_table AS A
INNER join DPR_Table AS B ON A.DM_Key = B.DP_Key
INNER JOIN DCR_Table AS C ON C.DM_Key = A.DM_Key
WHERE
C.RMS_claim_id = @prevRMS
AND B.i_level = @previnslayer
AND A.processdate = @prevprocessdate
END
ELSE
BEGIN
UPDATE FFSR_table
SET
IPC_Chg= 0,
EPC_Chg= 0,
TPC_Chg= 0
FROM
FFSR_table AS A
INNER join DPR_Table AS B ON A.DM_Key = B.DP_Key
INNER JOIN DCR_Table AS C ON C.DM_Key = A.DM_Key
WHERE
C.RMS_claim_id = @prevRMS
AND B.i_level = @previnslayer
AND A.processdate = @prevprocessdate
END

-- Previous inslayer <> current inslayer
IF @previnslayer <> @inslayer AND @prevRMS = @RMS
BEGIN
print 'Scenario 2'
UPDATE FFSR_table
SET
IPC= @previpc- @ipc,
EPC= @prevxpc-@xpc,
TPC= @prevtpc-@tpc
FROM
FFSR_table AS A
INNER join DPR_Table AS B ON A.DM_Key = B.DP_Key
INNER JOIN DCR_Table AS C ON C.DM_Key = A.DM_Key
WHERE
C.RMS_claim_id = @RMS
AND B.i_level = @inslayer
AND A.processdate = @processdate
END

--SET Prev = Current Record
SET @prevprocessdate = @processdate
SET @previnslayer = @inslayer
SET @prevRMS = @RMS
SET @previpc =@ipc
SET @prevxpc =@xpc
SET @prevtpc =@tpc
END
ELSE -- Next RMS
BEGIN
--Set Previous = Current Record
SET @prevprocessdate = @processdate
SET @previnslayer = @inslayer
SET @prevRMS=@RMS
SET @blntmp=0
SET @previpc =@ipc
SET @prevxpc =@xpc
SET @prevtpc =@tpc
END
END
--Get Next Record
FETCH NEXT FROM ClaimCursor INTO @processdate,@RMS,@inslayer,@ipc,@xpc,@tpc
END
CLOSE ClaimCursor
DEALLOCATE ClaimCursor

Please advice.

Thanks.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-10-11 : 14:47:26

can you explain your requirement with some sample data rather than posting code? that will be much helpful to somebody trying to help you out. see link below for format of posting data

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources