How to write performance queries! Take advantage of the Temporary Table Caching

Hi Guys,

I don’t usually talk about numbers but I have to say that almost 8000 clicks in just one month on my blog is really a huge amount! So, thanks to the 7000 k friends who follow me!

In the last post (click here) we saw in detail how the tempDB database works.

Today we see how to use what we learned to write in temporary tables as quickly as possible.
In this way you can put your hand to your procedures to make them run ...and for free!

Let’s see how to exploit the temporary table cache mechanism!

Enjoy!

Temporary Table Caching

In the previous post we saw that there are some rules to take advantage of the temporary table caching.

We said that in the following cases table are not cached:

Named constraints are not created
DDL statements that affect the table are not run after the temporary table has been created. for example the CREATE INDEX or CREATE STATISTICS statements
Temp table is not created by using dynamic SQL
Temp table is created inside another object, such as a stored procedure or trigger

For example, the second point I see often happens.

But it’s a shame because we can go faster with a simple change.

You may ask, but is it really important?
Let’s see it doing some tests

Some tests: Cached Vs. not Cached!

For this test we use the latest release candidate (RC0) of SQL Server 2022, reviewed here

We will use the RMLUtils to run some benchmarks.

We will compare two stored procedures, one that does not take advantage of the table cache and another that instead benefits.

The first stored procedure is the following.

This stored procedure drop and recreate a temporary table.

After the creation of the table #temp_table a clustered index is added.

       
CREATE PROCEDURE [dbo].[temp_table_no_cache]
AS
BEGIN

  IF OBJECT_ID('TEMPDB..#temp_table','U') IS NOT NULL
    DROP TABLE #temp_table

  CREATE TABLE #temp_table (Id INT IDENTITY(1,1), code varchar(20), descr varchar(80))

  CREATE UNIQUE CLUSTERED INDEX idx_temp_table_ ON #temp_table (ID)

END

Why this stored procedure does not take advantage of the table cache feature?

This is because a DDL statements that affect the table is executed after the creation of the temporary table.

Now we write a second stored procedure.

This time i add the PRIMARY KEY constraint in the definition. Rembember that creating a primary key automatically creates a corresponding unique clustered index.

       
CREATE PROCEDURE [dbo].[temp_table_cache]
AS
BEGIN

  IF OBJECT_ID('TEMPDB..#temp_table','U') IS NOT NULL
    DROP TABLE #temp_table

  CREATE TABLE #temp_table (Id INT IDENTITY(1,1) PRIMARY KEY, code varchar(20), descr varchar(80))

END

To compare the execution time of the two stored procedures I will run them through the tool RMLUtils.

This tool provides an executable called Ostress.exe that is able to run the same command several times simultaneously on n thread.

This is the syntax of the OStress.exe command:

       
OStress.exe -i<file.sql> -U<xx> -P<xxxxxx> -S<zzzzz> -d<prove< -oc:\dir -n<int> -r<int>

where

the -i parameter is the name of a SQL file that contain the commands to execute (my file file contain the exec temp_table_cache or exec temp_table_nocache command)
the -U parameter is the username
the -P parameter is the password
the -d parameter is the database name
the -o parameter is a path where the log files will be written
the -n parameter is the number of thread to run
the -r parameter is Represents how many times the given commands are executed