SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Store Procedure Performance Issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

govindraaj.s
Starting Member

India
1 Posts

Posted - 02/20/2013 :  04:59:28  Show Profile  Reply with Quote
Hi,
I have a task where i need to loop through over 6lakhs records, I tried many ways but it takes hell a lot of time to execute (say 12hrs).

Can some one fine tune / optimize the query and make sure it runs much faster.

Below is my store procedure.


ALTER PROCEDURE dbo.USP_SAMPLE
AS
BEGIN

SET NOCOUNT ON;

DECLARE @Cust_No VARCHAR(20)
DECLARE @PROCESS_SEQ_NO INT
DECLARE @DIFFERENCE INT
DECLARE @TotalEC INT
DECLARE @ECPoint INT

DECLARE curEarnedCash CURSOR
For

SELECT A.CUSTOMER_NO,
B.PROCESS_SEQ_NO ,
A.[Difference],
A.[EC_Points] AS Total_EC_POINTS,
((ISNULL(NORMAL_CASH, 0) + ISNULL(SPECIAL_CASH, 0) + ISNULL(ADJUST_CASH, 0)) - (ISNULL(REDEEM_CASH, 0) + ISNULL(TRANSFER_CASH, 0) + ISNULL(PURGE_CASH, 0)))AS EC_Points
FROM ECASH_TEST B
INNER JOIN ( SELECT RS.CUSTOMER_NO,
SUM(ISNULL(CR_AMT, 0) - ISNULL(CR_BLOCKAMT, 0)) + SUM(ISNULL(SPL_CR_AMT, 0) - ISNULL(SPL_CR_BLOCKAMT, 0)) AS RS_POINTS,
Temp_EC.EC_Points,
Temp_EC.EC_Points - (SUM(ISNULL(CR_AMT, 0) - ISNULL(CR_BLOCKAMT, 0)) + SUM(ISNULL(SPL_CR_AMT, 0) - ISNULL(SPL_CR_BLOCKAMT, 0))) AS [Difference]
FROM REWARDS_SUMMARY RS
INNER JOIN ( SELECT CUSTOMER_NO,
SUM(ISNULL(NORMAL_CASH, 0) + ISNULL(SPECIAL_CASH, 0) + ISNULL(ADJUST_CASH, 0)) - SUM(ISNULL(REDEEM_CASH, 0) + ISNULL(TRANSFER_CASH, 0) + ISNULL(PURGE_CASH, 0))AS EC_Points
FROM ECASH_TEST
GROUP BY CUSTOMER_NO
HAVING ((SUM(ISNULL(NORMAL_CASH, 0)) + SUM(ISNULL(SPECIAL_CASH, 0)) + SUM(ISNULL(ADJUST_CASH, 0))) - (SUM(ISNULL(REDEEM_CASH, 0)) + SUM(ISNULL(TRANSFER_CASH, 0)) + SUM(ISNULL(PURGE_CASH, 0)))) <> 0
)Temp_EC
ON Temp_EC.CUSTOMER_NO = RS.CUSTOMER_NO
WHERE (((ISNULL(CR_AMT, 0) - ISNULL(CR_BLOCKAMT, 0)) + (ISNULL(SPL_CR_AMT, 0) - ISNULL(SPL_CR_BLOCKAMT, 0))) >= 0)
GROUP BY RS.CUSTOMER_NO,Temp_EC.EC_Points
HAVING Temp_EC.EC_Points - (SUM(ISNULL(CR_AMT, 0) - ISNULL(CR_BLOCKAMT, 0)) + SUM(ISNULL(SPL_CR_AMT, 0) - ISNULL(SPL_CR_BLOCKAMT, 0))) <> 0
) A
ON A.CUSTOMER_NO = B.CUSTOMER_NO
ORDER BY B.CUSTOMER_NO

OPEN curEarnedCash
FETCH NEXT FROM curEarnedCash Into @Cust_No, @PROCESS_SEQ_NO,@DIFFERENCE,@TotalEC,@ECPoint
WHILE @@Fetch_Status = 0 BEGIN
IF @DIFFERENCE > @TotalEC
BEGIN
UPDATE ECASH_TEST
SET REDEEM_CASH = ((ISNULL(NORMAL_CASH, 0) + ISNULL(SPECIAL_CASH, 0) + ISNULL(ADJUST_CASH, 0)) - (ISNULL(TRANSFER_CASH, 0) + ISNULL(PURGE_CASH, 0))),
TRANSFER_CASH = 0,
UPDATED_USER = 'SDM611812',
UPDATED_DT = GETDATE()
WHERE PROCESS_SEQ_NO = @PROCESS_SEQ_NO
AND CUSTOMER_NO = @Cust_No

SET @DIFFERENCE = @DIFFERENCE - @ECPoint;
END
ELSE
BEGIN
UPDATE ECASH_TEST
SET REDEEM_CASH = ISNULL(REDEEM_CASH, 0) + @DIFFERENCE,
TRANSFER_CASH = 0,
UPDATED_USER = 'SDM611812',
UPDATED_DT = GETDATE()
WHERE PROCESS_SEQ_NO = @PROCESS_SEQ_NO
AND CUSTOMER_NO = @Cust_No
SET @DIFFERENCE = 0 ;
END

FETCH NEXT FROM curEarnedCash Into @Cust_No, @PROCESS_SEQ_NO,@DIFFERENCE,@TotalEC,@ECPoint

End -- End of Fetch

Close curEarnedCash
Deallocate curEarnedCash
DROP TABLE #PointsTable
END


Note:
I have also tried loop instead of cursor yet no use.

chadmat
The Chadinator

USA
1974 Posts

Posted - 03/09/2013 :  18:32:41  Show Profile  Visit chadmat's Homepage  Reply with Quote
Why do you need to loop/cursor? You should be able to do this as a single update statement with a case on the value that REDEEM_CASH is being set to. Not having a sample dataset, I can't test that, but I am pretty sure it is doable.

-Chad
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 03/10/2013 :  04:14:00  Show Profile  Reply with Quote
explain with some sample data what you're trying to achieve and we will try to suggest a no cursor solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000