SQLServerCentral Article

Eliminating Cursors

,

Have you ever wondered how you can speed up that SQL code of yours where you were forced to use a CURSOR and ended up having a pretty slow code? Here is one method that our team found while working on a current project.

T-SQL provides us with a CURSOR statement to work on the data on a row-by-row basis. Cursors are especially handy when you are using OUTPUT stored procedures and need to pass one ID to the stored procedure at a time. However, this kills the whole theory of set-based operations, which are inherently faster than their row based counterparts. The following is one way of converting your CURSOR statement using the WHILE keyword in such a scenario

Suppose you have the following SQL statement:

        DECLARE @item_category_id INT
        DECLARE @order_id INT
        DECLARE @purchase_order_id INT
        
        DECLARE item_cursor CURSOR FAST_FORWARD FOR
        
        SELECT
            it.item_category_id
            ,ord.order_id
        FROM dbo.item_categories it
        
        INNER JOIN dbo.orders ord
        ON ord.item_category_id = it.item_category_id
        
        WHERE ord.order_date >= '1-sep-05' 
        and it.isSuspended != 1 
        
        OPEN item_cursor
        
        FETCH NEXT FROM item_cursor INTO
        @item_category_id
        ,@order_id
        
        WHILE @@FETCH_STATUS = 0
            BEGIN
                    EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
                    
                    /*
                        Call other code here to process your purchase order for this item
                    */                    
                    FETCH NEXT FROM item_cursor INTO
                    @item-category_id
                    ,@order_id
            END
    

Now here is the code, which does not use a CURSOR but achieves the same result using a WHILE loop. The key here is to get all the item categories which fit our WHERE clause into a memory table and use the Primary Key of this table to pick up each successive item category.

        --Declare variables
        DECLARE @item_category_id INT
        DECLARE @order_id INT
        DECLARE @purchase_order_id INT
        
        --Declare a memory table
        DECLARE @item_table TABLE (primary_key INT IDENTITY(1,1) NOT NULL, --THE IDENTITY STATEMENT IS IMPORTANT!
                                    item_category_id INT,
                                    order_id INT
                                    )
                                    
        --now populate this table with the required item category values
        INSERT INTO @item_table
        SELECT  -- Same SELECT statement as that for the CURSOR
            it.item_category_id
            ,ord.order_id
        FROM dbo.item_categories it
        
        INNER JOIN dbo.orders ord
        ON ord.item_category_id = it.item_category_id
        
        WHERE ord.order_date >= '1-sep-05' 
        and it.isSuspended != 1                       
    
        DECLARE @item_category_counter INT
        DECLARE @loop_counter INT
        
        SET @loop_counter = ISNULL(SELECT COUNT(*) FROM @item_table),0) -- Set the @loop_counter to the total number of rows in the 
                                                                        -- memory table
                                                                      
        SET @item_category_counter = 1
        
        WHILE @loop_counter > 0 AND @item_category_counter <= @loop_counter
            BEGIN
                SELECT @item_category_id = item_category_id
                       ,@order_id = order_id
                FROM @item_table 
                WHERE primary_key = @item_category_counter
                
                --Now pass the item-category_id and order_id to the OUTPUT stored procedure
                 EXEC dbo.usp_generate_purchase_order @item_category_id, @order_id, @purchase_order_id OUTPUT
                    
                /*
                    Call other code here to process your pruchase order for this item
                */                
               SET @item_category_counter = @item_category_counter + 1
            END
    

That's it! You have just converted your CURSOR statement to a typical SQL WHILE loop. We noticed that this approach gave us a 75% increase in performance over the statement using the CURSOR keyword.

Happy SQLing!

Rate

3.67 (81)

You rated this post out of 5. Change rating

Share

Share

Rate

3.67 (81)

You rated this post out of 5. Change rating