Como posso obter o pushdown de predicado na minha visão

Estou tentando entender se isso é uma falha do otimizador, se é o resultado de um mecanismo de otimização/custo deliberado, ou se eu simplesmente ignorei algo.

É um pouco de tudo isso.

Há muita coisa acontecendo na consulta apresentada - demais na verdade - então, para evitar escrever meio livro sobre isso, vou resumir ao elemento principal que está fazendo com que você não consiga o plano que procura:

O otimizador não empurra predicados para o lado interno de uma aplicação.

A regra que opera em seleções relacionais (filtros, predicados) acima de um apply chama-se, naturalmente, SELonApply. Ela realiza a seguinte substituição lógica:

Sel (A Aplicar B) -> Sel (Sel A Aplicar B)

Ele toma parte(s) de uma seleção potencialmente complexa envolvendo A e B, e empurra as partes que puder para a mesa de acionamento A. Nenhuma parte da seleção é empurrada para B. A(s) parte(s) da seleção que não pode(m) ser empurrado para baixo ficam para trás.

Isso pode soar como um descuido chocante e contrário à experiência. Isso porque não é a história completa.

O otimizador tenta converter uma aplicação para a junção equivalente no início do processo de compilação (durante a simplificação, antes do plano trivial e da otimização baseada em custo). Ele é capaz de empurrar seleções para baixo em ambos os lados de uma junção , onde é seguro. Essa junção pode, por sua vez, ser transformada em uma aplicação física durante a otimização baseada em custo.

O efeito de tudo isso é fazer parecer que o otimizador empurrou um predicado para o lado interno de uma aplicação:

1. Aplicação escrita transformada em uma junção.
2. Predicado(s) pressionado(s) em ambos os lados da junção.
3. Junte-se transformado em um aplicativo.

Deixe-me mostrar-lhe um exemplo:

DECLARE @T1 table (pk integer PRIMARY KEY, c1 integer NOT NULL INDEX ic1);
DECLARE @T2 table (fk integer NOT NULL, c2 integer NOT NULL, PRIMARY KEY (fk, c2));

SELECT 
    T1.*,
    T2.*
FROM @T1 AS T1
CROSS APPLY 
(
    SELECT T2.* 
    FROM @T2 AS T2
    WHERE T2.fk = T1.pk
) AS T2
WHERE 
    1 = 1
    AND T1.c1 = 1
    AND T2.c2 = 2;

Se você observar cuidadosamente o plano, verá o predicado em T2 enviado para a busca do lado interno e a junção de loop aninhada é uma aplicação (tem referências externas ). Isso só foi possível porque o otimizador foi capaz de reescrever a aplicação como uma junção inicialmente, enviar os predicados e, em seguida, transformar novamente em uma aplicação posteriormente.

Podemos desabilitar a reescrita de aplicação para junção usando o sinalizador de rastreamento não documentado 9114:

DECLARE @T1 table (pk integer PRIMARY KEY, c1 integer NOT NULL INDEX ic1);
DECLARE @T2 table (fk integer NOT NULL, c2 integer NOT NULL, PRIMARY KEY (fk, c2));

SELECT 
    T1.*,
    T2.*
FROM @T1 AS T1
CROSS APPLY 
(
    SELECT T2.* 
    FROM @T2 AS T2
    WHERE T2.fk = T1.pk
) AS T2
WHERE 
    1 = 1
    AND T1.c1 = 1
    AND T2.c2 = 2
OPTION (QUERYTRACEON 9114);

Isso significa que só SELonApplypode ser usado, o que só empurra para a mesa de acionamento A:

Observe que a parte da seleção em T2.c2 está 'presa' acima do apply, em um filtro. (A busca do lado interno está apenas na igualdade fk/pk especificada dentro do apply.)

