June 27, 2007 at 6:32 am
Hi...
According to requirement I have to take 1000 fields in column1 of Table1 and pass those fields
one by one as parameter to another query. For this I used cursor and got result.
Can anyone help me how to get the result without using cursor and with less execution time.
I tried by using temporary table & hash table but they r taking time to execute.
DECLARE Cursor_Name CURSOR FOR
SELECT Column1 FROM Table1 WHERE <CONDITION>
OPEN Cursor_Name
DECLARE @Parameter
FETCH NEXT INTO @Parameter
WHILE(@@FETCH_STATUS<>0)
BEGIN
SELECT * FROM Table2 WHERE arameter=@Parameter">Parameter=@Parameter
FETCH NEXT INTO @Parameter
END
CLOSE Cursor_Name
DEALLOCATE Cursor_Name
Can anyone help to get better alternate to cursor in this query with less execution time.
Thanks in advance.
June 28, 2007 at 12:23 am
Hi, here is something i got from net. Hope this helps!!!
Eliminating Cursors
Summary:
T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance.
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!
June 28, 2007 at 12:27 am
Sorry about the formatting.
Eliminating Cursors
Summary:
T-SQL does some things wonderfully, but cursors are the bane of the language, often causing performance issues. Changing your queries around to remove cursors can be tricky and new author Kamran Ali brings us one technique he has used to dramatically improve performance.
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!
June 28, 2007 at 10:36 am
I have to ask... is there more complex logic that needs to be performed other than SELECT from table2?
Why not a JOIN as such:
SELECT t2.* FROM Table2 t2
JOIN Table1 t1 on t1.Column1 = t2.Column --(Parameter?)
WHERE t1.Column1 = <CONDITION>
Todd Carrier
MCITP - Database Administrator (SQL 2008)
MCSE: Data Platform (SQL 2012)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply