how to change a cursor to something more efficiant
How can I improve the cursor or change it to something more performance based?
here is the cursor
DECLARE @rn INT;
DECLARE @nextrn INT;
DECLARE cur CURSOR
FOR
SELECT rownum
FROM #tmpHCLC
WHERE break_ind = 'Y'
GROUP BY rownum
ORDER BY rownum;
OPEN cur;
FETCH NEXT FROM cur INTO @rn;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nextrn = ( SELECT TOP 1
rownum
FROM #tmpHCLC
WHERE break_ind = 'Y'
AND rownum > @rn
);
UPDATE #tmpHCLC
SET concurrent_ind = concurrent_ind + CAST(@rn AS VARCHAR)
WHERE concurrent_ind = 'Y'
AND rownum >= @rn
AND rownum < @nextrn;
FETCH NEXT FROM cur INTO @rn;
END;
CLOSE cur;
DEALLOCATE cur;
here is the cursor
DECLARE @rn INT;
DECLARE @nextrn INT;
DECLARE cur CURSOR
FOR
SELECT rownum
FROM #tmpHCLC
WHERE break_ind = 'Y'
GROUP BY rownum
ORDER BY rownum;
OPEN cur;
FETCH NEXT FROM cur INTO @rn;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nextrn = ( SELECT TOP 1
rownum
FROM #tmpHCLC
WHERE break_ind = 'Y'
AND rownum > @rn
);
UPDATE #tmpHCLC
SET concurrent_ind = concurrent_ind + CAST(@rn AS VARCHAR)
WHERE concurrent_ind = 'Y'
AND rownum >= @rn
AND rownum < @nextrn;
FETCH NEXT FROM cur INTO @rn;
END;
CLOSE cur;
DEALLOCATE cur;
Pawan Kumar
Are you using Oracle?
ASKER
This is Microsoft SQL server 2014
Hi,
Can you please provide me some sample data and the expected output?
Can you please provide me some sample data and the expected output?
Hi,
Please try this..
Hope it helps!
Please try this..
DECLARE @rn INT;
DECLARE @nextrn INT;
;WITH CTE AS
(
SELECT rownum
FROM #tmpHCLC
WHERE break_ind = 'Y'
GROUP BY rownum
)
,CTE1 AS
(
SELECT b.rownum curr, b.rownum nex FROM CTE b
CROSS APPLY
(
SELECT TOP 1 a.rownum
FROM #tmpHCLC a
WHERE a.break_ind = 'Y'
AND a.rownum > a.rownum
)
)
UPDATE c
SET c.concurrent_ind = c.concurrent_ind + CAST(@rn AS VARCHAR)
FROM #tmpHCLC c INNER JOIN CTE1 c1 ON c.rownum >= c1.curr AND c.rownum < c1.nex
WHERE c.concurrent_ind = 'Y'
Hope it helps!
Updated.. Please try..
Hope it helps!
DECLARE @rn INT;
DECLARE @nextrn INT;
;WITH CTE AS
(
SELECT rownum
FROM #tmpHCLC
WHERE break_ind = 'Y'
GROUP BY rownum
)
,CTE1 AS
(
SELECT b.rownum curr, k.rownum nex FROM CTE b
CROSS APPLY
(
SELECT TOP 1 a.rownum
FROM #tmpHCLC a
WHERE a.break_ind = 'Y'
AND a.rownum > a.rownum
)k
)
UPDATE c
SET c.concurrent_ind = c.concurrent_ind + CAST(@rn AS VARCHAR)
FROM #tmpHCLC c INNER JOIN CTE1 c1 ON c.rownum >= c1.curr AND c.rownum < c1.nex
WHERE concurrent_ind = 'Y'
Hope it helps!
The code as it's written will never UPDATE anything, because @nextrn is never given a value, and thus it remains NULL, which means that in the the UPDATE, the WHERE condition " AND rownum < @nextrn" can never be true, so the UPDATE can never actually UPDATE any row.
ASKER
would this not accomplish the same thing as the cursor the way its written?
UPDATE a
SET a.concurrent_ind = 'Y'+ cast(a.rownum AS VARCHAR(MAX))
FROM #tmpHCLC a
WHERE a.concurrent_ind = 'y' AND a.break_ind = 'Y'
UPDATE a
SET a.concurrent_ind = 'Y'+ cast(a.rownum AS VARCHAR(MAX))
FROM #tmpHCLC a
WHERE a.concurrent_ind = 'y' AND a.break_ind = 'Y'
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
This made it very simple and elegant to replace a cursor that was created by another application. Remember to always avoid cursors when ever possible It can be a major performance drain if no proper indexes exist.
Sorry about the previous comment, didn't have time then to look closely enough at the code.
But the new code is not necessarily the same as the original code:
In the original code, rows after the first row with "break_ind = 'Y'" do not require the break_ind to be set in order for the concurrent_ind to be updated, but of course in the new code every row would require break_ind to be set as well.
But the new code is not necessarily the same as the original code:
In the original code, rows after the first row with "break_ind = 'Y'" do not require the break_ind to be set in order for the concurrent_ind to be updated, but of course in the new code every row would require break_ind to be set as well.