Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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  
 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.09 seconds. Powered By: Snitz Forums 2000