CODEWITHSUNDEEP
sql-servert-sqluser-defined-functionscommon-table-expression
Daniel Gimenez
Daniel Gimenez

Reputation: 20599

Bad query plan with query using CTE and scalar functions

I have a poorly performing query on a very large table that uses a CTE to narrow down the results. The CTE results in three rows max and is produced by getting a max aggregate value from the source table and then using CROSS APPLY and scalar functions to produce the desired columns.

Interestingly, if I load the data from the CTE into a table variable and join the table variable to my source table then the performance of the query improves quite dramatically.

The query plans are very different:

What would be the way to fix this so I could just use the CTE - maybe a query or join hint of some sort?

-- table variable for alt version of query
-- DECLARE @PeriodsTbl TABLE (PeriodCode varchar(10), PeriodDate date, PeriodDateKey int);

WITH PeriodsSource AS (
    SELECT forPeriods.*, forPeriodDateKeys.*
    FROM (SELECT dbo.DateKeyToDate(MAX(EndDateKey)) MaxDate FROM BigTable) forMaxDate
    CROSS APPLY (
        SELECT x.*, dbo.DateToDatekey(x.PeriodDate) PeriodDateKey
        FROM ( VALUES
            ( 'cur', CAST(MaxDate AS DATE) )
            , ( 'pw', CAST(DATEADD(day, -7, MaxDate) AS DATE) )
            , ( 'py', CAST(DATEADD(day, -364, MaxDate) AS DATE) )
        ) x ( PeriodCode, PeriodDate )
    ) forPeriods
)
--INSERT INTO @PeriodsTbl 
--SELECT PeriodCode, PeriodDate, PeriodDateKey 
--FROM PeriodsSource ps;
SELECT *
FROM PeriodsSource ps -- Use @PeriodsTbl For Table Version
INNER JOIN BigTable bt ON bt.StartDateKey <= ps.PeriodDateKey 
    AND bt.EndDateKey >= ps.PeriodDateKey

Upvotes: 0

Views: 311

Answers (1)

Sean Lange
Sean Lange

Reputation: 33580

My guess is the performance problems are because of the liberal use of scalar functions. They are routinely terrible from a performance perspective. Converting those to an inline table valued function would be my first step. Make sure they are NOT multi-statement table valued functions though or you might actually end up going backwards for performance. Or if the function is fairly simple you could just inline the logic which will be even better.

EDIT

Using a scalar function in the join caused the plan to change from a more efficient one to one joining without a predicate and filtering after. PeriodDateKey is produced in the CTE from the function DateToDatekey and that is what is being joined to BigTable. Changing it to a functionally equivalent expression caused the plan to change and produced a big improvement.

Trying to move the function call outside of the CROSS APPLY produced no improvement and while removing all references to scalar functions produced a performance gain, it was not as significant as the gain realized by the one change to PeriodDateKey

Upvotes: 1

Related Questions