How to fine tune cursor by rewrote the code
-
I have a stored procedure, inside that SP I am using below cursor with Temp tables. Currently it is taking 33 minutes to process 15000 records.
I am trying to fine tune this cursor, is it possible to rewrite the code by avoid the cursor?
There are two temp tables
1.“#TRN_work” This table contain the transaction details (one account may have multiple transactions)
2. “#test_account” This table contain the Opening balance details (only one account entry ,no duplicates account entries )
Here the logic is, The Payments in “#TRN_work” transactions table has to be added to the payments in the “#test_account” table row by row manner .Because if one account has multiple transactions, All those amounts to be added to Opening balance table and that will give the total bill amount for a customer in a month
Sample Data:
#TRN_work
AdjID|AcctNum| AdjTimeStamp|CodeEnum|AgeBucket|Payment|Adjustment|AdjSurcharge|Aj_Tax|LateFeeAj_Total
695980143913442016-08-01 06:37:15.26780-4.840.00-0.130.000.00-4.84
695980243913442016-08-01 06:37:28.27080-4.840.00-0.130.000.00-4.84
695980643913442016-08-01 06:41:19.54780-4.410.000.000.000.00-4.41
695980743913442016-08-01 06:42:03.05080-3.770.000.000.000.00-3.77
695980843913442016-08-01 06:42:20.02780-5.540.00-0.130.000.00-5.54
695925043410132016-08-01 00:18:10.35780-4.590.00-0.14-0.370.00-4.59
695925143410132016-08-01 00:18:52.90780-5.900.00-0.14-0.480.00-5.90
695925243410132016-08-01 00:18:53.81380-4.590.00-0.14-0.370.00-4.59
695976743410132016-08-01 02:22:38.02080-5.900.00-0.14-0.480.00-5.90
695976843410132016-08-01 02:22:48.43380-4.320.00-0.14-0.350.00-4.32
696007043410132016-08-01 09:12:12.74380-5.900.00-0.14-0.480.00-5.90
695978743643992016-08-01 06:08:58.86080-30.420.00-0.900.000.00-30.42
695979843643992016-08-01 06:36:38.95780-3.000.000.000.000.00-3.00
695980543643992016-08-01 06:40:38.97380-3.000.000.000.000.00-3.00
696803443643992016-08-02 04:44:39.06380-30.420.00-0.900.000.00-30.42
699232143643992016-08-25 10:52:40.77380-3.000.000.000.000.00-3.00
#test_account
AcctNumPaymentsAdjustmentsAdjSurchargesAj_TaxTrxChargesTrxSurchargesTrxThirdPartyChargesTrxTaxLateFeePreviousBalanceCurrentBalanceBalance1Balance2Balance3Balance4Balance5TransCountLastAdjDateLastTrxDate
43913440.000.000.000.00523.4013.520.000.000.000.00536.920.000.000.000010900:00.035:57.2
43410130.000.000.000.00379.7311.340.0034.470.000.00425.540.000.000.00008100:00.042:06.8
43643990.000.000.000.00648.7918.3018.750.000.00263.73685.84123.48140.250.00004900:00.054:18.7
below is Cursor which is using to implement the logic :
DECLARE testcrAdj CURSOR READ_ONLY FOR
SELECT AcctNum
, AdjTimeStamp
, AgeBucket
, Payment
, Adjustment
, AdjSurcharge
, Aj_Tax
, LateFee
, Aj_Total
FROM#TRN_work
ORDER BY AdjID
OPEN testcrAdj
FETCH NEXT FROM testcrAdj
INTO @iAcctNum
, @dtAdjTimeStamp
, @AgingBucket
, @mPayment
, @mAdjustment
, @mAdjSurcharge
, @mAdjTax
, @mLateFee
, @AjTotal
WHILE ( @@FETCH_STATUS <> -1 )
BEGIN
IF ( @@FETCH_STATUS <> -2 )
BEGIN
UPDATE #test_account
SET Payments= Payments + @mPayment
, Adjustments= Adjustments + @mAdjustment
, AdjSurcharges= AdjSurcharges + @mAdjSurcharge
, Aj_Tax= Aj_Tax + @mAdjTax
, LateFee= LateFee + @mLateFee
, CurrentBalance = CurrentBalance + ( CASE @AgingBucket WHEN 0 THEN
@AjTotal ELSE 0 END )
, Balance1 = Balance1 + ( CASE @AgingBucket WHEN 1 THEN @AjTotal
ELSE 0 END )
, Balance2 = Balance2 + ( CASE @AgingBucket WHEN 2 THEN @AjTotal
ELSE 0 END )
, Balance3 = Balance3 + ( CASE @AgingBucket WHEN 3 THEN @AjTotal
ELSE 0 END )
, Balance4 = Balance4 + ( CASE @AgingBucket WHEN 4 THEN @AjTotal
ELSE 0 END )
, Balance5 = Balance5 + ( CASE WHEN @AgingBucket >= 5 THEN
@AjTotal ELSE 0 END )
, LastAdjDate = ( CASE WHEN @dtAdjTimeStamp > LastAdjDate THEN
@dtAdjTimeStamp ELSE LastAdjDate END )
WHEREAcctNum = @iAcctNum
UPDATE#test_account
SET Balance4= Balance4 + Balance5
, Balance5= 0
WHEREAcctNum = @iAcctNum
AND Balance5 < 0
UPDATE#test_account
SET Balance3= Balance3 + Balance4
, Balance4= 0
WHEREAcctNum = @iAcctNum
AND Balance4 < 0
UPDATE#test_account
SET Balance2= Balance2 + Balance3
, Balance3= 0
WHEREAcctNum = @iAcctNum
AND Balance3 < 0
UPDATE#test_account
SET Balance1= Balance1 + Balance2
, Balance2= 0
WHEREAcctNum = @iAcctNum
AND Balance2 < 0
UPDATE#test_account
SETCurrentBalance = CurrentBalance + Balance1
, Balance1= 0
WHEREAcctNum = @iAcctNum
AND Balance1 < 0
END
FETCH NEXT FROM testcrAdj
INTO @iAcctNum
, @dtAdjTimeStamp
, @AgingBucket
, @mPayment
, @mAdjustment
, @mAdjSurcharge
, @mAdjTax
, @mLateFee
, @AjTotal
END
CLOSE testcrAdj
DEALLOCATE testcrAdj
-
Welcome to SQL Server central.
Just having a look at your post, it's really difficult to read your data, especially with a lack of commas. Have a read of the forum etiquette post here[/url] and have a fiddle with your post and people will find it a lot easier to read and answer your post and code.
Make sure to make use of
IFcode shortcuts, it makes your code so much easier to read as well.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
is this
#test_accountAcctNum|Payment|Adjustments|AdjSurcharges|Aj_Tax|TrxCharges|TrxSurcharges|TrxThirdPartyCharges|TrxTax|LateFee|PreviousBalance
43913440.000.000.000.00523.4013.520.000.000.000.00
43410130.000.000.000.00379.7311.340.0034.470.000.00
43643990.000.000.000.00648.7918.3018.750.000.00263.73
CurrentBalance|Balance1|Balance2|Balance3|Balance4|Balance5|TransCount|LastAdjDate|LastTrxDate
536.920.000.000.000010900:00.035:57.2
425.540.000.000.00008100:00.042:06.8
685.84123.48140.250.00004900:00.054:18.7
one table or two tables?
suggest you read this article https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ and repost with some usable scripts
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
that is one table . As we have space constraint while post the question
i kept in two parts
-
kiran08.bi (9/1/2016)
that is one table . As we have space constraint while post the questioni kept in two parts
Thanks Kiran,
I would still suggest providing those create and insert statements though.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
can you please provide create table script and insert data script and your expected results based on your sample data.....as per earlier requests.
you will find you will get better and quicker responses if reposnders can easily replicate your tables and data.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day -
One thing I noticed is that you spread out Aj_total into different balances based on Age, and then the next thing you do is a series of queries to collapse those balances into a single field. This yields the same result as if you had just used Aj_Total in the first place.
Based on your data, you're going to need to aggregate your #TRN_Work data before joining it to the #test_accounts. I've got a sample query, but since you didn't provide expected output, I'm not sure what you are looking for with the dates.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
This looks like the main update can be achieved with a simple CTE to pre-aggregate per AcctNum and AgeBucket, joined to the #test_account for a single update.
;WITH cteAggregate AS (
SELECT
AcctNum
, mAgingBucket = AgeBucket
, mAdjTimeStamp = MAX(AdjTimeStamp)
, mPayment = SUM(Payment)
, mAdjustment = SUM(Adjustment)
, mAdjSurcharge = SUM(AdjSurcharge)
, mAj_Tax = SUM(Aj_Tax)
, mLateFee = SUM(LateFee)
, mAj_Total = SUM(Aj_Total)
FROM #TRN_work
GROUP BY AcctNum, AgeBucket
)
UPDATE #test_account
SET Payments = Payments + mPayment
, Adjustments = Adjustments + mAdjustment
, AdjSurcharges = AdjSurcharges + mAdjSurcharge
, Aj_Tax = Aj_Tax + mAj_Tax
, LateFee = LateFee + mLateFee
, CurrentBalance = CurrentBalance + CASE WHEN mAgingBucket = 0 THEN mAj_Total ELSE 0. END
, Balance1 = Balance1 + CASE WHEN mAgingBucket = 1 THEN mAj_Total ELSE 0. END
, Balance2 = Balance2 + CASE WHEN mAgingBucket = 2 THEN mAj_Total ELSE 0. END
, Balance3 = Balance3 + CASE WHEN mAgingBucket = 3 THEN mAj_Total ELSE 0. END
, Balance4 = Balance4 + CASE WHEN mAgingBucket = 4 THEN mAj_Total ELSE 0. END
, Balance5 = Balance5 + CASE WHEN mAgingBucket >= 5 THEN mAj_Total ELSE 0. END
, LastAdjDate = CASE WHEN mAdjTimeStamp > LastAdjDate THEN mAdjTimeStamp ELSE LastAdjDate END
FROM #test_account AS ta
INNER JOIN cteAggregate AS cte
ON ta.AcctNum = cte.AcctNum
drew.allen (9/1/2016)
One thing I noticed is that you spread out Aj_total into different balances based on Age, and then the next thing you do is a series of queries to collapse those balances into a single field. This yields the same result as if you had just used Aj_Total in the first place.Based on your data, you're going to need to aggregate your #TRN_Work data before joining it to the #test_accounts. I've got a sample query, but since you didn't provide expected output, I'm not sure what you are looking for with the dates.
Drew
Each collapsing update has a where clause, so it looks like it's trying to offset the oldest debts first.
-
Hi Thanks of the reply
i am trying to implement the CTE which is posted above . Thank you so much . And keep you update on this .
-
kiran08.bi (9/2/2016)
Hi Thanks of the replyi am trying to implement the CTE which is posted above .
You should have noticed that my partial solution only works if all of the AgeBucket values are the same.
Below is a complete solution that can replace your cursor.
;WITH cteAggregate AS (
SELECT
AcctNum
, mAdjTimeStamp = MAX(AdjTimeStamp)
, mPayment = SUM(Payment)
, mAdjustment = SUM(Adjustment)
, mAdjSurcharge = SUM(AdjSurcharge)
, mAj_Tax = SUM(Aj_Tax)
, mLateFee = SUM(LateFee)
, mAj_Total = SUM(Aj_Total)
, mCurrentBalance = SUM(CASE WHEN AgeBucket = 0 THEN Aj_Total ELSE 0. END)
, mBalance1 = SUM(CASE WHEN AgeBucket = 1 THEN Aj_Total ELSE 0. END)
, mBalance2 = SUM(CASE WHEN AgeBucket = 2 THEN Aj_Total ELSE 0. END)
, mBalance3 = SUM(CASE WHEN AgeBucket = 3 THEN Aj_Total ELSE 0. END)
, mBalance4 = SUM(CASE WHEN AgeBucket = 4 THEN Aj_Total ELSE 0. END)
, mBalance5 = SUM(CASE WHEN AgeBucket >= 5 THEN Aj_Total ELSE 0. END)
FROM #TRN_work
GROUP BY AcctNum
)
UPDATE #test_account
SET Payments = Payments + mPayment
, Adjustments = Adjustments + mAdjustment
, AdjSurcharges = AdjSurcharges + mAdjSurcharge
, Aj_Tax = Aj_Tax + mAj_Tax
, LateFee = LateFee + mLateFee
, CurrentBalance = CurrentBalance + mCurrentBalance
, Balance1 = Balance1 + mBalance1
, Balance2 = Balance2 + mBalance2
, Balance3 = Balance3 + mBalance3
, Balance4 = Balance4 + mBalance4
, Balance5 = Balance5 + mBalance5
, LastAdjDate = CASE WHEN mAdjTimeStamp > LastAdjDate THEN mAdjTimeStamp ELSE LastAdjDate END
FROM #test_account AS ta
INNER JOIN cteAggregate AS cte
ON ta.AcctNum = cte.AcctNum;
UPDATE #test_account
SET Balance4 = Balance4 + Balance5
, Balance5 = 0
WHERE Balance5 < 0;
UPDATE #test_account
SET Balance3 = Balance3 + Balance4
, Balance4 = 0
WHERE Balance4 < 0;
UPDATE #test_account
SET Balance2 = Balance2 + Balance3
, Balance3 = 0
WHERE Balance3 < 0;
UPDATE #test_account
SET Balance1 = Balance1 + Balance2
, Balance2 = 0
WHERE Balance2 < 0;
UPDATE #test_account
SET CurrentBalance = CurrentBalance + Balance1
, Balance1 = 0
WHERE Balance1 < 0;
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply