Using 2 cursors in SQL 2005 - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Using 2 cursors in SQL 2005

Author

Topic

rytta87
Starting Member

1 Post

Posted - 2010-06-03 : 08:47:40

Hello,
I have the following case:
I'm currently working on a CRM database and my aim is to generate a report showing how much time does a customer need to reach a certain amount of purchases.
I am using table variables and cursors to generate the result. I am getting correct results but the execution time of the query is very slow (approx. 4 mins).

Below is the code I'm using:

USE CRM

DECLARE @CARD_NB AS NUMERIC --CARD NUMBER
DECLARE @PURCH_DTE AS DATETIME --PURCHASE DATE OF CURRENT ROW
DECLARE @EARNED_PTS AS NUMERIC --PTS EARNED FOR CURRENT TRANSACTION

DECLARE @FIRST_PDTE AS DATETIME --FIRST PURCHASE DATE
DECLARE @LAST_PDTE AS DATETIME --LAST PURCHASE DATE
DECLARE @TOTAL_PTS AS NUMERIC --ACCUMULATION OF TRX PTS UNTIL >=2000 PTS

--Table variable to store card numbers on which I want to work

DECLARE @PTBAL TABLE
(CardNo NVARCHAR(50),
OperationDate DATETIME,
Points DECIMAL(18,6))

INSERT INTO @PTBAL
SELECT pb.CardNo, pb.OperationDate, pb.Points
FROM PointBalance pb,
(
SELECT pb.CardNo
FROM PointBalance pb
GROUP BY pb.CardNo
HAVING SUM(pb.Points)>=2000
) T1
WHERE T1.CardNo=pb.CardNo
AND pb.CardNo >10000000 AND pb.CardNo<70000000
AND pb.OperationType='Purchase'
AND pb.Points<>0
ORDER BY pb.CardNo

--Table variable to hold results
DECLARE @TMP1 TABLE
(CARD_NUMBER NVARCHAR(50),
FIRST_PDATE DATETIME,
LAST_PDATE DATETIME,
TOT_PTS DECIMAL(18,6))

-- Get all cards having balance >=2000 pts
DECLARE CSRCARD CURSOR FOR
SELECT pb.CardNo
FROM @PTBAL pb
GROUP BY pb.CardNo

OPEN CSRCARD

FETCH NEXT FROM CSRCARD INTO @CARD_NB
WHILE @@FETCH_STATUS = 0
BEGIN

-- Get transactions for card from 1st cursor
DECLARE CSRTRX CURSOR FOR
SELECT pb.OperationDate, pb.Points
FROM @PTBAL pb
WHERE pb.CardNo=@CARD_NB
ORDER BY pb.OperationDate

OPEN CSRTRX

FETCH NEXT FROM CSRTRX INTO @PURCH_DTE, @EARNED_PTS
SET @TOTAL_PTS=0

WHILE @@FETCH_STATUS = 0
BEGIN

SET @FIRST_PDTE=NULL
SET @LAST_PDTE=NULL

-- 1st purchase date
SET @FIRST_PDTE= (SELECT MIN(pb.OperationDate) FROM @PTBAL pb WHERE pb.CardNo=@CARD_NB)

-- Total pts accumulated so far by cardholder
SET @TOTAL_PTS=@TOTAL_PTS + @EARNED_PTS

-- Set last purchase date
IF @TOTAL_PTS>=2000
BEGIN
SET @LAST_PDTE=@PURCH_DTE

-- Insert results into temp table TMP1
INSERT INTO @TMP1
SELECT @CARD_NB AS CARD_NUMBER, @FIRST_PDTE AS FIRST_PDATE,
@LAST_PDTE AS LAST_PDATE, @TOTAL_PTS AS TOT_PTS

BREAK

END

FETCH NEXT FROM CSRTRX INTO @PURCH_DTE, @EARNED_PTS
END

CLOSE CSRTRX
DEALLOCATE CSRTRX

-- End of 2nd cursor

FETCH NEXT FROM CSRCARD INTO @CARD_NB
END

CLOSE CSRCARD
DEALLOCATE CSRCARD

-- Show final results
SELECT t.CARD_NUMBER, c.[First] AS FIRST_NAME, c.[Last] AS LAST_NAME,
CASE WHEN c.MobileCode=3 THEN '0'+ CAST(c.MobileCode AS NVARCHAR(10))+c.MobilePhone
ELSE CAST(c.MobileCode AS NVARCHAR(10))+c.MobilePhone END AS MOBILE,
c.Points AS CURRENT_BALANCE, t.TOT_PTS AS ELIGIBLE_BALANCE,
t.FIRST_PDATE, t.LAST_PDATE,
DATEDIFF(DAY,t.FIRST_PDATE,t.LAST_PDATE) AS TIME_NEEDED
FROM @TMP1 t
INNER JOIN Customers c
ON c.CardNo=t.CARD_NUMBER
ORDER BY CURRENT_BALANCE DESC

For each card number, I have to sort the transactions by date, then add each transaction amount to @TOTAL_PTS variable, when this variable reaches 2000, I break the loop and save the results into the table variable @TMP1. Since i have about 1500 cards, and each card has many transactions, it is taking a lot of time to process the results and insert into the variable table.
Is there a way to make this query go faster?

Thanks for your replies

Sachin.Nand

2937 Posts

Posted - 2010-06-03 : 15:03:59

You have to show some sample data and desired output.
Its really not possible for anyone to guess what you exactly want.Also no one going to "optimize" the cursor.Maybe someone will come up with a much better and faster solution.

To know more on how you can show sample data click the link below.

http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources