How to Perform SQL Server Row-by-Row Operations Without Cursors

Here is an example procedure with a nested loop and no cursors:

if exists (select * from sysobjects where name = N’prcNestedLoopExample’)

   drop procedure prcNestedLoopExample

go

CREATE PROCEDURE  prcNestedLoopExample

AS

/*

**  Non-cursor method to cycle through the Customer table **  and get Customer Name for each iCustId. Get all
**  products for each iCustid.

**

** Revision History:

** —————————————————–

**  Date        Name       Description       Project

** —————————————————–

**  08/12/03    DVDS       Create            —–

**

*/

SET NOCOUNT ON

– declare all variables!

DECLARE     @iReturnCode          int,

            @iNextCustRowId       int,

            @iCurrentCustRowId    int,

            @iCustLoopControl     int,

            @iNextProdRowId       int,

            @iCurrentProdRowId    int,

            @vchCustomerName      nvarchar(255),

            @chProductNumber      nchar(30),

            @vchProductName       nvarchar(255)

           

– Initialize variables!

SELECT     @iCustLoopControl = 1

SELECT     @iNextCustRowId = MIN(iCustId)

FROM  Customer

– Make sure the table has data.

IF ISNULL(@iNextCustRowId,0) = 0

   BEGIN

            SELECT ‘No data in found in table!’

            RETURN

   END

– Retrieve the first row

SELECT      @iCurrentCustRowId = iCustId,

            @vchCustomerName = vchCustomerName

FROM  Customer

WHERE       iCustId = @iNextCustRowId

– Start the main processing loop.

WHILE @iCustLoopControl = 1

   BEGIN

        – Begin the nested(inner) loop.

        – Get the first product id for current customer.

            SELECT  @iNextProdRowId = MIN(iProductId)

            FROM    CustomerProduct

            WHERE   iCustId = @iCurrentCustRowId

           

         — Make sure the product table has data for
         — current customer.

         IF ISNULL(@iNextProdRowId,0) = 0

            BEGIN

              SELECT ‘No products found for this customer.’

            END

         ELSE

            BEGIN

           – retrieve the first full product row for
           — current customer.

              SELECT  @iCurrentProdRowId = iProductId,

                       @chProductNumber = chProductNumber,

                       @vchProductName = vchProductName

              FROM  CustomerProduct

              WHERE           iProductId = @iNextProdRowId

             END

            WHILE ISNULL(@iNextProdRowId,0) <> 0

           BEGIN

        — Do the inner loop row-level processing here.

        – Reset the product next row id.

                SELECT  @iNextProdRowId = NULL

                                   

       – Get the next Product id for the current customer

                SELECT @iNextProdRowId = MIN(iProductId)

                FROM  CustomerProduct

                WHERE iCustId = @iCurrentCustRowId

                AND  iProductId > @iCurrentProdRowId

     – Get the next full product row for current customer.

             SELECT     @iCurrentProdRowId = iProductId,

                        @chProductNumber = chProductNumber,

                        @vchProductName = vchProductName

             FROM  CustomerProduct

             WHERE           iProductId = @iNextProdRowId

           END

      – Reset inner loop variables.

            SELECT       @chProductNumber = NULL

            SELECT       @vchProductName = NULL

            SELECT       @iCurrentProdRowId = NULL

       — Reset outer looping variables.  

            SELECT           @iNextCustRowId = NULL

           

       – Get the next iRowId.

            SELECT           @iNextCustRowId = MIN(iCustId)

            FROM  Customer

            WHERE           iCustId > @iCurrentCustRowId

       – Did we get a valid next row id?

            IF ISNULL(@iNextCustRowId,0) = 0

               BEGIN

                        BREAK

               END

        – Get the next row.

            SELECT    @iCurrentCustRowId = iCustId,

                      @vchCustomerName = vchCustomerName

            FROM  Customer

            WHERE     iCustId = @iNextCustRowId

           

   END

RETURN

In the above example we are looping through a customer table and, for each customer id, we are then looping through a customer product table, retrieving all existing product records for that customer. Notice that a different technique is used to exit from the inner loop. Instead of using a BREAK statement, the WHILE loop depends directly on the value of @iNextProdRowId. When it becomes NULL, having no value, the WHILE loop ends.

Conclusion

SQL Cursors are very useful and powerful because they offer a high degree of row-level data manipulation, but this power comes at a price: negative performance. In this article I have demonstrated an alternative that offers much of the cursor’s flexibility, but without the negative impact to performance. I have used this alternative looping method several times in my professional career to the benefit of cutting many hours of processing time on production SQL Servers.

Pages: 1 2 3




Related Articles :

  • No Related Articles Found

2 Responses to “How to Perform SQL Server Row-by-Row Operations Without Cursors”

  1. The following are the techniques which can be used instead of cursors:

    1. CTE
    2. Loop
    3. Temporary Tables

  2. There’s lots of claims of performance in this article but no substantial evidence that the Temp Table/While Loop replacement for the Cursor is any faster. A well written “firehose” cursor will usually beat a well written Temp Table/While loop combination.

    Yes, cursors are usually a bad thing and should be avoided but only if you can come up with a proper set-based solution. That goes for any form of RBAR including Temp Tables/While loops and recursive CTEs.

Software Reviews | Book Reviews | FAQs | Tips | Articles | Performance Tuning | Audit | BI | Clustering | Developer | Reporting | DBA | ASP.NET Ado | Views tips | | Developer FAQs | Replication Tips | OS Tips | Misc Tips | Index Tuning Tips | Hints Tips | High Availability Tips | Hardware Tips | ETL Tips | Components Tips | Configuration Tips | App Dev Tips | OLAP Tips | Admin Tips | Software Reviews | Error | Clustering FAQs | Performance Tuning FAQs | DBA FAQs |