I am trying to convert a stored procedure to a table valued function and the performance has taken a HUGE hit and I was wondering if there was anything that can be done about it. Since a table valued function can not use #temp tables it must be converted
to a @temp table variable.
Here are some steps I have already taken...
The original stored proc starts off by populating a #temp table via "Select x Into #temp ..."
Leaving it a stored proc for now, I explicitly created the #temp table and did an "Insert Into ... Select From" to more closely model how it must work when using a @temp table variable. There was no discernible performance difference.
Still leaving it as a stored proc, I then swapped out the #temp table with the @temp table variable and now, all of the sudden, the performance drops from sub-second to over a minute!!!
The temp table only has one field defined as an int and it is distinct, so I tried making the field the Primary Key to see if that would help and it did not.
The temp table is created by scanning a table with around 11,000 rows and the temp table itself has about 4400 rows in it (if it makes a difference to anyone).
Does anyone have any suggestions (or hope) for me?
View Complete Post