Using Local Variables in a CTE causes drastic Slowdown

Question

When I declare a variable before my CTE it causes it to take about 5x longer to complete. I am on SQL Server 2008 R2.

Time in my database is stored as Unix time so I am having some variables convert from Local Time to Unix Time. For now I am only including the Unix portion. Accumulation is a running sum of production and is the reason I am using a CTE. The following portion of Code takes approximately 5 minutes to execute.

Declare @StartTime bigint
Declare @EndTime bigint
declare @ticksPerDay bigint

Set @StartTime = 635330772000000000
set @EndTime = 635357556000000000
set @ticksPerDay = 864000000000

WITH MyCTE as

    (SELECT ROW_NUMBER() OVER (ORDER BY CH.countercode, CH.Time ASC) AS 
    Sequence, CH.PlantCode, CH.CounterCode, CH.Time, CH.Accumulation
        ,DateAdd(mi, DATEDIFF(mi,getutcdate(), GetDate()),
        DateAdd(d,Cast((CH.Time * Power(10.00000000000,-7)/60/60/24) as 
        int), CAST('0001-01-01' as Date)) + Cast(ch.Time * 
        Power(10.00000000000,-7)/60/60/24%1 as Datetime)) as LocalTime

    FROM eit.CounterHist CH

        INNER JOIN eit.CounterBasic cb on ch.CounterCode = cb.CounterCode
        INNER JOIN eit.LineEquipment le on  cb.PlantElementCode = 
        le.PlantElementCode

    Where CH.Time>=@StartTime -(@ticksPerDay/24) and CH.Time<=@EndTime and 
        le.IsCriticalMachine = 1)

SELECT c1.PlantCode, c1.CounterCode, c1.time, c1.LocalTime, 
COALESCE(c1.Accumulation - c2.Accumulation, 0) AS Prod

FROM MyCTE AS c1
    Inner JOIN MyCTE AS c2 ON c1.Sequence = c2.Sequence + 1

Where c1.Time>=@StartTime and c1.Time<=@EndTime
order by c1.Sequence ASC;

On the other hand, if i simply hard code the times into the where clauses instead of using variables, it takes around 1 minute to run. the code would look as follows (please look at the where clauses):

WITH MyCTE as

   (SELECT ROW_NUMBER() OVER (ORDER BY CH.countercode, CH.Time ASC) AS 
   Sequence, CH.PlantCode, CH.CounterCode, CH.Time, CH.Accumulation,

    DateAdd(mi, DATEDIFF(mi,getutcdate(), GetDate()),
    DateAdd(d,Cast((CH.Time * Power(10.00000000000,-7)/60/60/24) as int), 
    CAST('0001-01-01' as Date)) + Cast(ch.Time * 
    Power(10.00000000000,-7)/60/60/24%1 as Datetime)) as LocalTime

   FROM eit.CounterHist CH
     INNER JOIN eit.CounterBasic cb on ch.CounterCode = cb.CounterCode
     INNER JOIN eit.LineEquipment le on  cb.PlantElementCode = 
     le.PlantElementCode

   Where CH.Time>=635330772000000000 -(864000000000/24) and 
    CH.Time<=635357556000000000 and le.IsCriticalMachine = 1)

SELECT c1.PlantCode, c1.CounterCode, c1.time, c1.LocalTime, 
 COALESCE(c1.Accumulation - c2.Accumulation, 0) AS BottleCount

FROM MyCTE AS c1
  Inner JOIN MyCTE AS c2 ON c1.Sequence = c2.Sequence + 1

Where c1.Time>=635330772000000000 and c1.Time<=635357556000000000
order by c1.Sequence ASC

Is there a way to use a local variable or something else in a CTE as typing in Unix time is difficult.

Edit: Front Loading more of the Code(power part) didn't really save any time. The bulk of the time save i Am seeing is in the CTE portion in the Where Clause.

Where CH.Time>=@StartTime
Where CH.Time>=635330772000000000

When i use @StartTime, it takes 5 times longer then when i hard code it in the second option.

I really want to avoid hard coding that as i want to be able to input a normal date such as '2018-03-14 06:00:00' and convert to the above time format.

