What is the importance of inlining in query optimisation?

Question

I'm reading up on query inlining and I'm finding it very confusing.

On one hand, I'm being told that user-defined scalar-valued functions are terrible for performance because they (almost always) can't be inlined and that this stops the entire query that references them from being run in parallel. Similarly, I'm told that inline table-valued functions are awesome for performance because they can be inlined. On the other hand, I'm being told that I need to worry about repeated work and bad performance from both CTEs and derived tables because they get inlined.

Putting the above together, I'm confused about if inlining is a good thing or not and would like to see an explanation for why it is important for query optimisation. When is it a good thing? When is it a bad thing? How can I spot each of these in execution plans?

Answer 1

I think you are confusing three different cases:

a. Inline Table Valued Functions (iTVFs). They are called inline as opposed to multi-statement TVFs. Multi-statement TVFs store query results in temp tables, potentially causing TEMPDB contention. iTVFs do not and they tend to generally perform better (see this interesting article for a performance comparison).

b. CTE inlining. I am not sure, but I think this is relevant to the fact that a CTE is executed every time it is referenced. So, if you join a CTE to itself 3 times, the CTE will be executed 3 times. You are probably better off persisting the results of the CTE to a temp table in this case and reference the temp table in you query, instead.

c. Scalar UDF inlining. Scalar UDFs are notorious for having bad performance, due to a number of reasons, explained here (Namely: iterative execution, serial execution, lack of costing, interpreted execution). Microsoft tried to improve the situation by introducing scalar UDF inlining, in SQL Server 2019. With scalar UDF inlining, the body of the function is inlined in the query, thus yielding better execution plans. Alas, as you mention in your question, there is a set of requirements that must be fulfilled in order for a scalar UDF to be inlinable. As a result, not all scalar UDFs can be inlined and thus benefit from the performance gain offered by the better execution plan.

Answer 2

The basic thing to understand is that more complex queries are harder to optimize. The SQL Server query optimizer will spend a limited amount of time searching for an optimal query plan, and the more query operators there are, the more potential query plans the optimizer needs to consider. So as queries become more complex, the likelihood of finding an optimal plan decreases.

So combining a small number of query operators in a way that allows the query optimizer to find one big plan is a good thing. But as you add more and more operations to the query, the chances of getting a bad plan increase.

What this means in practice is that you should let the query optimizer do its job, but if you observe that your complex queries aren't getting very good plans, you can make some changes to your query to simplify the query optimization. One common option is to replace CTEs/subqueries with temp tables, breaking up the query into multiple simpler queries. Similarly, you can create additional indices, replace views with indexed views or physical tables, add query hints, etc.