0

Is there a general performance difference in SQL Server 2012 passing in a table valued parameter versus just passing in a fixed set of individual scalar-valued parameters into a stored procedure?

Also, does passing in the same individual set of scalar-valued parameters reduce recompilation of your stored procedure versus passing in a table parameter that might have different number of row values within?

I am working on trying to reduce recompilation of some of my lengthiest stored procedures (3000+ lines which unfortunately I cannot share) and I notice that many of them have a tabled value parameter being passed as well as other potential "issues" that can be improved upon including including creation of multiple temp tables, indexes being created for those temp tables within the stored procedure, missing indexes on the main tables, and conditional logic which may or may not run a part of the stored procedure.

Thank you for your time.

EDIT: Clarified fixed set of scalar-valued based on comments.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
rjt011000
  • 112
  • 10
  • 2
    Do you mean you are considering replacement of a single table-valued parameter, which allows you to pass an indefinite number of values of the same type, with a fixed number of scalar-valued parameters of the same type? – Andriy M Oct 06 '14 at 19:01
  • @AndriyM: I am considering that as a solution yes. – rjt011000 Oct 06 '14 at 19:03
  • 3
    Don't even consider it. TVPs will outperform individual parameters and be much more scalable and flexible too (let's say you decide you want to support 100 parameters, then later change to 200, then pointy-haired boss wants 500 parameters - have fun maintaining that stored procedure definition). I really doubt the TVPs are the issue - this is like saying all the cars on the highway are slow, it must be the wheels - because they all have wheels! – Aaron Bertrand Oct 06 '14 at 19:18
  • 1
    If it's a fixed set of scalar-valued parameters (could you just tell us a sample number?) then I still don't see how switching to individual parameters and dumping the values into #temp tables is going to help. I really think you're barking up the wrong tree. – Aaron Bertrand Oct 06 '14 at 19:26
  • @AaronBertrand The current TVP for one of my stored procedures has only seven columns and generally have between 10 - 20 rows. – rjt011000 Oct 06 '14 at 19:30
  • 3
    So change that stored procedure to use a #temp table and *prove* that passing 140 parameters is going to be faster (or cause fewer recompiles, or whatever you think it's going to fix) than passing a data table. I'd love to see evidence of that. – Aaron Bertrand Oct 06 '14 at 19:32
  • @AaronBertrand thank you for the feedback. I truly appreciate it. I know I am presenting with alot of unknowns here (linked servers? network connectivity? HDs type?) – rjt011000 Oct 06 '14 at 19:45

0 Answers0