Thursday, August 14, 2008

Temp tables vs. Variable tables in SQL

I've had an interesting situation arise at a client site. The client was using variable tables within a proc as a temporary mechanism to store data before returning results back to the user. The problem was that these queries took more than 30 seconds to complete which is unsatisfactory to say the least.

As an experiment we converted the temp tables and the execution time was sub 3 second. While still not ideal the performance increase was a bit jaw dropping. Keep in mind we just change the @ table definition to a #table definition to get the performance decrease. Of course we had to add a statement at the end of the proc to drop that table but the users are happy and the DBA hasn't seen any decreased performance on the server. Small changes FTW.

No comments: