As Kimberly blogged about recently, SQLskills is embarking on a new initiative to blog about basic topics, which we’re calling SQL101. We’ll all be blogging about things that we often see done incorrectly, technologies used the wrong way, or where there are many misunderstandings that lead to serious problems. If you want to find all of our SQLskills SQL101 blog posts, check out SQLskills.com/help/SQL101.
At the spring SQLintersection conference in Orlando, one of the attendees asked me a question about why a query wouldn’t go parallel even though the cost for the plan was in the hundreds. There are actually a number of different reasons why a query might execute serially, but usually one of the first things that comes to mind is scalar user defined functions. Developers love to use scalar user defined functions inside of SQL Server because it lets them compartmentalize code and easily reuse it, which is a common goal in object-oriented programming, but it’s also a performance anti-pattern for SQL Server as I’ll demonstrate in this post.
For the purposes of this post, I’m using the WorldWideImporters Standard Edition example database, and I’m going to recreate a scalar user defined function that I recently saw in a client engagement that was used to format the output of a column strictly for client presentation purposes. Using the [Warehouse].[ColdRoomTemperatures_Archive] table, which stores 5 second intervals of temperature readings as the basis for my example report, we are going to calculate the delta temperature between samples. For the delta if the number is greater than zero, then we want it to show with a + sign before the value and it if is below zero a – sign. An example query to provide the basic data set for the report would be:
USE [WideWorldImporters]
GO
SELECT
a.ColdRoomSensorNumber,
a.Temperature AS StartingTemp,
b.Temperature AS EndingTemp,
a.ValidFrom,
a.ValidTo,
b.Temperature - a.Temperature AS Delta
FROM Warehouse.ColdRoomTemperatures_Archive AS a
INNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b
ON a.ValidTo = b.ValidFrom
AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber
WHERE a.ValidFrom BETWEEN '05/01/2016' AND '05/02/2016'
ORDER BY ColdRoomSensorNumber, ValidFrom;
For the sake of argument, the scenario I am using is a presentation layer issue, and the argument can be made that this problem should be handled by the presentation/application tier to do the formatting requested. However, there could be scenarios where the same data is needed in more than one application, reports, API feeds, etc. so for consistency the formatting is determined to be required for the SQL output. This is where a developer might write a function to handle the formatting so the code can be reused anytime we need to output temperature deltas to make sure that everything does it exactly the same way.
IF OBJECT_ID('dbo.GetFormatedTemperatureDelta') IS NOT NULL
DROP FUNCTION dbo.GetFormatedTemperatureDelta;
GO
CREATE FUNCTION dbo.GetFormatedTemperatureDelta
(@StartingTemperature decimal(10,2), @EndingTemperature decimal(10,2))
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @Result VARCHAR(10);
SET @Result = CASE
WHEN @StartingTemperature-@EndingTemperature > 0
THEN '+'+CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)
WHEN @StartingTemperature-@EndingTemperature < 0
THEN CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)
ELSE '0.00'
END;
RETURN(@Result);
END
GO
SELECT
a.ColdRoomSensorNumber,
a.Temperature AS StartingTemp,
b.Temperature AS EndingTemp,
a.ValidFrom,
a.ValidTo,
dbo.GetFormatedTemperatureDelta(b.Temperature, a.Temperature) AS Delta
FROM Warehouse.ColdRoomTemperatures_Archive AS a
INNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b
ON a.ValidTo = b.ValidFrom
AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber
WHERE a.ValidFrom BETWEEN '05/01/2016' AND '05/02/2016'
ORDER BY ColdRoomSensorNumber, ValidFrom;
As soon as they apply this formatting to the reporting query using the function, performance may or may not be noticeably impacted. Using the above two queries the duration of execution for both is just above 1 second of time based on the current data being requested for the report as shown by the STATISTICS TIME output for both:
SQL Server Execution Times:
CPU time = 1155 ms, elapsed time = 643 ms.SQL Server Execution Times:
CPU time = 1250 ms, elapsed time = 1680 ms
However, notice the difference in the duration for the original statement vs the statement using the user defined function. The user defined function took over 2.5 times the duration. This problem gets worse as the size of the data set gets larger, but what we don’t see is the impact that this had to the way the query executes. Comparing the before function use and after function use execution plans:
Plan Without Function
Plan With Function
Before using the function, we had a parallel execution and after the plan is now serial. If we look at what happens for each statements execution with Extended Events using the sqlserver.sql_statement_completed and sqlserver.module_end events, filtered to my specific session_id and tracking how events relate to each other with TRACK_CAUSLITY=ON for the session, we’ll find that the function is executing for every row returned by the query, turning our set based operation into a RBAR (row-by-agonizing-row) operation.
CREATE EVENT SESSION [TrackFunctions] ON SERVER
ADD EVENT sqlserver.module_end(
WHERE ([sqlserver].[session_id]=(85))),
ADD EVENT sqlserver.sql_statement_completed(
WHERE ([sqlserver].[session_id]=(85)))
WITH (TRACK_CAUSALITY=ON)
GO
Extended Event Session Results (grouped by activity_id)
We could change the function to do nothing at all and the impacts would be exactly the same. The query with the scalar user defined function will always run serially and row-by-row with the exception of if the function is created using SQLCLR which does allow scalar user defined functions that DO NOT perform data access to leverage parallelism. However, for this simple logic SQLCLR wouldn’t be required or recommended just to gain parallel query execution back. The easier fix is to simply inline the code to the original query:
SELECT
a.ColdRoomSensorNumber,
a.Temperature AS StartingTemp,
b.Temperature AS EndingTemp,
a.ValidFrom,
a.ValidTo,
CASE WHEN b.Temperature - a.Temperature > 0 THEN '+'+CAST(b.Temperature-a.Temperature AS VARCHAR)
WHEN b.Temperature - a.Temperature < 0 THEN CAST(b.Temperature-a.Temperature AS VARCHAR)
ELSE '0.00' END
AS Delta
FROM Warehouse.ColdRoomTemperatures_Archive AS a
INNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b
ON a.ValidTo = b.ValidFrom
AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber
WHERE a.ValidFrom BETWEEN '05/01/2016' AND '05/02/2016'
ORDER BY ColdRoomSensorNumber, ValidFrom
GO
SQL Server Execution Times:
CPU time = 1233 ms, elapsed time = 676 ms.
Plan with inline expression
Here we have the fast execution time with a parallel plan but we are sacrificing the reuse of the code for other places where we might want to encapsulate the same logic. However, that doesn’t have to be the case since this logic can be done inline to the query, it can also be written into an inline-table valued function.
IF OBJECT_ID('dbo.GetFormatedTemperatureDelta_tvf') IS NOT NULL
DROP FUNCTION dbo.GetFormatedTemperatureDelta_tvf;
GO
CREATE FUNCTION dbo.GetFormatedTemperatureDelta_tvf
(@StartingTemperature decimal(10,2), @EndingTemperature decimal(10,2))
RETURNS TABLE
AS
RETURN (SELECT Delta =CASE
WHEN @StartingTemperature-@EndingTemperature > 0
THEN '+'+CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)
WHEN @StartingTemperature-@EndingTemperature < 0
THEN CAST(@StartingTemperature-@EndingTemperature AS VARCHAR)
ELSE '0.00'
END)
GO
SELECT
a.ColdRoomSensorNumber,
a.Temperature AS StartingTemp,
b.Temperature AS EndingTemp,
a.ValidFrom,
a.ValidTo,
Delta
FROM Warehouse.ColdRoomTemperatures_Archive AS a
INNER JOIN Warehouse.ColdRoomTemperatures_Archive AS b
ON a.ValidTo = b.ValidFrom
AND a.ColdRoomSensorNumber = b.ColdRoomSensorNumber
CROSS APPLY dbo.GetFormatedTemperatureDelta_tvf(b.Temperature, a.Temperature)
WHERE a.ValidFrom BETWEEN '05/01/2016' AND '05/02/2016'
ORDER BY ColdRoomSensorNumber, ValidFrom
GO
SQL Server Execution Times:
CPU time = 1251 ms, elapsed time = 729 ms.
Plan With TVF
Here we get the best of both worlds, a parallel execution plan and the ability to reuse this logic in other places where we need the formatted output. If you look closely at the plan and the Compute Scalar operator, you will find that it is identical to the plan with inline code shown above. As a consultant, I’ve done a lot of conversions of scalar user defined functions to inline table valued functions to resolve performance issues and improve code scalability for clients. This is a very common problem to see in engagements and understanding the impacts of scalar user defined functions to performance is important for fast performance and optimizing TSQL code.
