I have a cursor that runs long for about minutes which I was able to reduce to run in seconds using a CTE (Common Table Expressions). The result set was identical until there was given a condition where I have to get the top 10 values for each iteration in cursor which I am not able to do with set based logic.
Assuming the cursor based logic being:
SET @mycurse = CURSOR FOR
SELECT value FROM sometable
OPEN @mycurse
FETCH NEXT FROM @mycurse INTO @SomeVariable
IF @@FETCH_STATUS <> 0
Print 'Error in the Cursor Fetch Statement'
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TOP10
SELECT Value1,Value2,Value3,Value4
FROM (
select Value1,Value1,Value3,Value4 from BaseTable where SomeVariable=@SomeVariable
)
FETCH NEXT FROM @mycurse INTO @SomeVariable
END
CLOSE @mycurse
And the CTE based soltion looks like this:
;With myCTE(value)
as
(
SELECT value FROM sometable
)
INSERT INTO @TOP10
SELECT Value1,Value2,Value3,Value4
FROM
(select
Value1, Value1, Value3, Value4
from BaseTable BT, myCTE MC
where BT.SomeVariable = MC.SomeVariable)
As you see both work fine and return identical result, with CTE version using significantly less time. But the logic of having top 10 values makes it go wrong, it returns only 10 values where it is supposed to return 20 values.
SET @mycurse = CURSOR FOR
SELECT value FROM sometable
OPEN @mycurse
FETCH NEXT FROM @mycurse INTO @SomeVariable
IF @@FETCH_STATUS <> 0
Print 'Error in the Cursor Fetch Statement'
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @TOP10
SELECT top 10 Value1,Value2,Value3,Value4
FROM
(select
Value1, Value1, Value3, Value4
from BaseTable
where SomeVariable = @SomeVariable)
FETCH NEXT FROM @CUR_Liability INTO @VarLiability
END
CLOSE @CUR_Liability
So please provide me with a solution for this scenario, it does not necessarily need to be CTE based my goal is to reduce run time. Also I can update with sample result set if this is not clear enough. Thanks in advance.
Update : I have managed to cook up a solution using Row_Number()
function but I am still curious about other options.
TOP 10
is supposed to return 20 values? (or just add example data)ORDER BY
for theTOP
to be consistent.