CASE Subqueries in BETWEEN and CASE Statements

Автор: Craig Freedman Subqueries in BETWEEN and CASE Statements

Рассмотрим следующий запрос:

CREATE TABLE T1 (A INT, B1 INT, B2 INT)

CREATE TABLE T2 (A INT, B INT)

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) BETWEEN T1.B1 AND T1.B2

Давайте посмотрим на план запроса, который мы получаем:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[A], [Expr1008], [Expr1014]))

       |--Merge Join(Inner Join, MERGE:([T2].[A])=([T2].[A]), RESIDUAL:([T2].[A]=[T2].[A]))

       |    |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1026]=(0) THEN NULL ELSE [Expr1027] END))

       |    |    |--Stream Aggregate(GROUP BY:([T2].[A]) DEFINE:([Expr1026]=COUNT_BIG([T2].[B]), [Expr1027]=SUM([T2].[B])))

       |    |         |--Sort(ORDER BY:([T2].[A] ASC))

       |    |              |--Table Scan(OBJECT:([T2]))

       |    |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Expr1028]=(0) THEN NULL ELSE [Expr1029] END))

       |         |--Stream Aggregate(GROUP BY:([T2].[A]) DEFINE:([Expr1028]=COUNT_BIG([T2].[B]), [Expr1029]=SUM([T2].[B])))

       |              |--Sort(ORDER BY:([T2].[A] ASC))

       |                   |--Table Scan(OBJECT:([T2]))

       |--Filter(WHERE:([Expr1014]<=[T1].[B2]))

            |--Index Spool(SEEK:([T1].[A]=[T2].[A] AND [T1].[B1] <= [Expr1008]))

                 |--Table Scan(OBJECT:([T1]))

Обратите внимание, что подзапрос выполняется дважды. Есть два просмотра T2, две сортировки и два агрегата потоков. В плане запроса оба набора операторов выделены жирным шрифтом.

Почему SQL Server планирует выполнение подзапроса дважды? Ответ заключается в том что SQL Server преобразует «X BETWEEEN Y AND Z» в «X <= Y AND X >= Z». Если, как в нашем случае, X является подзапросом, то подзапрос повторяется:

SELECT *

FROM T1

WHERE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) >= T1.B1 AND

    (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) <= T1.B2

Всё это происходит на очень раннем этапе обработки запроса. К сожалению, из-за этого SQL Server оценивает его так, как если бы это были два совершенно разных подзапроса. Вместо того, чтобы сначала сканировать T1, а затем применить подзапрос для каждой её строки, в этом плане сначала сканируется T2. Такой порядок соединения является результатом того, что оптимизатор выполнил декорреляцию подзапросов.

Как же заставить SQL Server выполнить подзапрос только один раз? На самом деле существует несколько решений, и все они предполагают переписывание запроса для вычисления подзапроса отдельно от предложения BETWEEN, чтобы при преобразовании предложения BETWEEN не дублировался подзапрос. Вот несколько примеров:

SELECT Q.A, Q.B1, Q.B2

FROM

    (

    SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B

    FROM T1

    ) Q

WHERE SUM_B BETWEEN Q.B1 AND Q.B2

 

SELECT T1.*

FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q

WHERE Q.SUM_B BETWEEN T1.B1 AND T1.B2

 

SELECT T1.*

FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q

WHERE T1.A = Q.A AND Q.SUM_B BETWEEN T1.B1 AND T1.B2

Все три варианта переписывания дают один и тот же план:

  |--Nested Loops(Inner Join, OUTER REFERENCES:([T2].[A], [Expr1008]))

       |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1016]=(0) THEN NULL ELSE [Expr1017] END))

       |    |--Stream Aggregate(GROUP BY:([T2].[A]) DEFINE:([Expr1016]=COUNT_BIG([T2].[B]), [Expr1017]=SUM([T2].[B])))

       |         |--Sort(ORDER BY:([T2].[A] ASC))

       |              |--Table Scan(OBJECT:([T2]))

       |--Filter(WHERE:([Expr1008]<=[T1].[B2]))

            |--Index Spool(SEEK:([T1].[A]=[T2].[A] AND [T1].[B1] <= [Expr1008]))

                 |--Table Scan(OBJECT:([T1]))

 SQL Server также преобразует оператор CASE в форме:

CASE X

    WHEN Y1 THEN Z1

    WHEN Y2 THEN Z2

    ...

    ELSE ZN

END

 

Into:

 

CASE

    WHEN X = Y1 THEN Z1

    WHEN X = Y2 THEN Z2

    ...

    ELSE ZN

END

Таким образом, операторы CASE тоже могут стать причиной описанной выше проблемы, если X является подзапросом. К сожалению, с оператором CASE количество повторных вычислений подзапроса зависит от количества предложений WHEN и может быть довольно большим. Вот простой запрос, иллюстрирующий проблему:

SELECT *,

    CASE (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A)

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1

Вот такой получается план этого запроса:

  |--Compute Scalar(DEFINE:([Expr1016]=CASE WHEN [Expr1008]=[T1].[B1] THEN 'B1' ELSE CASE WHEN [Expr1014]=[T1].[B2] THEN 'B2' ELSE NULL END END))

       |--Nested Loops(Inner Join, PASSTHRU:([Expr1008]=[T1].[B1]), OUTER REFERENCES:([T1].[A]))

            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T1].[A]))

            |    |--Table Scan(OBJECT:([T1]))

            |    |--Compute Scalar(DEFINE:([Expr1008]=CASE WHEN [Expr1030]=(0) THEN NULL ELSE [Expr1031] END))

            |         |--Stream Aggregate(DEFINE:([Expr1030]=COUNT_BIG([T2].[B]), [Expr1031]=SUM([T2].[B])))

            |              |--Table Scan(OBJECT:([T2]), WHERE:([T2].[A]=[T1].[A]))

            |--Compute Scalar(DEFINE:([Expr1014]=CASE WHEN [Expr1032]=(0) THEN NULL ELSE [Expr1033] END))

                 |--Stream Aggregate(DEFINE:([Expr1032]=COUNT_BIG([T2].[B]), [Expr1033]=SUM([T2].[B])))

                      |--Table Scan(OBJECT:([T2]), WHERE:([T2].[A]=[T1].[A]))

Тут снова применимы те же решения проблемы, которые заключаются в переписывании запроса одним из представленных ниже способов:

SELECT Q.A, Q.B1, Q.B2,

    CASE Q.SUM_B

        WHEN Q.B1 THEN 'B1'

        WHEN Q.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM

    (

    SELECT *, (SELECT SUM(T2.B) FROM T2 WHERE T2.A = T1.A) SUM_B

    FROM T1

    ) Q

 

SELECT T1.*,

    CASE Q.SUM_B

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1 CROSS APPLY (SELECT SUM(T2.B) SUM_B FROM T2 WHERE T2.A = T1.A) Q

 

SELECT T1.*,

    CASE Q.SUM_B

        WHEN T1.B1 THEN 'B1'

        WHEN T1.B2 THEN 'B2'

        ELSE NULL

    END CASE_B

FROM T1, (SELECT T2.A, SUM(T2.B) SUM_B FROM T2 GROUP BY T2.A) Q

WHERE T1.A = Q.A