Custom Table Type Performance in SQL Server

Question

We used Custom Table Types to pass a list of integers into our stored procedures.

Then we use them to join other tables in our stored proc queries.

For instance:

CREATE PROCEDURE [dbo].[sp_Name] ( @Ids [dbo].[OurTableType] READONLY ) AS SET Nocount ON SELECT * FROM SOMETABLE INNER JOIN @Ids [OurTableType] ON [OurTableType].Id = SOMETABLE.Id 

We have seen very poor performance when using large data sets.

One approach we used to speed things up is dumping the contents into a temporary table and merging instead.

For instance:

 CREATE PROCEDURE [dbo].[sp_Name] ( @Ids [dbo].[OurTableType] READONLY ) AS SET Nocount ON CREATE TABLE #TempTable(Id INT) INSERT INTO #TempTable SELECT Id from @Ids SELECT * FROM SOMETABLE INNER JOIN #TempTable ON #TempTable.Id = SOMETABLE.Id DROP TABLE #TempTable 

This greatly improves performance, but I wanted to get some opinions on this approach and any other consequences that we did not consider. It may also be useful to explain why this improves performance.

NB Sometimes we may need to pass in more than just an integer, so we don’t use a comma-separated list or something like that.

Answer 1

This topic has been discussed before. The main reason for poor JOIN performance is that a table value parameter (TVP) is a table variable. Variable tables do not store statistics and, apparently, the query optimizer contains only 1 row. Therefore, they are just able to do something like INSERT INTO Table (column_list) SELECT column_list FROM @TVP; but DON'T JOIN.

There are several things to try to get around this:

1. Dump everything to a local temporary table (you already do this). The technical drawback here is that you duplicate the data transmitted to the TVP in the tempdb database (where both the TVP and the temporary table store their data).

2. Maybe try defining a custom table type to have a clustered primary key. You can make it embedded in the [Id] field:

    [ID] INT NOT NULL PRIMARY KEY 

   Not sure how much this helps performance, but worth a try.

3. You can try adding OPTION (RECOMPILE) to the query. This is a way to get the Query Optimizer to see how many rows are in a table variable in order to have the correct estimates.

    SELECT column_list FROM SOMETABLE INNER JOIN @Ids [OurTableType] ON [OurTableType].Id = SOMETABLE.Id OPTION (RECOMPILE); 

   The downside is that you have a RECOMPILE which takes extra time each time this process is called. But it may be total net profit.

4. Starting with SQL Server 2014, you can use OLTP in memory and specify WITH (MEMORY_OPTIMIZED = ON) for a custom table type. Please see Scenario: the table variable may be MEMORY_OPTIMIZED = ON for details. I heard this definitely helps. Unfortunately, in SQL Server 2014 and SQL Server 2016 RTM, this feature is only available in the 64-bit version of Enterprise Edition. But, starting with SQL Server 2016 Service Pack 1 (SP1), this feature has been available for all releases (a possible exception is SQL Server Express LocalDB).

5. SQL Server 2019 introduces " deferred compilation of table variables ":

   With delayed compilation of table variables, compiling a statement that refers to a table variable is delayed until the first actual statement execution. This delayed compilation behavior is identical to the behavior of temporary tables. This change leads to the use of the actual number of elements instead of the original single-line guess.

   Please see the related documentation for details.

PS. Do not do SELECT * . Always list columns. Unless you do something like IF EXIST(SELECT * FROM)...