Problem
SQL Server memory optimized table variable and a temporary table: Which is faster to temporarily load and store data?
Dear readers, I want to share a true story. In 2018, when I was using SQL Server 2016 in production, I had a complex stored procedure where I needed to store data temporarily. I can’t remember exactly, but I needed to store, say, 50,000 rows temporarily. At that time, I thought memory-optimized table variables were extremely fast for storing temporary data. So, I used them. There was not a lot of concurrency on that stored procedure. However, after a while, I realized that it was not fast enough. I investigated to find the root of the issue and found that loading data into the Memory-Optimized table variable was causing the problem.
Solution
Sometimes we need to temporarily store query results. To do this, there are three common solutions:
- Memory-optimized table variables
- Temporary tables
- Table variables
When you are temporarily storing data for some purpose, three parameters affect your query performance:
- Latch contention on tempdb
- Concurrency
- Parallelism
In this tip, I will ignore the explanations of latch contention on tempdb and concurrency, and focus only on the effect of parallelism. The example in this article discusses how parallelism improves the performance of the query.
Set Up Test Environment
As always, I use the StackOverflow database for our examples. The database compatibility level is set to 160, but changing it does not affect the example in this article. The StackOverflow database is an open-source database from StackOverflow.com. To use memory-optimized table variables in our example, we need to add a memory-optimized file and filegroup to the database:
Use master
GO
Alter Database [StackOverflow] Add FileGroup FG_SOF_inm Contains Memory_Optimized_Data
GO
Alter Database [StackOverflow] Add File (Name = F_SOF_inm, FileName = N'D:\DataBases\Data\StackOverflow\') To FileGroup FG_SOF_inm
GOA memory-optimized table variable provides great efficiency by using the same memory-optimized algorithm and data structures also used by memory-optimized tables. The efficiency is maximized when the table variable is accessed from within a natively compiled module. For more information about memory-optimized table variables, view this article: Faster temp table and table variable by using memory optimization.
Memory Optimized Table Approach
I’m setting up a script to simulate the scenario I faced. Since we cannot declare memory-optimized table variables inline, we must create a table type in advance. The following script declares a memory-optimized table variable and loads information about users who live in India into it (I skipped the AboutMe column). Before running the script, I simply press Ctrl+M to view the actual execution plan.
Use StackOverflow
GO
Create Type Users AS Table
(
[Id] [int] NOT NULL,
[Age] [int] NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[EmailHash] [nvarchar](40) NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[WebsiteUrl] [nvarchar](200) NULL,
[AccountId] [int] NULL,
Primary Key NonClustered (Id)
)
With (Memory_optimized = On)
GO
Declare @Users Users;
Insert Into @Users
(
Id,
Age,
CreationDate,
DisplayName,
DownVotes,
EmailHash,
LastAccessDate,
Location,
Reputation,
UpVotes,
Views,
WebsiteUrl,
AccountId
)
Select
Id,
Age,
CreationDate,
DisplayName,
DownVotes,
EmailHash,
LastAccessDate,
Location,
Reputation,
UpVotes,
Views,
WebsiteUrl,
AccountId
From dbo.Users u Where u.Location = N'India'
GOThe plan shows that SQL Server scanned the whole table to fetch information about users who live in India:
Creating a nonclustered index on the Location column does not improve query performance because the query optimizer ignores it. The query execution time and CPU time are the same, which shows that the query ran in serial execution mode. This query took nearly 6 seconds to execute:
Temporary Table Approach
I repeat: in the scenario I faced, there was not a lot of concurrency. Up to three sessions were executing the stored procedure simultaneously. In the future, I will write an article to explain why I emphasized that there was no concurrency. I’m going to replace the Memory-Optimized table variable with a temporary table using the following code:
Drop Table If Exists #Users
GO
Create Table #Users
(
[Id] [int] NOT NULL,
[Age] [int] NULL,
[CreationDate] [datetime] NOT NULL,
[DisplayName] [nvarchar](40) NOT NULL,
[DownVotes] [int] NOT NULL,
[EmailHash] [nvarchar](40) NULL,
[LastAccessDate] [datetime] NOT NULL,
[Location] [nvarchar](100) NULL,
[Reputation] [int] NOT NULL,
[UpVotes] [int] NOT NULL,
[Views] [int] NOT NULL,
[WebsiteUrl] [nvarchar](200) NULL,
[AccountId] [int] NULL
)
GO
Insert Into #Users
(
Id,
Age,
CreationDate,
DisplayName,
DownVotes,
EmailHash,
LastAccessDate,
Location,
Reputation,
UpVotes,
Views,
WebsiteUrl,
AccountId
)
Select
Id,
Age,
CreationDate,
DisplayName,
DownVotes,
EmailHash,
LastAccessDate,
Location,
Reputation,
UpVotes,
Views,
WebsiteUrl,
AccountId
From dbo.Users u Where u.Location = N'India'
GOSQL Server scanned the entire table as before, but executed the query using four CPU cores, and you can see the parallelism icon in the execution plan:
The CPU time is four times the query elapsed time, which shows that SQL Server executed the query in parallel execution mode:
Query execution time was reduced to 817 milliseconds from nearly 6 seconds. A great improvement!
Summary
You are loading large amounts of data, say 50,000 rows, into a Memory-Optimized table variable, and your query runs slowly because the query optimizer executes it in serial execution mode. In such a scenario, to increase the speed of query execution, replace the Memory-Optimized table variable with a temporary table.
Next Steps
