Why are temporary tables faster than table variables for joins?

Question

Why are temporary tables faster than table variables for joins in SQL Server?

NOTE: In both scenarios the tables have PK, and the tables are joined with other "physical" tables through the PK.

Solution 1

Both are persisted in Tempdb; however, performance problems come into play because the optimizer does not maintain statistics on table variables. This is problematic because the optimizer is ALWAYS going to assume there is 1 row in your table variable. Obviously this can really screw up a query plan, especially when there are lot of rows in your table variable. I wouldn't use a table variable to store anything more than a 1000 or so rows; otherwise, the performance could be unpredictable.

Solution 2

Temp tables are similar to tables but they are store in tempdb when created, which means that optimizer can create statistics on them,while table varaibles as similar to variables and there are no statistics on them.Usualy when comparing tmp tables vs table variables,the temp tables come out on top. The reason is that the query optimizer will sometimes generate poor plans for table vars. This can mostly be seen when there is a lot of data. The biggest point I can make is that table variables are more likely to cause unpredictable execution plans when compared to the plans generated for temp tables. Temp tables on the other hand, will cause more recompilation.

Comments

[Martin Smith]

There are no statistics maintained for table variables but row count is. The optimizer generally assumes the table variable cardinality is 0 rows because the statement is compiled before the table variable is populated. If the statement is subject to recompile it can use the actual row count.

[Martin Smith]

Example of this in the cardinality section of my answer here