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!
