Блог

Используя SQL Server 2019 — У меня есть запрос:

 SELECT
    h.ItemType, h.ItemNum, h.Color, h.Cond, pg.*
FROM
    Inventory_Item_History_Summary h
LEFT JOIN 
    Price_Overrides po ON po.ItemType LIKE h.ItemType   '%' 
                       AND po.ItemNum = h.ItemNum  
                       AND po.ColorID = h.Color 
                       AND po.Cond = h.Cond
LEFT JOIN 
    (SELECT 
         *, 
         maxdate = MAX(PriceDate) OVER (PARTITION BY ItemNo, ItemType, Color, Cond) 
     FROM 
         [Price_Guide]) AS pg ON pg.PriceDate = pg.Maxdate 
                              AND pg.ItemType LIKE h.ItemType   '%' 
                              AND pg.ItemNo = h.ItemNum 
                              AND pg.Cond = h.Cond 
                              AND pg.Color = h.Color
WHERE 
    po.Price IS NULL 
    AND pg.ItemNo IS NULL
  

Если я выполняю его без pg.ItemNo IS NULL ограничения, для возврата 14 212 строк требуется меньше секунды. Я запустил его с этим ограничением, и для завершения требуется 6,5 минут, при этом возвращается 3041 запись.

Если я выгружаю результирующий набор без ограничения ItemNo is null в Excel и фильтрую его, я узнаю за полсекунды, что есть 3041 запись, где ItemNo равно null.

Что могло заставить SQL Server зависать на таком простом фильтре в Where предложении, особенно когда он возвращает полный набор результатов так быстро?

Примечание: Я не использую, EXISTS потому что в конечном итоге мое WHERE предложение должно гласить:

 WHERE po.Price IS NULL 
  AND (pg.ItemNo IS NULL OR pg.maxdate < CURRENT_TIMESTAMP - 30)
  

это означает, что я хочу, чтобы Price_Guide данные возвращались, если maxdate слишком старый. Я опубликовал упрощенную версию выше, потому что именно там я сузил проблему зависания.

РЕДАКТИРОВАТЬ: Смотрите ниже для частичного сравнения планов выполнения. Вы можете видеть, что при выполнении с ItemNo равно нулю, фильтр выдает «41300072 из 291», тогда как при выполнении без фильтра выдает «2906 из 291». Также странно, что быстрый запуск имеет хэш-карту в конце, где медленный запуск имеет вложенные циклы

Обновление: я смог выполнить переработку приведенного ниже запроса, которая занимает 3 секунды — все еще медленно, учитывая, что полный набор записей возвращается менее чем за 1. Однако, если кто-нибудь может пролить свет на то, почему оригинал был настолько засорен, я хотел бы услышать ваши мысли.

 Select h.ItemType, h.ItemNum, h.Color, h.Cond, pg.*
FROM Inventory_Item_History_Summary h
    LEFT JOIN Price_Overrides po on po.ItemType like h.ItemType   '%' and po.ItemNum=h.ItemNum and po.ColorID=h.Color and po.Cond=h.Cond
    LEFT JOIN (SELECT g.*, mpg.maxdate FROM Price_Guide g
                JOIN (SELECT ItemNo, ItemType, Color, Cond, max(PriceDate) as maxdate 
                        FROM Price_Guide GROUP BY ItemNo, ItemType, Color, Cond) mpg
                       on g.ItemNo=mpg.ItemNo and g.ItemType=mpg.ItemType and g.Color=mpg.Color and g.cond=mpg.cond and g.PriceDate=mpg.maxdate) AS pg on pg.ItemType like h.ItemType   '%' AND pg.ItemNo=h.ItemNum and pg.Cond=h.Cond and pg.Color=h.Color
WHERE po.Price IS NULL and (pg.maxdate is null or pg.maxdate<CURRENT_TIMESTAMP-30)
  

Чтобы убедиться, что с вашим сервером все в порядке, пожалуйста, выполните это (вы не на производственном сервере, верно?)

Сначала перестройте индекс в вашей таблице:

 ALTER INDEX ALL ON [Price_Guide] REBUILD
  

Затем удалите весь план выполнения, кэшированный на сервере (все еще не в рабочей среде, верно?)

 DBCC FREEPROCCACHE 
  

Затем выполните свой запрос с помощью pg.ItemNo IS NULL

У вас все еще есть проблема? Нет, нормально.

Да? Выберите оба запроса и нажмите CTRL-L, чтобы получить расчетный план выполнения обоих запросов. Щелкните правой кнопкой мыши и выберите «показать XML плана выполнения». Затем скопируйте xml-план выполнения и вставьте его в https://www.brentozar.com/pastetheplan / и предоставьте общий URL.

РЕДАКТИРОВАТЬ: вы выполнили обе команды? Мне было интересно, поскольку в планах выполнения есть что-то странное.

Если вы посмотрите на нижнюю ветвь самого быстрого из них. Это то, что показывает план выполнения, он оценил 2906 строк для чтения, и, фактически, он прочитал 2906

Второе выполнение показывает это: по оценкам, было прочитано 2906 строк, но (добавлю ваше любимое плохое слово здесь) он прочитал 41,3 миллиона строк, это количество строк, которого он не ожидал.

Хорошо, теперь, что делать. Я предполагаю, что нужно взглянуть на ваши индексы и переосмыслить ваш запрос. Вам действительно нужны все поля из Price_Guide, только несколько? Является ли кластеризованный индекс единственным индексом в таблице или существуют другие индексы? Рассмотрите возможность создания (отфильтрованного?) индекса на основе потребности запроса. Вам нужно переработать свой запрос, чтобы убедиться, что это уменьшено.