Testing performance of table variable vs. temp variable

[sds814]

I'm enhancing a SP that is using 4 or 5 temp tables to store data. At the end a select query is done joining the temp tables. I wanted to see if the performance of the SP would improve if I used table variables with primary keys. What would be a good way to test the performance between the two?

I was thinking of doing an execution plan and seeing the time it takes to load the data into the tables and then on the select query.

Thanks for your help!

 

[Qik3Coder]

sds814,

Besides simply adding PKs to the temp tables is to better understand your data and how much you have.
Table variables are going to perform faster than temp tables with smaller data sets, but there is a memory/CPU hit for the speed.

In order to compare your results you're going to need to create a variant on the procedure with your changes, then run them both several times.
I've created temp tables and table variables with keys, loaded with data, then actually regenerated the statistics on the tables before doing my select.

Lodlaiden

You've got questions and source code. We want both!
There's a whole lot of Irish in that one.