column alias for expression slows query

Question

Thought this was really weird, so I thought I would post it and see what people think.

I have the following query:

;with base as
(
SELECT Distinct
    JobName
    ,ActualStart as 'Start'
    ,ActualDurationInSeconds as 'DurationInSeconds'
    , right('0' + CONVERT(varchar(2), floor(ActualDurationInSeconds)/3600 ),2)
    + ':' + right('0' + CONVERT(varchar(2), floor(ActualDurationInSeconds)/60%60) ,2) as 'DurationHrsMins'
FROM JobStats
WHERE JobName like 'WW%'
)

, Yesterday As
(
    Select Distinct JobName,
        Start,
        DurationHrsMins,
        DurationInSeconds
    From base
    Where Start >= CONVERT(DATE, GETDATE()-1)
)

, OneWeek as
(
    Select JobName,
    AVG(DurationInSeconds) as 'Avg'
    From base
    Where Start >= CONVERT(DATE, GETDATE()-7)
    Group By JobName
)

, TwoWeek as
(
    Select JobName,
    AVG(DurationInSeconds) as 'Avg'
    From base
    Where Start >= CONVERT(DATE, GETDATE()-14)
    Group By JobName
)
, Lifetime as
(
    Select JobName,
    AVG(DurationInSeconds) as 'Avg'
    From base
    Group By JobName
)   
, Deviation as
(
    Select JobName,
    STDEV(durationInSeconds) as deviation
    From base
    Group by JobName
)

SELECT Distinct
   Y.JobName,
   Y.DurationHrsMins as 'CurrentDisplay',
   Y.DurationInSeconds as 'CurrentDurationInSeconds',
   right('0' + CONVERT(varchar(2), floor(W1.Avg)/3600 ),2)
        + ':' + right('0' + CONVERT(varchar(2), floor(W1.Avg)/60%60) ,2) as 'OneWeekAvgDisplay',
   W1.Avg as OneWeekDuration,
   right('0' + CONVERT(varchar(2), floor(W2.Avg)/3600 ),2)
        + ':' + right('0' + CONVERT(varchar(2), floor(W2.Avg)/60%60) ,2) as 'TwoWeekAvgDisplay',
   W2.Avg as TwoWeekDuration,
    right('0' + CONVERT(varchar(2), floor(L.Avg)/3600 ),2)
        + ':' + right('0' + CONVERT(varchar(2), floor(L.Avg)/60%60) ,2) as 'LifetimeAvgDisplay',
   L.Avg as LifeTimeDuration,      
   CASE 
    WHEN Y.DurationInSeconds > W1.Avg * 1.15 THEN 'RED'
    WHEN Y.DurationInSeconds > W1.Avg * .8 THEN 'YELLOW'
    WHEN Y.DurationInSeconds < W1.Avg THEN 'GREEN'
    ELSE 'GREEN'
   END as StopLight,
   CONVERT(int,d.Deviation)
FROM Yesterday Y
JOIN OneWeek as W1 on W1.JobName = Y.JobName
JOIN TwoWeek as W2 on W2.JobName = Y.JobName
JOIN Lifetime as L on L.JobName = Y.JobName
JOIN Deviation as D on d.JobName = Y.JobName

 Order By Y.JobName

Now this query runs great, its runs so fast, it doesn't even register as a 1 second execution. HOWEVER, if I give the CONVERT(int,d.Deviation) an alias, suddenly the query now has to run for a minute! WTF!?

I looked at the estimated exaction plans, and they are completely different! I find this crazy that giving an alias could have this large of an affect.

Any ideas on why this may occur?

Thanks

EDIT:

using an inner merge join on Deviation allows for a quick execution with the name.

Answer 1

One of the reasons that queries run very very fast, then suddenly tank your server after a single change is because the cache is no longer valid (at least the server doesn't think so). I can't find specific documentation on how Common Table Expressions (CTE) interact with the cache, but my guess is that changing the alias causes the server to stop using the cache and re-process your data.

Since you already have the query developed, one way to test this would be to drop your cache before you run each query. I'm willing to bet that your first query takes much longer than before, and the run times will be almost identical between the two.

You'll want to do a little reading on that before you mess with the cache though.

How can I clear the SQL Server query cache?