Evaluating the Use of Cursor/Iterative Solutions vs. Set-Based Solutions
Iterations for operations that have to be done per row
-- procedure doing work for an input customer
USE TSQL2012;
IF OBJECT_ID(N'Sales.ProcessCustomer', N'P') IS NOT NULL
DROP PROC Sales.ProcessCustomer;
GO
CREATE PROC Sales.ProcessCustomer
(
@custid AS INT
)
AS
PRINT 'Processing customer ' + CAST(@custid AS VARCHAR(10));
GO
-- iterations with a cursor
SET NOCOUNT ON;
DECLARE @curcustid AS INT;
DECLARE cust_cursor CURSOR FAST_FORWARD FOR
SELECT custid
FROM Sales.Customers;
OPEN cust_cursor;
FETCH NEXT FROM cust_cursor INTO @curcustid;
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC Sales.ProcessCustomer @custid = @curcustid;
FETCH NEXT FROM cust_cursor INTO @curcustid;
END;
CLOSE cust_cursor;
DEALLOCATE cust_cursor;
GO
/* Specifies a FORWARD_ONLY, READ_ONLY cursor with performance optimizations enabled. FAST_FORWARD cannot be specified if FOR_UPDATE is also specified. */
-- iterations without a cursor
SET NOCOUNT ON;
DECLARE @curcustid AS INT;
SET @curcustid = (SELECT TOP (1) custid
FROM Sales.Customers
ORDER BY custid);
WHILE @curcustid IS NOT NULL
BEGIN
EXEC Sales.ProcessCustomer @custid = @curcustid;
SET @curcustid = (SELECT TOP (1) custid
FROM Sales.Customers
WHERE custid > @curcustid
ORDER BY custid);
END;
GO
Cursor vs. set-based solutions to data manipulation tasks
-- definition of helper function GetNums
IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
GO
CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
AS
RETURN
WITH
L0 AS (SELECT c FROM (VALUES(1),(1)) AS D(c)),
L1 AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
CROSS JOIN dbo.GetNums(1, @rows_per_partition) AS RPP;
GO
/* TABLOCKS: pecifies that the acquired lock is applied at the table level. The type of lock that is acquired depends on the statement being executed. For example, a SELECT statement may acquire a shared lock. By specifying TABLOCK, the shared lock is applied to the entire table instead of at the row or page level. If HOLDLOCK is also specified, the table lock is held until the end of the transaction.*/
/* ABS(CHECKSUM(NEWID())) : The NEWID() function will be called for each row returned by the SELECT statement, which results in 5 separate GUID values. From there, each of those 5 is passed to CHECKSUM() which returns an index hash value which happens to be an integer. ABS() finally forces it to be a positive integer. */
-- cursor solution for running totals (66 seconds)
DECLARE @Result AS TABLE
(
actid INT,
tranid INT,
val MONEY,
balance MONEY
);
DECLARE
@actid AS INT,
@prvactid AS INT,
@tranid AS INT,
@val AS MONEY,
@balance AS MONEY;
DECLARE C CURSOR FAST_FORWARD FOR
SELECT actid, tranid, val
FROM dbo.Transactions
ORDER BY actid, tranid;
OPEN C
FETCH NEXT FROM C INTO @actid, @tranid, @val;
SELECT @prvactid = @actid, @balance = 0;
WHILE @@fetch_status = 0
BEGIN
IF @actid <> @prvactid
SELECT @prvactid = @actid, @balance = 0;
SET @balance = @balance + @val;
INSERT INTO @Result VALUES(@actid, @tranid, @val, @balance);
FETCH NEXT FROM C INTO @actid, @tranid, @val;
END
CLOSE C;
DEALLOCATE C;
SELECT * FROM @Result;
GO
-- set-based solution for running totals using window functions (4 seconds)
SELECT actid, tranid, val,
SUM(val) OVER(PARTITION BY actid
ORDER BY tranid
ROWS UNBOUNDED PRECEDING) AS balance
FROM dbo.Transactions;
-- set-based solution for running totals using joins (46 minutes, 53 seconds)
SELECT T1.actid, T1.tranid, T1.val,
SUM(T2.val) AS balance
FROM dbo.Transactions AS T1
JOIN dbo.Transactions AS T2
ON T2.actid = T1.actid
AND T2.tranid <= T1.tranid
GROUP BY T1.actid, T1.tranid, T1.val;
Using Temporary Tables vs. Table Variables
Scope
-- temp table
CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
EXEC('SELECT col1 FROM #T1;');
GO
SELECT col1 FROM #T1;
GO
DROP TABLE #T1;
GO
-- table variable not visible in inner levels
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
EXEC('SELECT col1 FROM @T1;');
GO
-- error
Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@T1".
-- table variable not visible across batches
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL
);
INSERT INTO @T1(col1) VALUES(10);
GO
SELECT col1 FROM @T1;
GO
-- error
Msg 1087, Level 15, State 2, Line 2
Must declare the table variable "@T1".
DDL and indexes
-- temp table: run from two sessions
CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
CONSTRAINT PK_#T1 PRIMARY KEY(col1)
);
-- error in second
Msg 2714, Level 16, State 5, Line 1
There is already an object named 'PK_#T1' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
-- cleanup in first
DROP TABLE #T1;
-- run from two sessions
CREATE TABLE #T1
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1)
);
-- can create indexes after the fact
CREATE UNIQUE NONCLUSTERED INDEX idx_col2 ON #T1(col2);
-- cleanup
DROP TABLE #T1;
-- table variable: not allowed to name constraints
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
CONSTRAINT PK_@T1 PRIMARY KEY(col1)
);
-- error
Msg 156, Level 15, State 2, Line 6
Incorrect syntax near the keyword 'CONSTRAINT'.
-- unnamed constraints
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1)
);
-- cannot create indexes after the fact
-- need to define constraints that create indexes
DECLARE @T1 AS TABLE
(
col1 INT NOT NULL,
col2 INT NOT NULL,
col3 DATE NOT NULL,
PRIMARY KEY(col1),
UNIQUE(col2)
);
Physical representation in tempdb
-- temp table
CREATE TABLE #T1
(
col1 INT NOT NULL
);
INSERT INTO #T1(col1) VALUES(10);
SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
