Poor performance of SQL query with Table Variable or User Defined Type

Question

I have a SELECT query on a view, that contains 500.000+ rows. Let's keep it simple:

SELECT * FROM dbo.Document WHERE MemberID = 578310

The query runs fast, ~0s

Let's rewrite it to work with the set of values, which reflects my needs more:

SELECT * FROM dbo.Document WHERE MemberID IN (578310)

This is same fast, ~0s

But now, the set is of IDs needs to be variable; let's define it as:

    DECLARE @AuthorizedMembers TABLE
    (
        MemberID BIGINT NOT NULL PRIMARY KEY,    --primary key 
        UNIQUE NONCLUSTERED (MemberID) -- and index, as if it could help...
    );

    INSERT INTO @AuthorizedMembers SELECT 578310

The set contains the same, one value but is a table variable now. The performance of such query drops to 2s, and in more complicated ones go as high as 25s and more, while with a fixed id it stays around ~0s.

SELECT * 
FROM dbo.Document 
WHERE MemberID IN (SELECT MemberID FROM @AuthorizedMembers)

is the same bad as:

SELECT * 
FROM dbo.Document 
WHERE EXISTS (SELECT MemberID 
              FROM @AuthorizedMembers 
              WHERE [@AuthorizedMembers].MemberID = Document.MemberID)

or as bad as this:

SELECT * 
FROM dbo.Document 
INNER JOIN @AuthorizedMembers AS AM ON AM.MemberID = Document.MemberID

The performance is same for all the above and always much worse than the one with a fixed value.

The dynamic SQL comes with help easily, so creating an nvarchar like (id1,id2,id3) and building a fixed query with it keeps my query times ~0s. But I would like to avoid using Dynamic SQL as much as possible and if I do, I would like to keep it always the same string, regardless the values (using parameters - which above method does not allow).

Any ideas how to get the performance of the table variable similar to a fixed array of values or avoid building a different dynamic SQL code for each run?

P.S. I have tried the above with a user defined type with same results

Edit: The results with a temporary table, defined as:

CREATE TABLE #AuthorizedMembers
    (
        MemberID BIGINT NOT NULL PRIMARY KEY
    );

    INSERT INTO #AuthorizedMembers SELECT 578310

have improved the execution time up to 3 times. (13s -> 4s). Which is still significantly higher than dynamic SQL <1s.

Answer 1

Your options:

• Use a temporary table instead of a TABLE variable
• If you insist on using a TABLE variable, add OPTION(RECOMPILE) at the end of your query

Explanation:

When the compiler compiles your statement, the TABLE variable has no rows in it and therefore doesn't have the proper cardinalities. This results in an inefficient execution plan. OPTION(RECOMPILE) forces the statement to be recompiled when it is run. At that point the TABLE variable has rows in it and the compiler has better cardinalities to produce an execution plan.

The general rule of thumb is to use temporary tables when operating on large datasets and table variables for small datasets with frequent updates. Personally I only very rarely use TABLE variables because they generally perform poorly.

I can recommend this answer on the question "What's the difference between temporary tables and table variables in SQL Server?" if you want an in-depth analysis on the differences.