Cursor problem - SQL Server Forums

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 2000 Forums

SQL Server Development (2000)

Cursor problem

Author

Topic

jung1975
Aged Yak Warrior

503 Posts

Posted - 2003-05-21 : 22:33:29

I would like to see the different approaches to solve this problem that sleezy have posted on Database Journal.

/*I HAVE WRITTEN A prcRepaymentFacility PROCEDURE TO ALLOCATE INTEREST PAID when payment is recieved. THE PROBLEM
IS I'M USING THREE CURSORS TO PROCESS MY DATA AS THIS REQUIRES ROW BY ROW MANIPULATION.
WHAT OTHER TECHNIQUE CAN I USE TO PROCESS THIS DATA AND IMPROVE PERFORMANCE OF MY PROCEDURE

THIS QUERY RUNS FOR A VERY LONG TIME. SO IN 1:40HRS I WOULD HAVE 4402 RECORDS.
I HAVE 6 MILLION RECORDS TO GO THROUGH. HOW CAN I OPTIMIZE THIS QUERY.I'VE TRIED ALL I CAN
SHOULD IT BE RE-WRITTEN AS TWO SEPARATE FUNCTIONS ??? I'M STUCK
PLEASE ASSIST I NEED TO HAVE IT READY BY TUES BUT I CAN'T OPTIMISE IT..

--SCROLL THROUGH RECORDS WITHOUT A CURSOR .....
-- DECLARE @OBJECT_KEY VARCHAR(50)
-- SET @OBJECT_KEY = ''
--
-- WHILE @OBJECT_KEY IS NOT NULL
-- BEGIN
-- SELECT TOP 20 @OBJECT_KEY = MIN(OBJECT_KEY)
-- FROM REPAYMENT_TABLE
-- WHERE OBJECT_KEY > @OBJECT_KEY
--
-- IF @OBJECT_KEY IS NOT NULL
-- SELECT @OBJECT_KEY + ............ETC
--
-- END
*/

IF OBJECT_ID('prcRepaymentFacility') IS NOT NULL
DROP PROC prcRepaymentFacility
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

CREATE PROCEDURE prcRepaymentFacility
AS

BEGIN TRAN
UPDATE a
SET a.paid_interest = 0
,a.paid_principal = 0
FROM REPAYMENT_TABLE a

DECLARE @X int

DECLARE @i int
DECLARE @j int
DECLARE @k int
DECLARE @m int
DECLARE @n int

Declare @Acc_No varchar (50)

DECLARE @Repay_No int
DECLARE @Due_Date datetime
DECLARE @Due_Principal money
DECLARE @Due_Interest money
Declare @Due_Penalty_on_Interest money
DECLARE @Paid_Principal money
DECLARE @Paid_Interest money
Declare @Paid_Penalty_on_Interest money

Declare @Interest_Payer money
Declare @Principal_Payer money

DECLARE @Repay_Amt money
DECLARE @RPay_Date varchar (30)

SET @X = 0

DECLARE RS_ALL CURSOR FOR --LOCAL FAST_FORWARD FOR

SELECT DISTINCT
Object_Key
FROM REPAYMENT_TABLE

OPEN RS_All

FETCH NEXT FROM RS_All
INTO @Acc_No

WHILE @@FETCH_STATUS = 0
BEGIN

SET @X = @X + 1
SELECT @X

DECLARE @Tmp TABLE
(
AccountNo VARCHAR(15)
,TotalAmount MONEY
,PaidDate DATETIME
)

INSERT INTO @Tmp
SELECT
AccountNo
,TotalAmount
,CONVERT(DATETIME,PaidDate)
FROM ZA15100P_Totals
WHERE AccountNo = @Acc_No

--<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<---------
SET @I = 0

DECLARE RS CURSOR FOR --LOCAL FAST_FORWARD FOR
SELECT
Repayment_Number
,Due_Date
,Due_Principal
,Due_Interest
,Paid_Principal
,Paid_Interest
,Due_Penalty_On_Interest
,Paid_Penalty_On_Interest
FROM REPAYMENT_TABLE
WHERE Object_Key = @Acc_no
ORDER BY Repayment_Number ASC

OPEN RS
FETCH NEXT FROM RS
INTO @Repay_No
,@Due_Date
,@Due_Principal
,@Due_Interest
,@Paid_Principal
,@Paid_Interest
,@Due_Penalty_on_Interest
,@Paid_Penalty_on_Interest
WHILE @@FETCH_STATUS = 0
BEGIN

SET @I = @I + 1

IF
(SELECT COUNT(*)
FROM @Tmp
WHERE CONVERT(VARCHAR(50),DATEPART(YEAR ,CONVERT(DATETIME,Paiddate))) +
CONVERT(VARCHAR(50),DATEPART(MONTH ,CONVERT(DATETIME,Paiddate))) =

CONVERT(VARCHAR(50),DATEPART(YEAR ,CONVERT(DATETIME,@due_date))) +
CONVERT(VARCHAR(50),DATEPART(MONTH ,CONVERT(DATETIME,@due_date)))) > 0

