Hi Everyone, Well last week I encountered a case where we were using table variable inside stored procedure to hold maximum of about 110 rows. We were experiencing some performance issue with this. On investigating the dba suggested (with some third party optimizing tool other than ssms execution plans) to replace the table variable by temp table. On executing the stored procedure again, the performance increased by 4 times. I ve learned that table variables are faster than temp tables. But this was a different one. Can anybody suggest what can be reason behind this? Thanks
Without seeing the execution plan or the query, it's hard to say what's going on for certain. But, I'm not at all surprised that even with only a few rows that a temporary table might be faster than a table variable. The main difference between the two is that table variables do not have statistics. Depending on what you're doing with the table variable, either joins or filtering is likely to be much faster with a temporary table. Neither type of object is faster than the other. Table variables have no statistics, so, in situations where statistics are not needed, they tend to be faster. Also, because they don't have statistics, in situations where recompiles occur due to statistics, they tend to be faster. Temporary tables have statistics. If you need statistics in the query, then temporary tables will tend to be faster. Both table variables and temporary tables write to tempdb. Both use memory. The primary difference is the statistics.
Henrik Staun Poulsen
That's odd.. I don't think swapping from a table variable temp table would increase the performance by a factor of four if it only had 110 rows. Was anything else changed? I'd clear the cache or run up a new instance of ssms too when testing. Haw many passes through the table object? I.e. how many rows in your output..? I'd also try with a permanent table outside of a stored procedure just as a query then just drop it see if performance changes.. i.e. select a, b, b into test_table from your_table ... do stuff ... drop table test_table