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
--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
This gives about 75 % performance increase
User 1 | User 2 |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READUNCOMMITTED BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- was previously 'Blade', now 'Super Blade' | |
| USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN SELECT Name from Production.Product WHERE ProductID =316 -- WILL RETURN 'Super Blade', even before committing transaction, | |
| COMMIT TRANSACTION |
User 1 | User 2 |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READCOMMITTED BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- was previously 'Blade', now 'Super Blade' | |
| USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL READ COMMITTED BEGIN TRAN SELECT Name from Production.Product WHERE ProductID =316 -- Deadlock until the COMMIT finishes for User 1 | |
| COMMIT TRANSACTION | |
-- Lock is released, and now will finally return 'Super Blade' |
User 1 | User 2 |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRAN SELECT Name from Production.Product WHERE ProductID= 316 -- returns 'Blade' | |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL REPEATABLEREAD BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID = 316 -- Deadlock, because User 2 still has the row | |
SELECT Name fromProduction.Product WHERE ProductID =316 -- Will still return 'Blade' | |
-- Still in deadlock until User 2 commits the transaction | |
COMMIT TRANSACTION | |
-- UPDATE completes we can COMMIT COMMIT TRANSACTION |
User 1 | User 2 |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVELSERIALIZABLE BEGIN TRAN SELECT COUNT(*) FROM Production.Product WHERESafetyStockLevel = 500 -- returns 167 | |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SERIALIZABLE BEGIN TRAN INSERT INTO Production.Product (ProductName,ProductNumber, SafetyStockLevel....) VALUES ('111','TEST111',500....) -- Deadlock, because this would affect the rowcount for User 2 | |
SELECT COUNT(*) FROM Production.Product WHERESafetyStockLevel = 500 -- STILL returns 167 | |
-- Still in deadlock until User 2 commits the transaction | |
COMMIT TRANSACTION | |
-- INSERT completes we can COMMIT COMMIT TRANSACTION | |
SELECT COUNT(*) FROM Production.Product WHERESafetyStockLevel = 500 -- NOW returns 168 |
User 1 | User 2 |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN UPDATE Production.Product SET Name = 'Super Blade' WHERE ProductID =316 -- was previously 'Blade', now 'Super Blade' | |
USE AdventureWorks GO SET TRANSACTION ISOLATION LEVEL SNAPSHOT BEGIN TRAN SELECT Name from Production.Product WHERE ProductID =316 -- Still 'Blade' | |
COMMIT TRANSACTION | |
SELECT Name from Production.Product WHERE ProductID =316 -- Still 'Blade' COMMIT TRANSACTION SELECT Name from Production.Product WHERE ProductID =316 -- NOW it's 'Super Blade' | |