Запрос работает то быстро, то медленно

#sql #sql_server

Имеется запрос, содержащий CTE. Необходимые индексы на таблицах есть. В первый раз
запрос отрабатывает достаточно быстро (8 сек). Далее, мне захотелось внутри CTE добавить
секцию WHERE (отфильтровать данные) по полю, которого нет в индексе => естественно
запрос стал работать дольше. Я увидел необходимые данные и теперь я хочу вернуться
к первоначальному выполнению (за 8 сек), я удалил ранее добавленную секцию WHERE, но
запрос теперь выполняется очень долго (около 15 минут) и никак не хочет откатываться
до первостепенного состояния :-(

Что применял:


Статистику обновил для всей БД - не помогло.
В плане запроса никаких "тяжелых" операторов нет


P.S. В таблицы данные не инсёртятся, т.е. нет нужды в перестройке индекса. Подскажите,
куда дальше копать?   

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


План выполнения:
 
    

Ответы

Ответ 1

Судя по количеству nested loops, упирается в проц (но это не точно). Может быть в
IO, если памяти мало. Но запрос выглядит стремно, и быстро работать не будет в принципе.

Можно узнать, во что именно, выполнив перед запросом

SET STATISTICS TIME ON
SET STATISTICS IO ON


Получите метрики с сервера в output. Только actual plan перед замерами стоит отключить.

Скорее всего будет зашкаливать количество logical reads (из-за циклов, меряется в
количестве 8-ми килобайтных страниц) и общий CPU Time (из-за циклов).


  В плане запроса никаких "тяжелых" операторов нет




Вложенные циклы при таких объемах (по толщине стрелок - это сотни тысяч/миллионы)
- это очень тяжелые операторы. Они выполняют нижнюю часть для каждой строки из верхней
части. На картинке выше - синяя часть выполняется заново для каждой строки из красной
части.


Отломайте ту часть, которая не имеет отношения к проблеме (после коммента).
Уберите все, что вызывает принудительные nested loops - конструкции вида select top
1 быстро работать не будут в принципе.
Когда заработает быстро, и в плане не останется толстенных nested loops, возвращайте
обратно выборки firstinsert / lastinsert, переписывая их на оконные функции. Вместо
пачки select top 1 в цикле миллионы раз сделайте один запрос с использованием FIRST_VALUE
/ LAST_VALUE ... PARTITION OVER (Asid_ID, CONVERT(date, ACTUAL_TIME)) и одним джойном
его на таблицу дней.
После переписывания - расставьте POC-индексы (индексы по колонкам из Partition и
Order с include значений для агрегатов)


Если по производительности ударит что-то еще - задавайте новым вопросом.