0

I am working on a bunch of Table Valued Functions which are large select statements. Are the query plans cached for these queries?

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22
  • 1
    Inline TVFs (a single `RETURN SELECT ...`) are interpolated in the queries they're used in and optimized according to specific use. Those plans are cached, but that's not quite the same as a plan for the TVF itself. For multi-statement TVFs I have no idea, as the performance stinks bad enough that I never use them as opposed to stored procedures. – Jeroen Mostert May 26 '21 at 19:40
  • I am maintaining a system in which tvf's were used. I know functions can have slow performance (if they do RBAR) but if the selects are optimized and the plans are cached why do you say that performance stinks? – benjamin moskovits May 26 '21 at 19:42
  • If it's an ml-tvf, rather than a i-tvf, yes @benjaminmoskovits performance won't be great. – Thom A May 26 '21 at 19:46
  • [This question](https://stackoverflow.com/q/2554333/4137916) contains a wealth of info on the subject. Turns out multi-statement TVFs are not quite as horrible as I thought, though still demonstrably worse than inline TVFs in typical scenarios. And yes, the execution plans for them are cached. I must say I have not used multi-statement TVFs for a long time, and SQL Server 2017 has improved on things by introducing interleaved execution for them, so the performance hits may not be as bad as they used to be. – Jeroen Mostert May 26 '21 at 19:49
  • Larnu judging by many of your answere I have great respect for you. But why do you say a ml-tvf performance is poor. I cannot tell my manager that I will change it to a proc without specific reasons. – benjamin moskovits May 26 '21 at 19:53
  • Jeroen I read the link you provided and it was very informative. Thanks – benjamin moskovits May 26 '21 at 20:00
  • 1
    If at all possible it's always worth investigating if it can be rewritten as an *inline* TVF (not an sproc, that's for entire queries using the TVFs) because it may well improve performance; you can typically get very far with just CTEs alone. Multi-statement TVFs are often only multi-statement because it was easier to write them that way, not because they couldn't be a single query. – Jeroen Mostert May 26 '21 at 20:01
  • So what are they, multi-statement or inline? As shown by that link and others online, there are usually large perf impacts with using a MS-TVF, usually fixed by using an inline one. The problem is that even if the plan is cached, the statistics are not available, also the whole query is executed even if you are joining or filtering on a single row, and they also require inserts into a table variable. And if there is a correlated parameter then it requires executing it again and again and again – Charlieface May 26 '21 at 20:21

0 Answers0