Cursor Vs Set Based Operation

-- SQL Server Nested Cursors example - Execution timing setu

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime = getdate() 

-- Setup local variables

DECLARE     @IterationID INT,

            @OrderDetail VARCHAR(max),

            @ProductName VARCHAR(10)

-- Setup table variable

DECLARE @Result TABLE (SalesOrderID INT, OrderDetail VARCHAR(max))

             

-- OUTER CURSOR declaration

DECLARE curOrdersForReport CURSOR FOR

SELECT SalesOrderID

FROM Sales.SalesOrderHeader

WHERE Year(OrderDate) = 2004

  AND Month(OrderDate) between 2 and 4

ORDER BY SalesOrderID 

OPEN curOrdersForReport

FETCH NEXT FROM curOrdersForReport INTO @IterationID

PRINT 'OUTER LOOP START'

WHILE (@@FETCH_STATUS = 0)

BEGIN

      SET @OrderDetail = ''

      -- INNER CURSOR declaration

      DECLARE curDetailList CURSOR FOR

      SELECT p.ProductNumber

      FROM Sales.SalesOrderDetail pd

      INNER JOIN Production.Product p

      ON pd.ProductID = p.ProductID

      WHERE pd.SalesOrderID = @IterationID

      ORDER BY SalesOrderDetailID 

      OPEN curDetailList

      FETCH NEXT FROM curDetailList INTO @ProductName

      PRINT 'INNER LOOP START'

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

            SET @OrderDetail += @ProductName + ', '

            FETCH NEXT FROM curDetailList INTO @ProductName

            PRINT 'INNER LOOP'

      END -- inner while

      CLOSE curDetailList

      DEALLOCATE curDetailList               

      -- Truncate trailing comma

      SET @OrderDetail = left(@OrderDetail, len(@OrderDetail)-1)

      INSERT INTO @Result VALUES (@IterationID, @OrderDetail)

      FETCH NEXT FROM curOrdersForReport INTO @IterationID

      PRINT 'OUTER LOOP'

END -- outer while

CLOSE curOrdersForReport

DEALLOCATE curOrdersForReport 

-- Publish results

SELECT * FROM @Result ORDER BY SalesOrderID

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

GO

-- 2800 msecs

-----------------------------------------------------------------

-- Equivalent set-based operations solution

-----------------------------------------------------------------

-- Execution timing setup

DBCC DROPCLEANBUFFERS

DECLARE @StartTime datetime = getdate()

SELECT  poh.SalesOrderID,

      OrderDetail = Stuff((

      SELECT ', ' + ProductNumber as [text()]

      FROM Sales.SalesOrderDetail pod

      INNER JOIN Production.Product p

      ON pod.ProductID = p.ProductID

      WHERE pod.SalesOrderID = poh.SalesOrderID

      ORDER BY SalesOrderDetailID

      FOR XML PATH ('')), 1, 1, '')

FROM Sales.SalesOrderHeader poh

WHERE Year(OrderDate) = 2004

  AND Month(OrderDate) between 2 and 4

ORDER BY SalesOrderID ; 

-- Timing result

SELECT ExecutionMsec = datediff(millisecond, @StartTime, getdate())

GO

-- 400 msecs 

/* Partial results 

SalesOrderID      OrderDetail

63119             FR-M63S-40

63120             SH-W890-S, SH-W890-M, SH-W890-L

63121             SH-W890-S, SH-W890-M

63122             PD-R347, HL-U509-B, HL-U509

*/