Monday, 5 March 2012

Quick win - check your table variable use

Quick wins are awesome. Making a change that takes minimal effort and yields a significant performance improvement is very satisfying.

This particular potential quick win relates to the use of table variables vs. temporary tables. Have a non-trivial stored procedure that produces some intermediary results and stores in a table variable which then goes on to be used further in the stored procedure? Consider evaluating a switch to a temporary table instead. This very quick change, can give a great performance boost for such little effort due to the points outlined on MSDN (quote):

table variables are not supported in the SQL Server optimizer's cost-based reasoning model. Therefore, they should not be used when cost-based choices are required to achieve an efficient query plan. Temporary tables are preferred when cost-based choices are required. This typically includes queries with joins, parallelism decisions, and index selection choices.
Queries that modify table variables do not generate parallel query execution plans. Performance can be affected when very large table variables, or table variables in complex queries, are modified. In these situations, consider using temporary tables instead......Queries that read table variables without modifying them can still be parallelized.
Indexes cannot be created explicitly on table variables, and no statistics are kept on table variables. In some cases, performance may improve by using temporary tables instead, which support indexes and statistics.
I've seen cases where this alone, with no other modifications, results in noticeable better performance. This could then be built on further by adding supporting indices. Of course, you should be considering whether you need temporary tables/table variables at all in your stored procedure. If you can remove the need entirely, then look to do that.

Further reading:


  1. "I've seen cases where this alone, with no other modifications, results in noticeable better performance."

    Got any stats for that?

  2. Hi Jamie,
    In the scenario I was looking at (can't post the full details of the sproc unfortunately), where I changed 3 table vars to temp tables, I saw:

    Cpu drop by ~22.5% (4398 vs 6583)
    Reads drop by ~17% (350353 vs 421039)
    Duration drop by ~13.5% (5705 vs 6583)

    In this case, the table vars were inserted into/updated/selected from in a few places down through the sproc.

  3. "Indexes cannot be created explicitly on table variables". The "Explicitly" verbage is interesting. You can create an index on a table variable if you make a column (or columns) a primary key. This can add processing time when inserting in to the table variable, but improve processing time when using it later.

    I'm not saying one is better than another. The only real way to know is to check it both ways.

  4. @George - Thanks for the comment. Yes, or alternatively you can also define UNIQUE constraints at time of declaration in table vars. You make a good point - check it both ways. I was trying not to come across as saying "temporary tables are always better than table variables", as I completely agree that it's something you should check for your scenario.