10

Every time I run into this type of queries I always wonder how would SQL Server work it out. If I run any type of query that requires a calculation and then use that value in multiple places, for example in the select and the order by, will SQL Server calculate it twice for every row or will it be cached? Furthermore, how does this work with User Defined Functions?

Examples:

SELECT CompanyId, Count(*)
FROM Sales
ORDER BY Count(*) desc

SELECT Geom.BufferWithTolerance(@radius, 0.01, 0).STEnvelope().STPointN(1).STX, Geom.BufferWithTolerance(@radius, 0.01, 0).STEnvelope().STPointN(1).STY
FROM Table

SELECT Id, udf.MyFunction(Id)
FROM Table
ORDER BY udf.MyFunction(Id)

Is there a way to make it more efficient or is SQL Server smart enough to handle it for me?

  • "it depends" here's one exhibit rextester.com/DXOB90032 – Martin Smith Mar 3 '17 at 20:24
  • Which you can compare with rextester.com/ARSO25902 – Martin Smith Mar 3 '17 at 20:31
  • @MartinSmith aren't you using a non deterministic function? If it is then I would expect SQL to execute it twice. – Jonas Stawski Mar 3 '17 at 22:42
  • there is always an exception! You can try SELECT RAND() FROM Sales order by RAND() - this is only evaluated once as it is both non deterministic and a run time constant. – Martin Smith Mar 4 '17 at 21:43
11

The SQL Server query optimizer can combine repeated calculated values into a single Compute Scalar operator. Whether or not it will do this depends on query plan costing and the properties of the calculated value. As expected, it will not do this for calculated values which are nondeterministic, which a few exceptions such as RAND(). It also will not do this for user defined functions.

I will start with a user defined function example. Here is an excellent example of a user defined function:

CREATE OR ALTER FUNCTION dbo.NULL_FUNCTION (@N BIGINT) RETURNS BIGINT
WITH SCHEMABINDING
AS
BEGIN
RETURN NULL;
END;

I also want to create a table and to put 100 rows into it:

CREATE TABLE X_100 (N BIGINT NOT NULL);

WITH
L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
L1   AS(SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2   AS(SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3   AS(SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4   AS(SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5   AS(SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n FROM L5)
INSERT INTO X_100 WITH (TABLOCK)
SELECT n
FROM Nums WHERE n <= 100;

The dbo.NULL_FUNCTION function is determistic. How many times will it be executed for the following query?

SELECT n, dbo.NULL_FUNCTION(n)
FROM X_100;

Based on the query plan this will be executed once for each row, or 100 times:

query plan 1

SQL Server 2016 introduced the sys.dm_exec_function_stats DMV. We can take snapshots of that DMV to see how many times a UDF is executed by a query.

SELECT execution_count
FROM sys.dm_exec_function_stats
WHERE object_id = OBJECT_ID('NULL_FUNCTION');

The result of that is 100, so the function was executed 100 times.

Let's try another simple query:

SELECT n, dbo.NULL_FUNCTION(n), dbo.NULL_FUNCTION(n) 
FROM X_100;

The query plan suggests that the function will be executed 200 times:

query plan 2

The results of sys.dm_exec_function_stats suggest that the function was executed 200 times.

Note that you cannot always use the query plan to figure out how many times a compute scalar is executed. The following quote is from "Compute Scalars, Expressions and Execution Plan Performance":

This leads people to think that Compute Scalar behaves like the majority of other operators: as rows flow through it, the results of whatever computations the Compute Scalar contains are added to the stream. This is not generally true. Despite the name, Compute Scalar does not always compute anything, and does not always contain a single scalar value (it can be a vector, an alias, or even a Boolean predicate, for example). More often than not, a Compute Scalar simply defines an expression; the actual computation is deferred until something later in the execution plan needs the result.

Let's try another example. For the following query I would hope that the UDF is calculated one time:

WITH NULL_FUNCTION_CTE (NULL_VALUE) AS
(
SELECT DISTINCT dbo.NULL_FUNCTION(0)
)
SELECT n , cte.NULL_VALUE
FROM X_100
CROSS JOIN NULL_FUNCTION_CTE cte;

The query plan suggests that it will be calculated one time:

query plan

However, the DMV reveals the truth. The compute scalar is deferred until it is needed, which is in the join operator. It is evaluated 100 times.

You also asked what you can do to encourage the optimizer to avoid recalculating the same expression multiple times. The best thing that you can do is to avoid using scalar UDFs in your code. Those have a number of performance issues outside of this question, including inflating memory grants, forcing the entire query to run with MAXDOP 1, bad cardinality estimates, and leading to additional CPU utilization. If you do need to use a UDF and the value of that UDF is a constant you can calculate it outside of the query and to put it in a local variable.

For queries without UDFs, you can try to avoid writing expressions which return the same result but aren't typed in exactly the same way. For this next example I'm using the publicly available AdventureworksDW2016CTP3 database, but really any database will do. How many times will COUNT(*) be calculated for this query?

SELECT OrderDateKey, COUNT(*) 
FROM dbo.FactResellerSales
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;

For this query we can figure this out by looking at the Hash Match (aggregate) operator.

hash match

The COUNT(*) is computed once for each unique value of OrderDateKey. Including the ORDER BY clause does not cause it to be calculated twice. You can see the execution plan here.

Now, consider a query that will return the exact same results but is written in a different way:

SELECT OrderDateKey, SUM(1)
FROM dbo.FactResellerSales
GROUP BY OrderDateKey
ORDER BY COUNT(*) DESC;

The query optimizer is not smart enough to combine them, so additional work will be done:

hash match 2

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.