• Madeira Team

Staging, temp and variable tables – performance, recompilation and what’s in between

A lot of times, when creating a procedure, we need to use some kind of an empty table in order to perform all kinds of calculations. The table is only needed for the calculation process itself, and has no other functionality otherwise. In most cases I choose to use a temporary table in order to do this, but I must admit that I’ve never given it much thought in terms of performance. It always seemed like the natural thing to do, but is it the best thing to do?

First, let consider our options:

  1. Staging table (dbo.MyTable)

  2. Temp table (#MyTable)

  3. Table variable (@MyTable)

In order to find out which one is the best choice, I ran a little test. I used all 3 types of tables in order to execute the same kind of procedure and measured the time it took to execute. I did this for 2 kinds of procedures (simple and complex calculation) and for various record sets from 10 records to 1,000,000 records. I’ve also cleaned the cache in order to make the procedures recompile and see how it affected performance for every type of table.

The test:

I first created some test data to be used throughout the test. The tables held 1,000,000 records of random values from 1 to 10,000. The table was indexed using a clustered primary key.

Transact-SQL

-- Test data table CREATE TABLE dbo.DataTable ( Id INT NOT NULL IDENTITY (1,1) , Value INT NOT NULL , CONSTRAINT pk_DataTable PRIMARY KEY CLUSTERED (Id) ); -- 1,000,000 recordes of test data INSERT INTO dbo.DataTable ( Value ) SELECT TOP 1000000 ABS(CHECKSUM(NEWID())%10000) + 1 FROM sys.all_columns AS T1 CROSS JOIN sys.all_columns AS T2; GO

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

-- Test data table

CREATE TABLE

dbo.DataTable

(

Id INT NOT NULL IDENTITY (1,1) ,

Value INT NOT NULL ,

CONSTRAINT pk_DataTable PRIMARY KEY CLUSTERED (Id)

);

-- 1,000,000 recordes of test data

INSERT INTO

dbo.DataTable

(

Value

)

SELECT TOP 1000000

ABS(CHECKSUM(NEWID())%10000) + 1

FROM

sys.all_columns AS T1

CROSS JOIN

sys.all_columns AS T2;

GO

I then created the Staging table. One column, no indexes, no constraints.

Transact-SQL

-- The Staging table CREATE TABLE dbo.StgTable ( Value INT NOT NULL ); GO

1

2

3

4

5

6

7

-- The Staging table

CREATE TABLE

dbo.StgTable

(

Value INT NOT NULL

);

GO

For the test itself I used three procedures, all doing exactly the same:

  1. Populating the Staging / Temp / Variable table with a certain amount of records.

  2. For the simple procedure – Count all of the values that are divided by 10.