And then call it like this:
We can now reuse this function anywhere we need to retrieve the value of an order. Seems useful, right?
There Be Dragons
Suppose you have the following query which is used as part of an export process to extract all orders from the system:
SELECT o.OrderId, o.OrderDate FROM Sales.Orders o ORDER BY o.OrderDate ASC
You have a requirement to change it to also include the value of each order. Great—you have a function for that! So, you slip in a call to the scalar UDF like so:
SELECT o.OrderId, o.OrderDate, Sales.CalculateOrderLineTotal(o.OrderId) AS OrderTotal FROM Sales.Orders o ORDER BY o.OrderDate ASC
Well, no. You soon hear back that the export process is performing poorly after this change. Let's take a look at the impact that change has had on the resources needed to fulfil the query and return the 73,595 results.
Using Plan Explorer, we get the following execution details:
|(A) Original Query (without order total)||(B) Query with scalar UDF|
Ouch. There's that bite I was saying about. In fact, less of a bite and more of a three-course meal. Huge increases in execution time, CPU utilization and reads. You might ask, "What if that's just the cost of getting that extra Order Total value returned with each row?" Let's check—here is the same query, except we've pulled the query from the scalar UDF out as a correlated subquery into the main query:
SELECT o.OrderId, o.OrderDate, (SELECT SUM(Quantity * UnitPrice) FROM Sales.OrderLines ol WHERE ol.OrderId = o.OrderId) AS OrderTotal FROM Sales.Orders o ORDER BY o.OrderDate
Again running through Plan Explorer to grab the stats, this time we see (C):
|(A) Original Query (without order total)||(B) Query with scalar UDF||(C) Query with correlated subquery|
What a difference! Slightly higher than the original query (A), but that's to be expected given we've introduced a lookup on another table to return the extra data.
But, they're doing the same thing to get the Order Total aren't they? Let's check the execution plans.
Scalar UDF approach [B]:
Plan Explorer shows us we have some warnings. If we hover over the Compute Scalar operation, we see the following warning about the use of the UDF:
Inlined correlated subquery approach [C]:
With the scalar UDF approach, there is no mention of the Sales.OrderLines table despite the fact we are now querying that table. Instead, what happens inside the scalar function is hidden behind the single "Compute Scalar" operation—as shown above, we can see something is of concern via the warnings. No matter what you have going on inside the function, there's no detail exposed in the execution plan—just a whole lot of cost. Whereas with the inlined subquery, we have full visibility of exactly what it's doing to get the extra data from Sales.OrderLines.
Scalar functions can often cause poor performance, just like in this case, for a number of reasons. The scalar function is called iteratively, once per row in the result-set. In this case, that's 73,595 executions, resulting in the poor performance we're seeing here. During the query optimization process, SQL Server does not apply any costings to the scalar function call, whereas clearly we're seeing it has a huge cost.
As conscientious software engineers, it can be easy to apply the programming best practises, like DRY, in the database layer and assume it's the best solution. But we should always check the performance of queries to verify that we're taking an approach that is acceptable in terms of performance and resource utilization.
In my next blog post, I'll cover enhancements that were introduced in SQL Server 2019 (compatibility level 150) that enable scalar UDFs to be inlined automatically into the calling query, and follow this exercise through to see how this plays out.