SP performance problem with table variables | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

SP performance problem with table variables

We had a stored procedure using only table variables that gave an interesting and intermittent performance problem. I finally think I found out what was causing this. It essentially went like this: Restore database
Run SP with parameter set 1: Takes 10 seconds
Run SP with parameter set 2: Takes 5 seconds Restore database
Run SP with parameter set 2: Takes 10 seconds
Run SP with parameter set 1: Takes 11 minutes!!! Reproduced on both SQL Server 2000 and SQL Server 2005. The problem was with the first query within the stored procedure joining 4 large tables which for parameter set 1 returned 318 records and for parameter set 2 returned 1 record. It seems that when the stored procedure is compiled first time it is also saved with query plans for all queries based on the parameters it is called with. Adding WITH RECOMPILE to the procedure declaration has fixed the problem and it now takes less than 10 seconds in all cases. Also explains why our customer was complaining about wildly varying performance on different days as it depended on which version of the query plan they got for that day after restarting their database overnight! The SP only uses table variables which is why it theoretically should not need to be recompiled at all, but the optimiser seems to be missing a trick. Is there a way to force the optimiser to always optimise a query without recompiling the whole stored procedure every time? Regards,
Axel
You are seeing a side-effect of "parameter sniffing", which was supposed to have a performance benefit. The common work around for that is to add local variables to your SP. You pass the parameter values into the local variables, and use only the local variables inside the SP.
]]>