SQL Server Performance Issues using SQL JOIN with a Temporary Table vs a Table Variable
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.
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.
Below is a subset with focus on the table scan operation.
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.
Refer to the following image for information regarding the memory grant.
As indicated in the image, the granted memory for the operation is 2.7 MB. Let's take a look at the following image.
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.
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.
The granted memory has increased to 38 MB.
The following image illustrates that SQL Server executed the query using two logical CPU cores.
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.
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.
- SQL Server Table Variable Example
- Differences between SQL Server temporary tables and table variables
- Table Variable Deferred Compilation in SQL Server
About the author
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