SQL Server Performance Issues using SQL JOIN with a Temporary Table vs a Table Variable

By:   |   Updated: 2024-01-04   |   Comments (2)   |   Related: More > Performance Tuning


Problem

Occasionally, it may be necessary to utilize a table variable in the SQL JOIN operation. When employing a table variable in a join, inaccurate cardinality estimation may result in suboptimal query performance. If the estimated number of rows is inaccurate, it could lead to an incorrect memory grant and cause a spill to disk. Inaccurate cardinality estimations can cause a query to run in serial mode instead of parallel and vice versa therefore impacting performance.

Solution

SQL Server generates an execution plan based on statistics, which table variables lack. From database compatibility level 150, table variable performance is improved by introducing table variable deferred compilation. This feature improves overall performance for queries that involve table variables.

With table variable deferred compilation, the compilation of a statement that references a table variable is deferred until the first actual execution of the statement. However, table variable deferred compilation doesn't change any other characteristics of table variables. For example, this feature doesn't add column statistics to table variables. It is important to note that cardinality includes both the number of rows and their respective contents. It is also worth noting that some people may choose to use table variables as a replacement for temporary tables under the misconception that table variables are stored in memory and do not use tempdb, making them more efficient than temporary tables. However, both objects rely on tempdb.

This article will illustrate how using a temporary table instead of a table variable can improve query performance.

SQL Server Performance Test with a Temporary Table instead of a Table Variable

I will use SQL Server 2022 and StackOverflow database for the examples. StackOverflow database is an open-source database from Stack Overflow.com.

Set the database compatibility level to 160:

Use master
GO

Alter Database StackOverflow Set Compatibility_Level = 160
GO

Create an index on the Location column in the Users table:

Use StackOverflow
GO

Create Index IX_Location On dbo.Users (Location) With (Data_Compression = Page)
GO

The query below creates a table variable, inserts two locations, and then performs a join on the Users table, sorting the results by Reputation in descending order.

Declare @Location Table (Location Nvarchar(100));
GO

Insert Into @Location Values (N'France'), (N'USA');
GO

Select u.* 
From dbo.Users u
Inner Join @Location l On u.Location = l.Location
Order By Reputation Desc;
GO

To view the actual execution plan, press Ctrl + M.

TableVariablePlan

Below is a subset with focus on the table scan operation.

TableVariableScan

The estimated and actual number of rows for the table scan operation is two.

Let's focus on the index seek operation in the execution plan. Upon examining the index seek operation, it is evident that the estimated number of rows is 129, while the actual number of rows is 24574. Indeed, the significant difference between the estimated and actual number of rows in the index seek operation indicates an underestimation problem.

If the estimates prove wrong during execution, the plan must continue despite insufficient memory. In such a case, a spill to disk occurs. When a spill occurs, the data is written to tempdb and must be read from there. As depicted in the image below, the underestimation problem resulted in a spill to disk on the sort operator.

SpillToDisk

Refer to the following image for information regarding the memory grant.

MemoryGrantInfoTableVariable

As indicated in the image, the granted memory for the operation is 2.7 MB. Let's take a look at the following image.

TableVariableCPU

According to the above image, SQL Server executed the query using a single logical CPU core, indicating that the query was executed in serial mode. It seems that the underestimation problem has led to several challenges.

To address the issues caused by the underestimation problem, a straightforward solution is to replace the table variable with a temporary table. The temporary table has been used to replace the table variable in the subsequent query:

Create Table #Location (Location Nvarchar (100) Collate SQL_Latin1_General_CP1_CI_AS)
GO

Insert Into #Location Values (N'France'), (N'USA')
GO

Select u.* 
From dbo.Users u
Inner Join #Location l On u.Location = l.Location
Order By Reputation Desc
GO

Below is the actual execution plan of the query.

TemporaryTablePlan

Note the index seek operator in the image, where the estimated number of rows is approximately 14000, while the actual number of rows is approximately 24500. When the query utilized a table variable, the estimated number of rows was only 129. The image below shows that there is no warning on the sort operator.

TemporaryTable-SortOperator

The granted memory has increased to 38 MB.

MemoryGrantInfoTemporaryTable

The following image illustrates that SQL Server executed the query using two logical CPU cores.

TemporaryTableCPU

It seems that all the problems have been resolved. In most cases, temporary tables are safer and more reliable than table variables. However, it is important to note that this solution may not always resolve the problem. For more comprehensive information, refer to this article, Tune SQL Server Query when Estimated Number of Rows is Incorrect.

Cardinality estimation for table variables can be affected by parameter sniffing. It's important to note that the performance characteristics of table variables can vary depending on the specific scenario and workload. Performance testing and benchmarking are recommended in your specific environment to evaluate the impact of table variables on your queries and make informed decisions based on the results.

Summary

Sometimes, it is necessary to use a table variable in the Join. However, poor cardinality estimation can lead to performance problems when doing so. Cardinality includes both the number of rows and their respective contents. To address the issues caused by the poor cardinality estimation, a straightforward solution is to replace the table variable with a temporary table.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Mehdi Ghapanvari Mehdi Ghapanvari is an SQL Server database administrator with 6+ years of experience. His main area of expertise is improving database performance. He is skilled at managing high-performance servers that can handle several terabytes of data and hundreds of queries per second, ensuring their availability and reliability.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-01-04

Comments For This Article




Saturday, January 6, 2024 - 1:25:08 AM - Mehdi Ghapanvari Back To Top (91836)
Writing the query as shown will result in the same performance.

;With Tbl (Id) As (Select Id From dbo.Users u Where Location In( N'France', N'USA'))
Select u.*
From dbo.Users u
Inner Join Tbl t
On u.Id = t.id
Order By u.Reputation Desc

However, temporary tables generally have a more accurate cardinality estimation than common table expressions (CTEs).

Friday, January 5, 2024 - 6:12:49 AM - Gilian van den Heever Back To Top (91831)
What about a CTE in this instance ?