Recently, I am trying to optimize some old stored procedures. In the
process, I found out in the online help that a "table variable" is
supposed to perform better than temp tables. However, I have found
exactly the opposite. Whereas using temp tables, I can obtain query
results in less than one second. Using a table variable took me more
than 20 seconds!
For the comparison, the stored procedures are almost the same. I've just
replaced CREATE TABLE #temp (...) with DECLARE temp TABLE (...). It
consists of only an insert to the table followed by a join select ...