O otimizador é construído com base em princípios relacionais. Ele aprecia um design de esquema relacional e consultas que usam construções relacionais. Aplicar (junção lateral) é uma extensão relativamente nova. O otimizador conhece muito mais truques com join do que com apply, daí o esforço inicial para reescrever.

Quando você usa coisas como aplicar ou o Top não relacional, está implicitamente assumindo mais responsabilidade pela forma final do plano. Em outras palavras, com mais frequência você terá que expressar sua consulta de maneira diferente (como em sua solução alternativa) para obter um bom resultado.

Para constar, minha preferência seria usar a função com valor de tabela embutida com posicionamento de predicado explícito. Se eu fosse reescrever a visão, eu poderia usar:

CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS
--- Regular sales
SELECT
    BU.BusinessUnit,
    RS.[Date],
    RS.SKU,
    RS.Quantity,
    RS.Amount
FROM dbo.BusinessUnits AS BU
JOIN dbo.Sales_unpartitioned AS RS
    ON RS.BusinessUnit = BU.BusinessUnit
WHERE 
    RS.SKU LIKE 'T%'

UNION ALL

--- This is a special reporting entry.
SELECT
    BU.BusinessUnit,
    SR.[Date],
    SR.SKU,
    SR.Quantity,
    SR.Amount
FROM dbo.BusinessUnits AS BU
JOIN dbo.Sales_unpartitioned AS SR
    ON SR.BusinessUnit = BU.BusinessUnit
WHERE
    1 = 1
    AND SR.SKU LIKE 'S%'
    --- We only want to see today's row.
    AND SR.[Date] = CONVERT(date, SYSDATETIME())
    --- In case of duplicates, get the row with the first "SKU".
    AND SR.SKU =
    (
        SELECT 
            MIN(SR2.SKU) 
        FROM dbo.Sales_unpartitioned AS SR2
        WHERE 
            SR2.BusinessUnit = SR.BusinessUnit
            AND SR2.[Date] = SR.[Date]
            AND SR2.SKU LIKE 'S%'
    );
GO

Para a consulta de teste fornecida:

DECLARE @from date='2021-01-01', @to date='2021-12-31';

SELECT *
FROM dbo.SalesReport_unpartitioned
WHERE BusinessUnit=16
  AND [Date] BETWEEN @from AND @to
ORDER BY BusinessUnit, [Date], SKU;

O plano de execução é:

A seção laranja é de vendas regulares. A seção amarela é para a entrada de relatório especial.

Este é apenas um daqueles casos em que o otimizador não foi escrito para lidar com esse requisito complicado específico, pois não pode dizer que o envio do datepredicado não está afetando nada na topsubconsulta.

Existe uma maneira, use uma junção e reescreva top 1como um filtro em uma row_numberanálise particionada. Incluir o BusinessUnitno row_numberparticionamento significa que é legal empurrar para baixo.

CREATE OR ALTER VIEW dbo.SalesReport_unpartitioned
AS

SELECT bu.BusinessUnit,
       s.[Date],
       s.SKU,
       s.Quantity,
       s.Amount
FROM dbo.BusinessUnits AS bu
JOIN (
    --- Regular sales
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM dbo.Sales_unpartitioned AS t
    WHERE  t.SKU LIKE 'T%'

    UNION ALL

    --- This is a special reporting entry. We only
    --- want to see today's row. In case of duplicates,
    --- get the row with the first "SKU".
    SELECT t.BusinessUnit, t.[Date], t.SKU, t.Quantity, t.Amount
    FROM  (SELECT s.BusinessUnit, s.[Date], s.SKU, s.Quantity, s.Amount, row_number() over (partition by s.BusinessUnit order by s.SKU) rn
           FROM dbo.Sales_unpartitioned AS s
           WHERE s.[Date]=CAST(SYSDATETIME() AS date)
             AND s.SKU LIKE 'S%'
          ) t
     WHERE   rn = 1
) AS s
ON bu.BusinessUnit = s.BusinessUnit

Colar o plano