This may have little to do with the CTE itself and more to do with the implicit conversions the sql engine is doing when dividing your bigint variables by an int. Check out your query plans and see if there are a lot of implicit conversions going on. Even without the conversions Paparazzo's answer of moving the computations up front and not doing them on each row will help tremendously, it will help infinitely more if it avoids the conversions happening each time as well. — user7396598, Mar 27 '18 at 18:01
Also, check out these articles on CTE performance vs temp table https://stackoverflow.com/questions/690465/which-are-more-performant-cte-or-temporary-tables -- and https://dba.stackexchange.com/questions/13112/whats-the-difference-between-a-cte-and-a-temp-table/13117#13117 -- By joining the CTE on itself you are actually running the cte query twice, which in this case would almost double your overall execution time. — user7396598, Mar 27 '18 at 18:03
Have a look at the query plan for both queries. What's the difference between them? — Greg, Mar 27 '18 at 19:13

paparazzo · Answer 1 · 2018-03-27T17:48:45.930

Query optimizer can do more up front with hard coded values.

Calculate more stuff up front:

Declare @StartTime bigint   = 635330772000000000
Declare @EndTime bigint     = 635357556000000000
declare @ticksPerDay bigint = 864000000000 
declare @StartTimeAdj bigint = @StartTime - (ticksPerDay / 24) 
declare @pwr float = Power(10.00000000000,-7)/60/60/24

WITH MyCTE as
(SELECT ROW_NUMBER() OVER (ORDER BY CH.countercode, CH.Time ASC) AS Sequence
      , CH.PlantCode, CH.CounterCode, CH.Time, CH.Accumulation
      , DateAdd(mi, DATEDIFF(mi,getutcdate(), GetDate()), DateAdd(d,Cast((CH.Time * @pwr) as int), CAST('0001-01-01' as Date)) + Cast(ch.Time * @pwr%1 as Datetime) 
               ) as LocalTime
 FROM eit.CounterHist CH
     INNER JOIN eit.CounterBasic cb on cb.CounterCode = ch.CounterCode  
     INNER JOIN eit.LineEquipment le on le.PlantElementCode = cb.PlantElementCode
 Where CH.Time >= @StartTimeAdj
   and CH.Time <= @EndTime  
   and le.IsCriticalMachine = 1
)

SELECT c1.PlantCode, c1.CounterCode, c1.time, c1.LocalTime, 
       COALESCE(c1.Accumulation - c2.Accumulation, 0) AS Prod    
FROM MyCTE AS c1
    Inner JOIN MyCTE AS c2 ON c1.Sequence = c2.Sequence + 1    
Where c1.Time> = @StartTime and c1.Time <= @EndTime
order by c1.Sequence ASC;

This is highly suspect to me

Inner JOIN MyCTE AS c2 ON c1.Sequence = c2.Sequence + 1

You can do that with lead or lag a cut the work in like half

WITH MyCTE as
(SELECT lead(Accumulation) OVER (ORDER BY CH.countercode, CH.Time ASC) AS LeadAccumulation
      , CH.PlantCode, CH.CounterCode, CH.Time, CH.Accumulation
      , DateAdd(mi, DATEDIFF(mi,getutcdate(), GetDate()), DateAdd(d,Cast((CH.Time * @pwr) as int), CAST('0001-01-01' as Date)) + Cast(ch.Time * @pwr%1 as Datetime) 
               ) as LocalTime
 FROM eit.CounterHist CH
     INNER JOIN eit.CounterBasic cb on cb.CounterCode = ch.CounterCode  
     INNER JOIN eit.LineEquipment le on le.PlantElementCode = cb.PlantElementCode
 Where CH.Time >= @StartTimeAdj
   and CH.Time <= @EndTime  
   and le.IsCriticalMachine = 1
)

SELECT c1.PlantCode, c1.CounterCode, c1.time, c1.LocalTime, 
       COALESCE(c1.Accumulation - c1.LeadAccumulation, 0) AS Prod    
FROM MyCTE AS c1
Where c1.Time> = @StartTime and c1.Time <= @EndTime
order by c1.countercode, c1.Time ASC;

I am trying to front load more of my code, but i have to do it in more chunks then you showed. One problem i am running into is when using the pwr variable i had to separate out the 60/60/24 because that isnt how the calculation works, but when i try to use the modulus with the pwr variable its giving me an error that a float and int are incompatible even though it worked before declaring it. Another thing to note is i cannot use lead and lag due to being in SQL 2008 R2. — Ryan Masad, Mar 27 '18 at 18:40

score 0 · Answer 2 · answered Mar 27 '18 at 22:09

When you put hardcode variables into a recursive CTE then data result is generated once or optimized, but if you put variables the CTE is regenerated multiple times.

Consider to create a temporary table of your CTE statement. Do not forget remove temporary table at the end.

Using Local Variables in a CTE causes drastic Slowdown

2 Answers2