Table valued parameters (TVP) are a great feature that solves the problem of passing a table of values into a stored procedure. In the past problems like this would get solved by complicated approaches such as passing and parsing XML, or passing long streams of dynamically generated SQL. There is a caveat you should be aware of with regards to performance when using TVPs. In this post I’ll describe what this is and how you can easily avoid problems as a result of this.
How can using TVPs negatively affect my performance?
This is pretty simple – they have no or inaccurate statistics. As a result the optimizer has difficulty coming up with an efficient execution plan.
How do I get around this?
Simple. Insert the values into a #temp table prior to using them. The overhead of adding an extra step involving a #temp table will likely outweigh the problems that joining to a TVP can cause. Keep in mind I’m suggesting using a #temp table rather than an @table variable. I have another post here explaining why.
I see problems caused by using TVP’s in joins and merges on a pretty regular basis (there are cases where this causes issues with spools on inserts as well). When I suggest to the developers they add an extra step which involves a #temp table they are usually skeptics; however, in almost every case I’ve suggested this there has been a pretty positive impact. Good luck tuning!
See how SQLGrease can quickly get you to the bottom of a query timeout error.