SQL-Ex blog

IF EXISTS

Проблема, с которой вы можете тут столкнуться, является одной из row goal. И недостаток реализации в T-SQL.

Если я выполню этот запрос, он будет трудиться около 10 секунд.

IF EXISTS

(

    SELECT 

       1/0

    FROM dbo.Posts AS p

    JOIN dbo.Votes AS v

        ON  p.Id = v.PostId

    WHERE v.VoteTypeId = 1

    AND   v.CreationDate >= '2018-12-01'

    AND   p.PostTypeId = 1

)

BEGIN

    SELECT x = 1;

END;

Часть плана, которая нас беспокоит, - это поиск в таблице Votes.



SQL SERVER’S COST BASED OPTIMIZER™ думает, что 2.52 (округленно 3) строк потребуется прочитать, чтобы найти нужные нам данные, но в итоге приходится выполнять намного больше работы.

Здесь стоит сделать небольшой экскурс в тему, чтобы указать, что, когда вы настраиваете медленный запрос, уделять внимание стоимости операторов может оказаться очень плохим помощником. Причина, по которой этот запрос выполняется медленно, заключается в том, что Стоимость была вычислена неверно, и это видно. При правильной стоимости вы не получите такой план. Вы не потратили бы бОльшую часть времени выполнения запроса на оператор с самой низкой ненулевой стоимостью.

В обычной ситуации вы могли бы применить хинты запроса, чтобы выяснить, почему был выбран этот план, но вы не можете это сделать в контексте ветви IF. Это отстой, так как запрос с хинтом Hash Join срабатывает примерно за 400мс. Мы могли бы решить проблему с помощью этого хинта, или если вы выключили row goals для запроса.

Исправление

Для настройки нам потребуется немного изменить логику. Вместо упаковки запроса в IF EXISTS мы собираемся установить переменную на базе запроса, и использовать ее в логике IF.

DECLARE

    @do_it bit;

SELECT

    @do_it = 

    (

        SELECT 

            CONVERT

            (

                bit,

                ISNULL

                (

                    MAX(1), 

                    0

                )

            )

        FROM dbo.Posts AS p

        JOIN dbo.Votes AS v

            ON  p.Id = v.PostId

        WHERE v.VoteTypeId = 1

        AND   v.CreationDate >= '2018-12-01'

        AND   p.PostTypeId = 1

    )

OPTION(HASH JOIN);

IF @do_it = 1

BEGIN

    SELECT x = 1;

END;

Это дает быстрый план, к которому мы стремимся. Хотя вы не можете использовать здесь выражение CASE и получить hash join по причинам, которые объяснил Pablo Blanco.

Но вот он. Замечательный hash join.

EXISTS с предикатами OR

Общим шаблоном запроса является EXISTS… OR EXISTS для разборки разных вещей, но вы можете получить странную перезапись запроса оптимизатором (SplitSemiApplyUnionAll), которая выглядит очень похожей на шаблон LEFT JOIN… IS NULL для поиска строк, которых не существует. Что обычно является плохим шаблоном, как это обсуждалось в цитируемой статье.

SELECT

    c = COUNT_BIG(*)

FROM dbo.Users AS u

WHERE u.Reputation > 1000000

AND   EXISTS

      (

          SELECT

              1/0

          FROM dbo.Badges AS b

          WHERE b.UserId = u.Id

      )

OR    EXISTS

      (

          SELECT

              1/0

          FROM dbo.Comments AS c

          WHERE c.UserId = u.Id

      );

Вот, о чем я говорю, в плане для этого запроса.



Здесь вместо двух полусоединений для EXISTS мы получаем два правых внешних соединения. Это означает (подобности в процитированной выше статье), что соединяются все строки таблиц, и фильтры применяются много позже в этом плане. Вы можете увидеть одно из этих правых внешних соединений, наряду с фильтрами (по выражениям!) на представленной картинке.

Исправление

Конечно, чтобы это пофиксить, следует переписать запрос таким образом, чтобы оптимизатор не смог применить эту глупость.

SELECT

    c = SUM(x.c)

FROM 

(

    SELECT

        c = COUNT_BIG(*)

    FROM dbo.Users AS u

    WHERE u.Reputation > 1000000

    AND   EXISTS

          (

              SELECT

                  1/0

              FROM dbo.Badges AS b

              WHERE b.UserId = u.Id

          )

    

    UNION ALL 

    SELECT

        c = COUNT_BIG(*)

    FROM dbo.Users AS u

    WHERE EXISTS

          (

              SELECT

                  1/0

              FROM dbo.Comments AS c

              WHERE c.UserId = u.Id

          )

) AS x;

Этот запрос выполняется примерно за 1,5 секунды по сравнению с 4,9 секундами выше.



Когда вы сталкиваетесь с подобными проблемами, решения не всегда очевидны, как и проблема. Большинство статей, которые я пишу, связаны с настройкой запросов из-за проблем, возникающих у моих клиентов. Хотя большинство проблем с производительностью возникает из предсказуемых мест, иногда вам приходится иметь дело с такими пограничными случаями, когда оптимизатор занижает стоимость.