How are the scalar functions executed by the optimizer

Question

I have read online that scalar functions can affect the performance since the optimizer does not have access to the contents of the scalar function. Since the function is executed for each row, does the optimizer have to build an execution plan for the contents of the functions every single time or does it build the plan the first time it accesses the function and then uses it for all the other rows?

Answer 1

For scalar UDFs that aren't inlined, the actual query plan is only generated once (obviously assuming no statistics updates, index rebuilds or any DDL changes in between executions). The plan is cached and re-used like any other query plan.

It does not get any data in sys.dm_exec_query_stats, so you cannot get any statistics on it that way. But you can use sys.dm_exec_function_stats to get similar info.

You can see the cached query plan using the following query

SELECT
  p.objtype,
  OBJECT_NAME(f.object_id),
  st.text,
  qp.query_plan,
  p.refcounts,
  p.usecounts,
  f.execution_count
FROM sys.dm_exec_function_stats f
JOIN sys.dm_exec_cached_plans p ON p.plan_handle = f.plan_handle
CROSS APPLY sys.dm_exec_query_plan(p.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(p.plan_handle) st
WHERE f.object_id = OBJECT_ID(N'dbo.fnTest')
OPTION(RECOMPILE);

You can turn this on or off using the Database Scoped Configuration EXEC_QUERY_STATS_FOR_SCALAR_FUNCTIONS (so far that knob is just for Azure SQL DB and Managed Instance).

---

The actual execution of the function does occur in a separate scope, requiring marshalling of the parameters and return result back and forwards, as well as context switching (different SET options, and different local variable scopes for example). This is one of the main reasons they perform so badly.

Answer 2

Oracle will cache DETERMINISTIC scalar functions input value and return value and re-use them for efficiency. Since this is SQL Server, a different approach is needed.

If your query calls the same scalar function for a large number of rows where the inputs to the scalar function are just a few values, you can use a precomputed outer table join for performance gains.

If, for example, your scalar function takes a person's age as input and outputs a number as output, you could

• Use an inline table giving the input and return values of the scalar function for the range of the inputs
• Join that table to your main query and not use the scalar function