Performance Tuning – SQL Server – Cursors

CREATE
	OR

ALTER PROCEDURE Cursor_Simple
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @SalesOrderID INT
	DECLARE @Delay TINYINT
	DECLARE @SalesPersonID SMALLINT
	DECLARE @TerritoryID INT

	IF OBJECT_ID('tempdb..#DelayedByTerritory1') IS NOT NULL
		DROP TABLE #DelayedByTerritory1

	CREATE TABLE #DelayedByTerritory1 (
		TerritoryID INT
		,DelayedShipping INT
		)

	INSERT INTO #DelayedByTerritory1
	VALUES('1',0),('2',0),('3',0),('4',0),('5',0),('6',0)
    ,('7',0),('8',0),('9',0),('10',0),('11',0)

	DECLARE Product_Cursor CURSOR
	FOR
	SELECT top 10000 SalesOrderID
	FROM Sales.SalesOrderDetail

	OPEN Product_Cursor

	FETCH NEXT
	FROM Product_Cursor
	INTO @SalesOrderID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Delay = DateDiff(hh, OrderDate, ShipDate)
			,@SalesPersonID = SalesPersonID
		FROM sales.SalesOrderHeader
		WHERE SalesOrderID = @SalesOrderID

		IF (@Delay > 72)
		BEGIN
			SELECT @TerritoryID = TerritoryID
			FROM Sales.SalesPerson
			WHERE BusinessEntityID = @SalesPersonID

			UPDATE #DelayedByTerritory1
			SET DelayedShipping = DelayedShipping + 1
			WHERE TerritoryID = ISNULL(@TerritoryID, 11)
		END

		FETCH NEXT
		FROM Product_Cursor
		INTO @SalesOrderID
	END

	CLOSE Product_Cursor

	DEALLOCATE Product_Cursor

	
END
GO

CREATE
	OR

ALTER PROCEDURE Cursor_LOCAL
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @SalesOrderID INT
	DECLARE @Delay TINYINT
	DECLARE @SalesPersonID SMALLINT
	DECLARE @TerritoryID INT

	IF OBJECT_ID('tempdb..#DelayedByTerritory1') IS NOT NULL
		DROP TABLE #DelayedByTerritory1

	CREATE TABLE #DelayedByTerritory1 (
		TerritoryID INT
		,DelayedShipping INT
		)

	INSERT INTO #DelayedByTerritory1
	VALUES('1',0),('2',0),('3',0),('4',0),('5',0),('6',0)
    ,('7',0),('8',0),('9',0),('10',0),('11',0)

	DECLARE Product_Cursor CURSOR LOCAL
	FOR
	SELECT top 10000 SalesOrderID
	FROM Sales.SalesOrderDetail

	OPEN Product_Cursor

	FETCH NEXT
	FROM Product_Cursor
	INTO @SalesOrderID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Delay = DateDiff(hh, OrderDate, ShipDate)
			,@SalesPersonID = SalesPersonID
		FROM sales.SalesOrderHeader
		WHERE SalesOrderID = @SalesOrderID

		IF (@Delay > 72)
		BEGIN
			SELECT @TerritoryID = TerritoryID
			FROM Sales.SalesPerson
			WHERE BusinessEntityID = @SalesPersonID

			UPDATE #DelayedByTerritory1
			SET DelayedShipping = DelayedShipping + 1
			WHERE TerritoryID = ISNULL(@TerritoryID, 11)
		END

		FETCH NEXT
		FROM Product_Cursor
		INTO @SalesOrderID
	END

	CLOSE Product_Cursor

	DEALLOCATE Product_Cursor

	
END
GO


CREATE
	OR

ALTER PROCEDURE Cursor_LOCAL_STATIC_READ_ONLY
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @SalesOrderID INT
	DECLARE @Delay TINYINT
	DECLARE @SalesPersonID SMALLINT
	DECLARE @TerritoryID INT

	IF OBJECT_ID('tempdb..#DelayedByTerritory1') IS NOT NULL
		DROP TABLE #DelayedByTerritory1

	CREATE TABLE #DelayedByTerritory1 (
		TerritoryID INT
		,DelayedShipping INT
		)

	INSERT INTO #DelayedByTerritory1
	VALUES('1',0),('2',0),('3',0),('4',0),('5',0),('6',0)
    ,('7',0),('8',0),('9',0),('10',0),('11',0)

	DECLARE Product_Cursor CURSOR LOCAL STATIC READ_ONLY
	FOR
	SELECT top 10000 SalesOrderID
	FROM Sales.SalesOrderDetail

	OPEN Product_Cursor

	FETCH NEXT
	FROM Product_Cursor
	INTO @SalesOrderID

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @Delay = DateDiff(hh, OrderDate, ShipDate)
			,@SalesPersonID = SalesPersonID
		FROM sales.SalesOrderHeader
		WHERE SalesOrderID = @SalesOrderID

		IF (@Delay > 72)
		BEGIN
			SELECT @TerritoryID = TerritoryID
			FROM Sales.SalesPerson
			WHERE BusinessEntityID = @SalesPersonID

			UPDATE #DelayedByTerritory1
			SET DelayedShipping = DelayedShipping + 1
			WHERE TerritoryID = ISNULL(@TerritoryID, 11)
		END

		FETCH NEXT
		FROM Product_Cursor
		INTO @SalesOrderID
	END

	CLOSE Product_Cursor

	DEALLOCATE Product_Cursor


END
GO

Author: Sauras Pandey

Sauras Pandey is an MCP, SQL Server DBA/Architect with 9+ years of experience working in a variety of environments

Leave a Reply

Your email address will not be published.