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.

Euxenite

Try changing your table variable to a temporary table CREATE TABLE #AuthorizedMembers It can improve performance tremendously in some situations, depending on amount of data. – Innocent

what about INNER JOIN performance instead of LEFT JOIN + IS NOT NULL ? – Instruct

I used left join as in the more generic case that's what i need, for this simplified case inner would indeed be better, but the performance is the same apparently, I guess the final optimized query is same (I updated the question) – Euxenite

for the temporary table, I would prefer to avoid this solution, but I'll reconsider it if nothing else is left, the recompile option doesnt seem to work for me, but I'm doing more tests now – Euxenite

Why does MemberID need to be BIGINT? – Present

Sorry, seems like a misdirected question. Wouldn't you use a Table Valued Parameter instead of a Table Variable or Temporary Table? – Leyes

Be sure to include the Actual Execution Plan to be able to analyze the performance problem. – Omaomaha

Frisbee, it does need to be a BIGINT in production :) And well, it's not the issue here. – Euxenite

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 can result in an inefficient execution plan.

OPTION(RECOMPILE) forces the statement to be recompiled right before it is run. At that point, the TABLE variable has rows in it and the compiler has proper cardinalities to produce a more efficient 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.

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.

Omaomaha

thanks, this is useful, where would you put the option(recompile) in the queries above, I cannot make it work (the performance is same) – Euxenite

@Euxenite add OPTION(RECOMPILE) at the end of your query – Omaomaha

@Euxenite If that doesn't improve execution time, switch to temporary tables. Provide the temporary table with an INDEX if your query would benefit from it. It's also possible that your query lacks proper indices... check out the execution plan to see if any tables are missing proper indices for your query. – Omaomaha

the performance is exactly the same with and without the OPTION(RECOMPILE), although it seems to be the right direction. I am going to work on the performance of the view itself, but for this particular case, replacing a line with table variable with a fixed set, even bigger, changes the executoion time from >26s to <1s. It really looks liike the TV is the issue. And dynamic SQL seems to be a better option to me than the temporary table anyway. – Euxenite

@Euxenite "And dynamic SQL seems to be a better option to me than the temporary table anyway." > That is not true. Dynamic SQL is to avoided when possible, while using temporary tables is common practice in a lot of scenarios. I have no clue why you would be against temporary tables when you initially started with a TABLE variable of which it is known that it produces bad execution plans. – Omaomaha

in fact dynamic SQL is most likely unavoidable in this particular scenario anyway; However, I wanted to keep it fairly static, only passing variables. But also I might have got wrong impression on the temporary tables, I'll give it a go. – Euxenite

@Euxenite Good luck! Let us know what your findings are. – Omaomaha

So using the temporary table, indeed made an improvement, the execution time dropped 3 times comparing to the table variables, but still much higher than the dynamic SQL, I'll update the question – Euxenite