Skip to content

Instantly share code, notes, and snippets.

@LitKnd

LitKnd/SQLWorkbooks_Case-of-the-Slow-Temp-Table.sql

Created July 27, 2017 15:09
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save LitKnd/5f96bc9550fce1b1382f90e9e2c19035 to your computer and use it in GitHub Desktop.
Save LitKnd/5f96bc9550fce1b1382f90e9e2c19035 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
SQLWorkbooks_Case-of-the-Slow-Temp-Table.sql
/***********************************************************************
Copyright 2016, Kendra Little - LittleKendra.com
MIT License, http://www.opensource.org/licenses/mit-license.php
***********************************************************************/
/* Doorstop*/
RAISERROR('Did you mean to run the whole thing?', 20,1) WITH LOG;
GO
/**********************************
Recreate database
**********************************/
USE master;
GO
IF DB_ID('ModTest') IS NOT NULL
BEGIN
ALTER DATABASE ModTest
SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
DROP DATABASE ModTest;
END
CREATE DATABASE ModTest;
GO
USE ModTest;
GO
/****************************************************
1. Set up test procedures
Compare duration for each test ....
****************************************************/
/* This procedure...
Creates a user table if it doesn't exists
Adds 1 million rows
Updates the number of rows specific in the parameter
Prints the duration of that update to the messages tab
*/
DROP PROCEDURE IF EXISTS dbo.UserDatabaseTableTest;
GO
CREATE PROCEDURE dbo.UserDatabaseTableTest
@RowID INT
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DROP TABLE IF EXISTS dbo.UserDatabaseTable;
CREATE TABLE dbo.UserDatabaseTable (
RowID INT IDENTITY(1,1),
CharColumn CHAR(500) NOT NULL,
CONSTRAINT PK_UserDatabaseTablePK PRIMARY KEY CLUSTERED (RowID)
);
RAISERROR('Add one million rows to user table...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT dbo.UserDatabaseTable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows in user table...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE dbo.UserDatabaseTable SET CharColumn = 'bar'
WHERE RowID <= @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of user table (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
EXEC dbo.UserDatabaseTableTest @RowID = 800000;
GO
/* This procedure...
Creates a temp table
Adds 1 million rows
Updates the number of rows specified in the parameter
Prints the duration of that update to the messages tab
*/
DROP PROCEDURE IF EXISTS dbo.TempTableTest;
GO
CREATE PROCEDURE dbo.TempTableTest
@RowID INT
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
CREATE TABLE #TempTable (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to #TempTable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT #TempTable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows #TempTablee...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE #TempTable SET CharColumn = 'bar'
WHERE RowID <= @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of #TempTable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
EXEC dbo.TempTableTest @RowID = 800000;
GO
/* This procedure...
Creates a table variable
Adds 1 million rows
Updates the number of rows specified in the parameter
Prints the duration of that update to the messages tab
*/
DROP PROCEDURE IF EXISTS dbo.TableVariableTest;
GO
CREATE PROCEDURE dbo.TableVariableTest
@RowID INT
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DECLARE @TableVariable TABLE (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to @TableVariable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT @TableVariable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows in @TableVariable...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE @TableVariable SET CharColumn = 'bar'
WHERE RowID <= @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of @TableVariable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
EXEC dbo.TableVariableTest @RowID = 800000;
GO
/* This procedure...
Creates a table variable
Adds 1 million rows
Updates the number of rows specified in the parameter with OPTION RECOMPILE
Prints the duration of that update to the messages tab
*/
DROP PROCEDURE IF EXISTS dbo.TableVariableTestRECOMPILE;
GO
CREATE PROCEDURE dbo.TableVariableTestRECOMPILE
@RowID INT
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DECLARE @TableVariable TABLE (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to @TableVariable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT @TableVariable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows in @TableVariable...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE @TableVariable SET CharColumn = 'bar'
WHERE RowID <= @RowID OPTION (RECOMPILE);
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of @TableVariable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
EXEC dbo.TableVariableTestRECOMPILE @RowID = 800000;
GO
/****************************************************
2. Compare Statistics IO for each test ....
****************************************************/
SET STATISTICS IO ON;
GO
EXEC dbo.UserDatabaseTableTest @RowID = 800000;
GO
EXEC dbo.TempTableTest @RowID = 800000;
GO
EXEC dbo.TableVariableTest @RowID = 800000;
GO
EXEC dbo.TableVariableTestRECOMPILE @RowID = 800000;
GO
SET STATISTICS IO OFF;
GO
/**** Back to the slides ****/
/****************************************************
3. Compare the execution plans
For each:
Look at estimated plan
Run with actual plan
Look at Actual IO Statistics in the actual plan
****************************************************/
EXEC dbo.UserDatabaseTableTest @RowID = 800000 WITH RECOMPILE;
GO
EXEC dbo.TempTableTest @RowID = 800000 WITH RECOMPILE;
GO
EXEC dbo.TableVariableTest @RowID = 800000 WITH RECOMPILE;
GO
EXEC dbo.TableVariableTestRECOMPILE @RowID = 800000 WITH RECOMPILE;
GO
/* What if we update far fewer rows? Can we get the 'narrow' plan?
These are going to be faster because we're doing a MUCH smaller update.
What we're interested in is the plan differences (especially on the "slow" queries)
Run with actual plans */
EXEC dbo.UserDatabaseTableTest @RowID = 8000 WITH RECOMPILE;
GO
EXEC dbo.TempTableTest @RowID = 8000 WITH RECOMPILE;
GO
EXEC dbo.TableVariableTest @RowID = 8000 WITH RECOMPILE;
GO
EXEC dbo.TableVariableTestRECOMPILE @RowID = 8000 WITH RECOMPILE;
GO
/* phooey */
EXEC dbo.TableVariableTestRECOMPILE @RowID = 1 WITH RECOMPILE;
GO
/**** Back to the slides ****/
/****************************************************
4. What if it's a batch of TSQL, not a proc?
****************************************************/
--UserDatabaseTableTest
--Run once to look at duration and stats io (plans off), then run with actual plans
SET STATISTICS IO ON;
GO
DECLARE @RowID INT = 800000;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DROP TABLE IF EXISTS dbo.UserDatabaseTable;
CREATE TABLE dbo.UserDatabaseTable (
RowID INT IDENTITY(1,1),
CharColumn CHAR(500) NOT NULL,
CONSTRAINT PK_UserDatabaseTablePK PRIMARY KEY CLUSTERED (RowID)
);
RAISERROR('Add one million rows to user table...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT dbo.UserDatabaseTable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows in user table...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE dbo.UserDatabaseTable SET CharColumn = 'bar'
WHERE RowID <= @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of user table (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
SET STATISTICS IO OFF;
GO
--TempTableTest
--Run once to look at duration (plans off), then run with actual plans
SET STATISTICS IO ON;
GO
DECLARE @RowID INT = 800000;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DROP TABLE IF EXISTS #TempTable;
CREATE TABLE #TempTable (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to #TempTable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT #TempTable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows #TempTablee...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE #TempTable SET CharColumn = 'bar'
WHERE RowID <= @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of #TempTable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
SET STATISTICS IO OFF;
GO
--TableVariableTest
--Run once to look at duration (plans off), then run with actual plans
SET STATISTICS IO ON;
GO
DECLARE @RowID INT = 800000;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DECLARE @TableVariable TABLE (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to @TableVariable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT @TableVariable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows in @TableVariable...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE @TableVariable SET CharColumn = 'bar'
WHERE RowID <= @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of @TableVariable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
SET STATISTICS IO OFF;
GO
--TableVariableTestRECOMPILE
--Run once to look at duration (plans off), then run with actual plans
SET STATISTICS IO ON;
GO
DECLARE @RowID INT = 800000;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
DECLARE @TableVariable TABLE (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to @TableVariable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT @TableVariable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows in @TableVariable...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE @TableVariable SET CharColumn = 'bar'
WHERE RowID <= @RowID OPTION (RECOMPILE);
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of @TableVariable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
SET STATISTICS IO OFF;
GO
/****************************************************
5. Bug repellent - two varieties
****************************************************/
/* OPTIMIZE FOR a narrow plan with the temp table */
DROP PROCEDURE IF EXISTS dbo.TempTableOptimizeForTest;
GO
CREATE PROCEDURE dbo.TempTableOptimizeForTest
@RowID INT
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
CREATE TABLE #TempTable (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to #TempTable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT #TempTable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows #TempTablee...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
UPDATE #TempTable SET CharColumn = 'bar'
WHERE RowID <= @RowID
OPTION (OPTIMIZE FOR (@RowID = 8000)); /* <------- */
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of #TempTable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
--Run once to look at duration (plans off), then run with actual plans
SET STATISTICS IO ON;
GO
EXEC dbo.TempTableOptimizeForTest @RowID = 800000;
GO
SET STATISTICS IO OFF;
GO
/* Change the scope with Dynamic SQL */
DROP PROCEDURE IF EXISTS dbo.TempTableDSQLTest;
GO
CREATE PROCEDURE dbo.TempTableDSQLTest
@RowID INT
AS
SET NOCOUNT OFF;
SET XACT_ABORT ON;
DECLARE @dsql NVARCHAR(MAX);
DECLARE @t1 DATETIME2(7), @Durationms INT, @msg nvarchar(1000);
CREATE TABLE #TempTable (
RowID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
CharColumn CHAR(500) NOT NULL
);
RAISERROR('Add one million rows to #TempTable...', 1, 1) WITH NOWAIT;
--This query adapted from pattern attributed
--to Itzik Ben-Gan in https://sqlperformance.com/2013/01/t-sql-queries/generate-a-set-1
WITH e1(n) AS
(
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL
SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0 UNION ALL SELECT 0
),
e2(n) AS (SELECT 0 FROM e1 CROSS JOIN e1 AS b),
e3(n) AS (SELECT 0 FROM e1 CROSS JOIN e2),
e4(n) AS (SELECT 0 FROM e1 CROSS JOIN e3),
e5(n) AS (SELECT 0 FROM e1 CROSS JOIN e4),
e6(n) AS (SELECT 0 FROM e1 CROSS JOIN e5)
INSERT #TempTable (CharColumn )
SELECT TOP (1000000) 'foo' FROM e6;
RAISERROR('Update rows #TempTablee...', 1, 1) WITH NOWAIT;
SELECT @t1 = SYSDATETIME();
SET @dsql = N'
UPDATE #TempTable SET CharColumn = ''bar''
WHERE RowID <= @RowID'
EXEC sp_executesql @stmt = @dsql,
@params = N'@RowID INT',
@RowID = @RowID;
SET @Durationms = DATEDIFF(ms, @t1, SYSDATETIME());
SET @msg = 'Duration of update of #TempTable (ms): ' + CAST(@Durationms as nvarchar(10));
RAISERROR(@msg, 1, 1) WITH NOWAIT;
GO
--Run once to look at duration (plans off), then run with actual plans
SET STATISTICS IO ON;
GO
EXEC dbo.TempTableDSQLTest @RowID = 800000;
GO
SET STATISTICS IO OFF;
GO
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment