Filtrar tabela antes de ingressar

Isso é uma boa prática?

Nesta circunstância, eu diria que sim. Eu provavelmente também adicionaria um OPTION (RECOMPILE)para deixá-lo "cheirar" os valores das variáveis. O plano ideal provavelmente variará dependendo da proporção de linhas na tabela maior que correspondem a esse intervalo.

Ele fornece um caminho extra potencialmente útil para o otimizador e não é algo que o otimizador de consulta faça sozinho, até onde eu sei. A coisa mais próxima disso é que, com uma junção de mesclagem, ela interromperá o processamento de uma entrada quando uma delas terminar. Isso significa que potencialmente evita uma verificação completa.

A única desvantagem que vem à mente seria se o cálculo dos valores mínimo/máximo do intervalo em si pode ser caro (mas isso deve ser muito barato se a tabela que você está usando como filtro estiver indexada nessa coluna).

Eu criei duas tabelas de teste

CREATE TABLE EmployeeBenefitData(EmployeeID INT PRIMARY KEY);

CREATE TABLE FilteredEmployee(EmployeeID INT PRIMARY KEY);

E carregou EmployeeBenefitData com números inteiros de 1 a 4.000.000 (6.456 páginas)

E FilteredEmployee com números inteiros de 2.000.000 E 2.010.000 (19 páginas)

E então executou 6 consultas do seguinte formulário

DECLARE @E1 INT,
        @E2 INT

SELECT @E1 = FE.EmployeeID,
       @E2 = EBD.EmployeeID
FROM   FilteredEmployee FE
       INNER LOOP JOIN EmployeeBenefitData EBD
         ON FE.EmployeeID = EBD.EmployeeID
OPTION (MAXDOP 1);

As 6 permutações foram feitas invertendo a ordem das duas tabelas e tentando todos os três tipos de junção LOOP, MERGE, HASH.

Os resultados foram os seguintes

+------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |      6456 |             0 |  8250009 |
| FE         | EBD         | Merge |              1 |      3257 |             1 |       19 |
| EBD        | FE          | Merge |              1 |      3257 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |      6456 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |      6456 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

As figuras acima ilustram o ponto sobre a junção de mesclagem, pois ela "só" varre pouco mais da metade da tabela maior. Ele ainda leu todas as linhas de 1 a 1.999.999 primeiro e as descartou.

Repetindo o experimento com a WHERE EBD.EmployeeID BETWEEN 2000000 AND 2010000deu o seguinte.

+------------+-------------+-------+----------------+-----------+---------------+----------+
| Left Table | Right Table | Join  | EBD Scan Count | EBD reads | FE Scan Count | FE reads |
+------------+-------------+-------+----------------+-----------+---------------+----------+
| FE         | EBD         | Loop  |              0 |     30637 |             1 |       19 |
| EBD        | FE          | Loop  |              1 |        21 |             0 |    20636 |
| FE         | EBD         | Merge |              1 |        21 |             1 |       19 |
| EBD        | FE          | Merge |              1 |        21 |             1 |       19 |
| FE         | EBD         | Hash  |              1 |        21 |             1 |       19 |
| EBD        | FE          | Hash  |              1 |        21 |             1 |       19 |
+------------+-------------+-------+----------------+-----------+---------------+----------+

A única consulta que não se beneficiou do predicado de intervalo adicional é aquela em que a tabela maior estava dentro de uma junção de loops aninhados.

Obviamente, isso não é surpreendente, pois esse plano (plano 1 abaixo) é conduzido por buscas repetidas de índice usando os valores de FilteredEmployee.

O plano 1 também foi o escolhido "naturalmente" pelo otimizador sem o predicado de intervalo. Com o predicado de intervalo em vigor, ele escolheu um plano de junção de mesclagem diferente, buscando no intervalo de índice relevante sem digitalizar linhas desnecessárias e custou significativamente mais barato (plano 2)

Já vi o planejador de consulta fazer coisas semelhantes com tabelas temporárias sem índice, mesmo às vezes quando o número de linhas na tabela temporária é pequeno.

Tente adicionar um índice que cubra as colunas que você está juntando e filtrando, para ver se o planejador usa as estatísticas delas para perceber que pode atingir a meta de maneira mais eficiente.

Além disso: se você tiver índices apropriados em todas as tabelas que estão sendo unidas, poderá adicionar uma dica de índice (adicionando WITH(INDEX(<index_name>))após a cláusula from) para fazer o planejador de consulta seguir uma determinada direção, embora isso torne suas visualizações/procs dependentes de nomes de índice e força o planejador de consultas a seguir uma rota específica, quando poderia ter escolhido uma rota melhor posteriormente, à medida que seus dados crescem/alteram.