by Habeeb (1 Submission)
Category: Databases/Data Access/DAO/ADO
Compatability: VB.NET
Difficulty: Unknown Difficulty
Originally Published: Tue 31st January 2006
Date Added: Mon 8th February 2021
Rating: (1 Votes)
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
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.
No comments have been posted about Have you ever wondered how you can speed up that SQL code of yours where you were forced to use a C. Why not be the first to post a comment about Have you ever wondered how you can speed up that SQL code of yours where you were forced to use a C.