Sql-server – Converting Cursor based filtering logic to CTE based

cursorssql servert-sql

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.

Best Answer

If I understand correctly, and you want to insert 10 values of the 2nd table for every iteration of the cursor (or cte), you can use this:

; WITH myCTE (value) AS
    ( SELECT value FROM sometable )
INSERT INTO @TOP10      
    SELECT t.Value1, t.Value2, t.Value3, t.Value4
    FROM myCTE AS mc
      CROSS APPLY 
        ( SELECT TOP (10)
              bt.Value1, bt.Value2, bt.Value3, bt.Value4
          FROM BaseTable AS bt 
          WHERE bt.SomeVariable = mc.value
          ORDER BY <SomeColumns>                     -- needs something here
        ) AS t ;

You also need an ORDER BY in the subquery with TOP to be consistent. Without order by, the database engine is free to return any 10 rows that match the conditions.

It could also be done using ROW_NUMBER():

; WITH myCTE (Value1, Value2, Value3, Value4) AS
    ( SELECT bt.Value1, bt.Value2, bt.Value3, bt.Value4,
             Rn = ROW_NUMBER() OVER (PARTITION BY bt.SomeVariable
                                     ORDER BY <SomeColumns>)
      FROM sometable AS s
        JOIN BaseTable AS bt 
          ON bt.SomeVariable = s.value
    )
INSERT INTO @TOP10      
    SELECT Value1, Value2, Value3, Value4
    FROM myCTE 
    WHERE Rn <= 10 ;

Related Solutions

Sql-server – Replace cursor with set-based approach

I would probably just do this the brute force way, and add indexes to support these joins where they don't exist. Not much gain to treating new customers and old customers any different once you've inserted all the customers that don't already exist:

INSERT dbo.Customer(fname, lname, address, city, state, zip, email)
    SELECT fname, lname, address, city, state, zip, email
     FROM dbo.job AS j
     WHERE job_no = @job_no
     AND NOT EXISTS
     (
        SELECT 1 FROM dbo.Customer
        WHERE fname = j.fname
        AND lname = j.lname
        AND (address = j.address OR email = j.email)
     );

INSERT INTO dbo.personal_code (customer_id, mailing_id, personal_code, email)
SELECT c.customer_id, j.mailing_id, j.personal_code, c.email
  FROM dbo.Customer AS c
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

INSERT dbo.personal_code_extra(personal_code_id, extra)
SELECT pc.personal_code_id, j.extra
  FROM dbo.personal_code AS pc
  INNER JOIN dbo.Customer AS c
  ON pc.customer_id = c.customer_id
  INNER JOIN dbo.job AS j
  ON c.fname = j.fname AND c.lname = j.lname 
  AND (c.address = j.address OR c.email = j.email)
  WHERE j.job_no = @job_no;

Sql-server – When converting a table valued function to inline, why do I get a lazy spool

A "lazy" spool, is not really lazy in the "bad" sense of the word. It's lazy in that it may remove the need to re-scan the input data for the given operation, resulting most of the time, in improved performance.

From this MSDN page:

The Lazy Spool logical operator stores each row from its input in a hidden temporary object stored in the tempdb database. If the operator is rewound (for example, by a Nested Loops operator) but no rebinding is needed, the spooled data is used instead of rescanning the input. If rebinding is needed, the spooled data is discarded and the spool object is rebuilt by rescanning the (rebound) input.

The Lazy Spool operator builds its spool file in a "lazy" manner, that is, each time the spool's parent operator asks for a row, the spool operator gets a row from its input operator and stores it in the spool, rather than consuming all rows at once.

You mention in your question that performance has increased by converting the function into an inline variant; I'd say that's winning. Looking at the largest cost operator is not always the best way to optimize your code; for instance that would never inform you that moving from a multi-statement-TVF to an inline-TVF would be so performance enhancing. If you want more specific performance improvements for the code in question, adding that code to your question will help us help you.

Best Answer

Related Solutions

Sql-server – Replace cursor with set-based approach

Sql-server – When converting a table valued function to inline, why do I get a lazy spool

Related Question