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. |
|