Skip to content
Home » Look, Ma, No Surprises

Look, Ma, No Surprises

  • by

Last week I demonstrated at least 30% performance improvement by switching to memory optimised table-valued parameters on SQL Server 2016.

This week I will demonstrate the same test using Azure SQL Database, on the Premium tier, where In-Memory OLTP is supported.

My test harness is the same. I will create a temp table and a table variable:

-- Temp table creation
CREATE TABLE #temptable
(ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

-- Table variable creation
DECLARE @tablevariable AS TABLE
(ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

Now I will create a standard table-valued parameter:

CREATE TYPE dbo.TVPStandard AS TABLE
(ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY);

DECLARE @TempTVPStandard AS dbo.TVPStandard;

Finally, I will create a memory-optimized table-valued parameter (there is no requirement to separately enable In-Memory OLTP in Azure SQL Database):

CREATE TYPE dbo.TVPInMemory AS TABLE
(ID UNIQUEIDENTIFIER NOT NULL PRIMARY KEY NONCLUSTERED)
WITH (MEMORY_OPTIMIZED = ON);

DECLARE @TempTVPMemory AS dbo.TVPInMemory;

So far, nothing is different from last week’s scenario. We now have the following structures at our disposal:

  • #temptable (a temp table)
  • @tablevariable (a table variable)
  • @TempTVPStandard (a standard TVP)
  • @TempTVPMemory (a memory-optimized TVP)

I’m going to use the same WHILE loop again, but instead of a million runs, I’ll do 1000, 10,000 and 100,000, because I’m paying for this instance of Azure SQL Database (I picked a Premium P1, with 125 DTUs) and I’m a cheapskate. I doubt the 125 DTUs is even enough to run a million times for the fourth option.

PRINT SYSUTCDATETIME();

SET NOCOUNT ON;

DECLARE @i INT = 0;
WHILE @i < <number of executions>
BEGIN
INSERT INTO <object name>
SELECT NEWID();
SELECT @i = @i + 1;
END;

SET NOCOUNT OFF;

PRINT SYSUTCDATETIME();

Results!

Like last week, at low row counts all four data structures performed around the same speed. Tempdb is shared with other Azure SQL Database customers, so I expected to see that slower.

I ran each series three times and took the lowest value from each run.

Data Structure 1,000 10,000 100,000 1,000,000
Temp Table (Clustered) 94 ms 453 ms 4,266 ms 44,955 ms
Table Variable (Clustered) 93 ms 344 ms 3,484 ms 34,673 ms
Standard TVP (Clustered) 94 ms 343 ms 3,500 ms 34,610 ms
Memory-Optimized TVP
(Non-Clustered)		 78 ms 203 ms 1,797 ms No Time

Unsurprisingly, because Azure SQL Databases share tempdb with other customers, the IO-bound figures are higher than those on my dedicated laptop, no matter how old it is.

The big winner here, again, is the memory-optimized table-valued parameter. More than twice as fast as a temp table, and almost twice as fast as a table variable or standard TVP.

Note, however, that because my Azure SQL Database was resource-constrained, I was unable to run the one-million step WHILE loop.

This is an excellent example of how In-Memory OLTP is a trade-off, no matter whether you’re running on premises or in the cloud. While you do consistently get much better performance, it is not reliable for large data structures.

If you have any In-Memory OLTP tweaks you’d like to share, find me on Twitter at @bornsql.

Tags: