在CTE中使用局部变量会导致急剧放缓

Question

当我在CTE之前声明一个变量时，它会导致其完成约5倍才能完成。我在SQL Server 2008 R2上。

我的数据库中的时间存储为UNIX时间，因此我有一些从当地时间转换为UNIX时间的变量。现在，我只包括UNIX部分。积累是生产的运行总和，这是我使用CTE的原因。代码的以下部分大约需要5分钟才能执行。

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;

另一方面，如果我简单地将时间用力地编码到werry子句而不是使用变量，则运行大约需要大约1分钟。代码看起来如下(请查看以下条款(：

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

有没有一种方法可以使用局部变量或CTE中的其他方法，因为很难在UNIX时间键入。

编辑：前面加载更多代码(电源零件(并没有真正节省任何时间。我看到的大部分时间保存的时间位于where子句中的CTE部分。

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

当我使用@starttime时，它需要5倍长时间将其硬编码在第二个选项中。

我真的很想避免进行硬编码，因为我希望能够输入正常日期，例如'2018-03-14 06:00:00'并转换为上述时间格式。

Answer 1

查询优化器可以用硬编码值提前完成更多操作。

预先计算更多内容：

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;  

这对我很怀疑

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

您可以用铅或落后于一半的工作来做到这一点

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;

Answer 2

将硬码变量放入递归cte中时，将生成一次或优化数据结果，但是如果放置变量，则多次将CTE再生。

考虑创建CTE语句的临时表。不要忘记在末尾卸下临时表。