0

Suppose that you have a multi-statement table-valued function that makes heavy use of table variables. What methods exist for optimising them? I only know of the following and they all disappoint me.

  1. Convert the table variables to memory-optimised table variables and pray that it makes a big difference. Given that they don't have statistics, it probably won't.
  2. Throw OPTION(RECOMPILE) everywhere and hope for the best. This will give you better cardinality estimates, but those aren't silver bullets.
  3. Cheat: Remove the table variables, convert the function to an inline table-valued function, and hope for the best.
  4. Cheat: Convert the table variables to temp tables, convert the function to a stored procedure, and pray that the change of interface isn't a problem.

Assume SQL Server 2019, since I think that's the last time the performance of anything mentioned in this question improved.

1
  • @Charlieface I think I'm missing your point. You've repeated #3. As for what the function actually does, I've seen this situation in many different places.
    – J. Mini
    Feb 29 at 8:05

1 Answer 1

2
+50
  1. Cheat: Remove the table variables, convert the function to an inline table-valued function, and hope for the best.

  2. Cheat: Convert the table variables to temp tables, convert the function to a stored procedure, and pray that the change of interface isn't a problem.

These aren't cheats, these are the solutions.

A non-inlined function (regardless of scalar or table-valued) is bad for performance for two reasons:

  1. It's executed RBAR (row by agonizing row). So it typically needs to processed once for every row in your dataset, that it's applied against.

  2. It creates a serial zone in the execution plan around where the multi-statement table-valued function is executed, meaning parallelism goes out the window for that area of the plan.

If you can convert the function to be inline-able then your odds of a better outcome are measurably better. It isn't really a "hope for the best" situation. Same thing for changing the function to a procedure instead. It opens up a lot more options for you to work with the SQL Server engine when query tuning. If the concern with a procedure is the change in interface, in some limited use cases you can wrap the procedure back in a function so that it's the same interface for the consumer. Though, the best option is to start using the proper features (e.g. procedures when possible) from the get-go.

A query re-write is almost certain if you're having performance issues with the code that was written for the multi-statement table-valued function.

2
  • As much as I love being right, the confirmation that my disliked solutions are indeed the solutions is rather saddening.
    – J. Mini
    Feb 28 at 21:00
  • @J.Mini I mean there's inherent limitations of the different things at play that you mentioned. Sans an actual execution plan, there's not much that can be offered other than recommending using the features that offer you better potential at rewriting the query to be more performant. TBH I almost never use table variables and almost always prefer temp tables to them, for various reasons. The best option is to start using the proper features (e.g. procedures when possible) from the get-go.
    – J.D.
    Feb 28 at 21:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.