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 2008 Forums
 Transact-SQL (2008)
 Store Procedure Performance Issue

Author  Topic 

govindraaj.s
Starting Member

1 Post

Posted - 2013-02-20 : 04:59:28
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

1974 Posts

Posted - 2013-03-09 : 18:32:41
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

52326 Posts

Posted - 2013-03-10 : 04:14:00
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
   

- Advertisement -