CODEWITHSUNDEEP
sql-servert-sqluser-defined-functions
D. Tower
D. Tower

Reputation: 45

Performance using Inline table valued function to encapsulate SQL code

The question is: Can inline table valued functions (ITVF) be used to encapsulate and reuse code? Or will this result in performance issues?

I was researching inline table valued functions, which led me to this discussion: When would you use a table-valued function?

An answer in the discussion states that an inline table valued function "allows the optimizer to treat these functions no differently than the objects they encapsulate giving you optimum performance (assuming that your indexes and statistics are ideal)."

My original problem was that I was trying to reformat different data sources into a standard format and then union them. I tested unioning 6 different ITVF versus performing the unions and transformation all in one query. The execution plan was identical.

Since my background is in oop, I would prefer to split up queries into smaller functions, but before I commit to doing this throughout future projects, I was wondering if using too many ITVFs will eventually cause performance issues.

Upvotes: 1

Views: 4133

Answers (2)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 89361

Can inline table valued functions (ITVF) be used to encapsulate and reuse code?

Yes. And they are superior in this to multi-statement TVFs because with multi-statement TVFs the encapsulation prevents the query optimizer from pushing predicates into the TVF logic, and prevents it from accurately estimating the number of rows returned.

Or will this result in performance issues?

Short answer, not typically.

Longer answer:

There are 4 ways to encapsulate and reuse query logic (whole queries, not just scalar expressions).

  1. Views
  2. Inline Table Valued Functions
  3. Multi-Statement Table Valued Functions
  4. Temporary Tables
  5. Table Variables

Views and Inline TVFs don't inherently degrade performance, but they add to the complexity of query optimization.

Where the optimizer fails to consistently find low-cost plans you may need to intervene. A common way you can do that is by forcing spooling (ie materializing) of intermediate results, for instance replacing an Inline TVF with a multi-statement TVF, or by spooling results to a temp table ahead-of-time.

Spooling reduces the complexity of the encapsulating query at the cost of possible optimization of the encapsulated query when run in the context of the larger query.

When spooling results, Temp Tables are typically the best, as SQL Server they can have indexes and statistics that enable SQL Server to accurately assess the cost of the plans that will consume the intermediate results.

Upvotes: 1

Russell Fox
Russell Fox

Reputation: 5445

ITVFs are perfect for encapsulating query logic for re-use. I have a dozen or so financial reports that all query the same set of tables for roughly the same information, and by creating a function to provide that data I can be sure that all of my reports are pulling from the same body of data with the same filters and transformations, etc.

That being said, you could also just as easily create a view instead of an ITVF, but ITVFs also provide a way to filter or otherwise transform data based on the parameters sent in. For example, my financial functions could accept a district name as an optional input parameter and only return data for that district. By using the ITVFs this way the optimizer can, over time, optimize the query plan based on the parameters sent in, which helps rather than hindering performance.

I'd recommend that instead of a union on six different ITVFs, just pull all of your tables together into a single ITVF: that way you only have one place to make updates if your table schemas or reporting demands change.

Upvotes: 1

Related Questions