CODEWITHSUNDEEP
sql-servert-sqlquery-optimizationranking
RaffaeleT
RaffaeleT

Reputation: 264

Same window function in multiple fields in T-SQL: optimization?

I am using the very same windowing function in multiple WHEN in a T-SQL SELECT.

SELECT 
    ModuleUsage = CASE
        WHEN Operation = 1 and OperationResult=1 and 1 != LAG(operation) OVER(
            PARTITION BY moduleid, clientname
            ORDER BY moduleid, clientname, timestamp, operation
        )
            THEN 1
        WHEN Operation = 2 and OperationResult=3 and 1 = LAG(operation) OVER(
            PARTITION BY moduleid, clientname
            ORDER BY moduleid, clientname, timestamp, operation
        )
            THEN -1
        ELSE 0
    END
    , *
FROM [dbo].[LicencesTracing]

Is it evaluated twice or does the query optimizer reuse the result the second time? Thanks in advance

Upvotes: 2

Views: 1500

Answers (2)

Alan Burstein
Alan Burstein

Reputation: 7918

You can determine if your Window function is being evaluated once or more by looking at the actual execution plan (e.g. run the query with "include actual execution plan" turned on).

If you have an index in place to support that window function then it really does not matter much as the operation is inexpensive. A POC index would be very helpful for this query. The index would look like this:

CREATE nonclustered index xxx on dbo.LicencesTracing(moduleid, clientname, timestamp, operation)
INCLUDE <whatever columns you are returning>;

You could simplify your query and force the optimizer to evaluate the Window function by calculating it in a subquery; something like this: (note that I can't test this...)

SELECT ModuleUsage = CASE 
    WHEN Operation = 1 and OperationResult = 1 and lagOp <> 1 THEN  1
    WHEN Operation = 2 and OperationResult = 3 and lagOp =  1 THEN -1 ELSE 0 
END
FROM
(
  SELECT Operation, OperationResult, lagOp =
    LAG(operation) OVER(PARTITION BY moduleid, clientname 
                   ORDER BY moduleid, clientname, timestamp, operation), *
  FROM [dbo].[LicencesTracing]
) sub;

Upvotes: 1

gotqn
gotqn

Reputation: 43636

Could we rewrite the query like below in order to have the function only one time?

       -- window function <> 1
SELECT CASE WHEN LAG(operation) OVER(PARTITION BY moduleid, clientname ORDER BY moduleid, clientname, timestamp, operation) <> 1

            THEN CASE WHEN Operation = 1 and OperationResult=1 THEN 1 ELSE 0 END

       -- window function = 1
       ELSE

            CASE WHEN Operation = 2 and OperationResult=3 THEN -1 ELSE 0 END
       END AS ModuleUsage
      ,*
FROM [dbo].[LicencesTracing];

And I thing using IIF will simplify the code:

SELECT CASE WHEN LAG(operation) OVER(PARTITION BY moduleid, clientname ORDER BY moduleid, clientname, timestamp, operation) <> 1 
            THEN IIF(Operation = 1 and OperationResult=1, 1, 0)
            ELSE IIF(Operation = 2 and OperationResult=3, -1, 0)
       END AS ModuleUsage
      ,*
FROM [dbo].[LicencesTracing];

Upvotes: 1

Related Questions