请求或快或慢

有一个包含 CTE 的请求。表上有必要的索引。请求第一次足够快地完成（8 秒）。此外，我想WHERE通过不在索引中的字段在 CTE（过滤数据）内添加一个部分 => 自然，查询开始工作更长时间。我看到了必要的数据，现在我想回到原来的执行（在 8 秒内），我删除了之前添加的部分WHERE，但是请求现在运行了很长时间（大约 15 分钟）并且不想滚动回到主要状态:-(

应用了什么：

• 更新了整个数据库的统计信息 - 没有帮助。
• 查询计划中没有“重”运算符

PS 数据没有插入到表中；无需重建索引。告诉我在哪里进一步挖掘？

SET NOCOUNT ON
DECLARE @day TABLE ([from] datetime NOT NULL, [to] datetime NOT NULL)
DECLARE @current datetime = '20190501'
WHILE @current < '20190601'
BEGIN
    INSERT INTO @day SELECT @current, DATEADD(day, 1, @current)
    SET @current = DATEADD(day, 1, @current)
END
SET NOCOUNT OFF
;WITH cte AS(SELECT
Country = 'Russia',
MacroRegion = bsp.Name,
Customer_Name = d.Name,
Contract_Number = d.ExtID,
Terminal_Device_Number = ISNULL((SELECT pc.Value
                    FROM Contact pc
                    JOIN Contact ac ON ac.Demasked_ID = pc.ID
                    WHERE ac.ID = a.Contact_ID),v_apply.Device_ID),
Service_Name  =bst.name,
StartDate=bs.firstinsert,     
EndDate = bs.lastinsert,     
Vehicle_ID=v_apply.VEHICLE_ID,
Billing_Service_ID = bs.Billing_Service_id,
Service_Type_Category=bst.Service_Type_Category,
Asid_ID = a.ID,
RenderedLastTime =(SELECT MAX(t.LastTime)
               FROM(SELECT LastTime = MAX(rsi.RenderDate)
                    FROM RenderedServiceItem rsi
                    WHERE rsi.Billing_Service_ID = bs.Billing_Service_id
                    UNION ALL
                    SELECT LastTime =(SELECT TOP (1) vltl.InsertTime
                                      FROM dbo.VEHICLE_LOG_TIME_LAST vltl 
                                      (NOLOCK)
                                      WHERE vltl.Vehicle_ID = 
                                      v_apply.VEHICLE_ID))t),
RANK() OVER(PARTITION BY  CAST (firstinsert as date),d.name  ORDER BY 
firstinsert) AS Z,
FirstInsert=bs.firstinsert
FROM Asid a 
inner join Department d  on a.Department_ID = d.DEPARTMENT_ID
inner join VEHICLE v on v.DEPARTMENT = d.DEPARTMENT_ID
left  join Billing_Service_Provider bsp  ON bsp.Billing_Service_Provider_ID 
                                            = a.Billing_Service_Provider_ID
outer APPLY (select (select top(1)  hbs.ACTUAL_TIME
                                from H_Billing_Service hbs 
                                where hbs.Asid_ID = a.ID 
                                and hbs.ACTUAL_TIME between d.[from] and d. 
                                [to]
                                order by hbs.ACTUAL_TIME asc)firstinsert,
                (select top(1)  hbs.ACTUAL_TIME
                                from H_Billing_Service hbs 
                                where hbs.Asid_ID = a.ID 
                                and hbs.ACTUAL_TIME between d.[from] and d. 
                                [to]
                                order by hbs.ACTUAL_TIME desc)lastinsert,
                (select top(1)  hbs.Billing_Service_Type_ID
                                from H_Billing_Service hbs
                                where hbs.Asid_ID=a.ID) 
                                Billing_Service_Type_ID,
                (select top(1)  hbs.ID
                                from H_Billing_Service hbs  
                                where hbs.Asid_ID = a.ID )Billing_Service_id
                from @day d         
             )bs
inner join Billing_Service_Type bst ON bst.ID = bs.Billing_Service_Type_ID

 /*Вот в этой секции началась проблема, то есть без неё было быстро, с ней 
 стало медленно. Затем после комментирования сей секции запрос снова быстро 
 отрабатывать не хочет
 and bst.Service_Type_Category in ('Group1', 'Group2', 'Group3','Group4')*/
OUTER APPLY(SELECT v.Vehicle_ID, Device_ID =  CONVERT(varchar(32), 
ci.device_id)
        FROM MLP_Controller mlpc
        right JOIN Controller c ON c.CONTROLLER_ID = mlpc.Controller_ID
        JOIN Vehicle vs ON v.VEHICLE_ID = c.VEHICLE_ID
        LEFT OUTER JOIN CONTROLLER_INFO ci ON ci.CONTROLLER_ID = 
c.CONTROLLER_ID
        WHERE v.VEHICLE_ID = vs.VEHICLE_ID) v_apply
WHERE d.IsCommercial = 1 AND  bs.firstinsert is not null
),cte2 AS
(SELECT 
cte.Country,
cte.MacroRegion,
cte.Customer_Name,
cte.Contract_Number,
cte.Terminal_Device_Number,
cte.Service_Type_Category,
cte.Service_Name,
cte.Firstinsert,
cte.StartDate,
cte.EndDate,
cte.Asid_ID,
cte.Billing_Service_ID,
cte.Z,
RenderedCount = CASE
                  WHEN cte.Service_Type_Category IN ('Group1', 'Group2', 
                  'Group3','Group4')
                  THEN
                     (SELECT [Count] = COUNT(1)
                     FROM @day d
                     WHERE EXISTS
                       (SELECT 1
                        FROM  H_Billing_Service hbs 
                        WHERE hbs.Asid_ID = cte.Asid_ID
                        AND hbs.ACTUAL_TIME BETWEEN d.[from] AND d.[to]
                        AND hbs.ACTUAL_TIME BETWEEN cte.Firstinsert AND 
                        cte.EndDate
                       )
                     )
                  ELSE 0
               END,
 LbsPositionCount =(SELECT COUNT(1)
                    FROM Position_Radius pr (NOLOCK)
                    WHERE pr.Vehicle_ID = cte.Vehicle_ID
                    AND pr.Log_Time BETWEEN DATEDIFF(ss, '1970', 
                    cte.StartDate) AND DATEDIFF(ss, '1970', cte.EndDate) 
                    AND pr.Type = 1 
                    ),
 LbsPositionRequested = (SELECT COUNT(1)
                         FROM Command c
                         WHERE c.Target_ID = cte.Vehicle_ID
                         AND c.Date_Received BETWEEN cte.StartDate AND 
                         cte.EndDate
                         AND c.Type_ID = 20 
                        ),
RenderedLastTime
FROM cte cte
where s.Z=1)
select * from cte2

执行计划：