BEGIN
DECLARE RS_1 CURSOR FOR
SELECT
TotalAmount * -1
,Paiddate
FROM @Tmp
WHERE CONVERT(VARCHAR(50),DATEPART(YEAR ,CONVERT(DATETIME,Paiddate))) +
CONVERT(VARCHAR(50),DATEPART(MONTH ,CONVERT(DATETIME,Paiddate))) =

CONVERT(VARCHAR(50),DATEPART(YEAR ,CONVERT(DATETIME,@due_date))) +
CONVERT(VARCHAR(50),DATEPART(MONTH ,CONVERT(DATETIME,@due_date)))

OPEN RS_1
FETCH NEXT FROM RS_1

INTO @Repay_Amt
,@RPay_Date

CLOSE RS_1
DEALLOCATE RS_1
END --End For Count check of RS_1

--Goto Skipper
--------------------- -----------------------------------------
-------------Populate Paid Interest & Principal------------------
-----------------------------------------------------------------
Start_Int:

SET @j = @i + 1
SET @k = 0

WHILE @J > 1 AND @Repay_Amt > 0
BEGIN

SET @k = @k + 1
SET @j = @j - 1

-------------------- -----------------------------------------
--------------Populate Paid Interest-----------------------------
-----------------------------------------------------------------

DECLARE RS_2 CURSOR FOR

SELECT
Due_Interest
,Paid_Interest
FROM REPAYMENT_TABLE
WHERE Repayment_Number = @j AND Object_Key = @Acc_no

OPEN RS_2

FETCH NEXT FROM RS_2
INTO @Due_Interest
,@Paid_Interest

IF @Due_Interest - @Paid_Interest > 0
BEGIN

IF @Due_Interest - @Paid_Interest - @Repay_Amt < 0
BEGIN

SET @Interest_Payer = @Due_Interest - @Paid_Interest
END
ELSE
BEGIN

SET @Interest_Payer = @Repay_Amt
END
END
ELSE
BEGIN

SET @Interest_Payer = 0
END

--SELECT @Interest_Payer
UPDATE REPAYMENT_TABLE
SET Paid_interest = @Paid_Interest + @Interest_Payer
WHERE CURRENT OF RS_2

CLOSE RS_2
DEALLOCATE RS_2

SET @Repay_Amt = @Repay_Amt - @Interest_Payer
SET @Interest_Payer = 0

-- goto Start_Int
------------------------------------------------------
------------------------------------------------------
--Populate the Paid Principal Amounts-----------------
------------------------------------------------------

SET @m = @i + 1
SET @n = 0

WHILE @m > 1 and @Repay_Amt > 0 --and @n < 2
BEGIN

SET @n = @n + 1
SET @m = @m - 1

DECLARE RS_2 CURSOR FOR

SELECT
Due_Principal
,Paid_Principal
FROM REPAYMENT_TABLE
WHERE repayment_number = @m AND object_Key = @Acc_no

OPEN RS_2
FETCH NEXT FROM RS_2
INTO
@Due_Principal
,@Paid_Principal

IF @Due_Principal - @Paid_Principal > 0

BEGIN
IF @Due_Principal - @Paid_Principal - @Repay_Amt < 0

BEGIN
SET @Principal_Payer = @Due_Principal - @Paid_Principal
END
ELSE

BEGIN
SET @Principal_Payer = CASE
WHEN @Repay_Amt > 0 THEN @Repay_Amt
ELSE 0
END
END
END
ELSE
BEGIN

SET @Principal_Payer = 0
END

--Select @Interest_Payer
UPDATE REPAYMENT_TABLE
SET Paid_Principal = @Paid_Principal + @Principal_Payer
WHERE CURRENT OF RS_2

CLOSE RS_2
DEALLOCATE RS_2

SET @Repay_Amt = @Repay_Amt - @Principal_Payer
SET @Principal_Payer = 0

END
IF @Repay_Amt > 0
BEGIN

DECLARE RS_3 CURSOR FOR

SELECT
Paid_Principal
FROM REPAYMENT_TABLE
WHERE Repayment_Number = @i AND Object_Key = @Acc_no

OPEN RS_3

FETCH NEXT FROM RS_3
INTO @Paid_Principal

UPDATE REPAYMENT_TABLE
SET Paid_Principal = @Paid_Principal + @Repay_Amt
WHERE CURRENT OF RS_3

CLOSE RS_3
DEALLOCATE RS_3
END
END
--------------------------------------------------------------

SET @Repay_Amt = 0
FETCH NEXT FROM RS
INTO
@Repay_No
,@Due_Date
,@Due_Principal
,@Due_Interest
,@Paid_Principal
,@Paid_Interest
,@Due_Penalty_on_Interest
,@Paid_Penalty_on_Interest
Skipper:

END
CLOSE RS
DEALLOCATE RS
--*************************************************

FETCH NEXT FROM RS_All
INTO @Acc_No
END

CLOSE RS_All
DEALLOCATE RS_All
COMMIT TRAN
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2003-05-22 : 20:09:44

Hi jung

Your problem is not optimization, it is the fact that you are using cursors which is so slow. I've started looking at your problem and from what I can see, it looks as if you don't need cursors at all.

At the moment I don't have the time to reverse engineer the problem from your code (sorry). Could you post some DDL (table definitions) with some example data, and your problem specification (ie interest calculation formulae), and I'll try a solution.

Cheers

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources