Início / dba / Perguntas / 323929
Accepted
Paradox
Asked: 2023-02-23 04:06:15 +0800 CST

Uma seleção dentro de uma cláusula where afeta o desempenho?

  • 772

Eu sei que devemos evitar o uso de funções na cláusula where de uma consulta sempre que possível, pois a função será executada para cada linha para retornar os resultados adequados. No entanto, o mesmo princípio se aplica a uma seleção aninhada em uma cláusula where?

por exemplo:

where col1 in (select col2 from table)

vs mover o select para uma tabela temporária.

sql-server

1 respostas

  1. Best Answer

    Não, o mesmo princípio não se aplica ao exemplo que você deu. O otimizador geralmente tentará converter esse tipo de predicado em uma tabela derivada em uma construção baseada em conjunto como a JOINou APPLY.

    Usando o banco de dados de amostra StackOverflow2010 no SQL Server 2019, executei essas duas consultas (depois de criar os dois índices não clusterizados indicados):

    /*
CREATE NONCLUSTERED INDEX IX_Reputation
ON dbo.Users (Reputation);

CREATE NONCLUSTERED INDEX IX_OwnerUserId
ON dbo.Posts (OwnerUserId);
*/

/* Sub-query / table expression in the IN clause */
SELECT p.Id
FROM dbo.Posts p
WHERE 
    p.OwnerUserId IN 
    (
        SELECT u.Id 
        FROM dbo.Users u 
        WHERE u.Reputation > 500000
    );

/* Materialize in a temp table first */
DROP TABLE IF EXISTS #tmpUsers;
CREATE TABLE #tmpUsers
(
    Id int NOT NULL PRIMARY KEY
);

INSERT INTO #tmpUsers
SELECT u.Id 
FROM dbo.Users u 
WHERE u.Reputation > 500000;

SELECT p.Id
FROM dbo.Posts p
WHERE 
    p.OwnerUserId IN 
    (
        SELECT u.Id 
        FROM #tmpUsers u 
    );

    Captura de tela do SSMS mostrando os planos de execução para essas duas consultas, sobre as quais explico mais abaixo

    Ambas as consultas são concluídas aproximadamente na mesma quantidade de tempo, com planos muito semelhantes (o plano da tabela temporária tem uma varredura em vez de uma busca, porque a busca faz parte da inserção da tabela temporária).

    As estimativas são um pouco melhores na versão de tabela não temporária, o que pode fazer uma diferença maior com conjuntos de dados maiores.

    Como observação, você pode obter estimativas muito melhores nesse caso adicionando OPTION (USE HINT ('FORCE_LEGACY_CARDINALITY_ESTIMATION')). Para as mesmas duas consultas, as estimativas passaram de 10 a 30 vezes os valores reais para 2 a 3 vezes os valores reais. Isso ocorre porque o modelo de estimativa de cardinalidade original executa melhor correspondência de histograma do que o simples 'alinhamento grosseiro' do CE posterior (para obter alguns detalhes sobre essa alteração, consulte o artigo de Paul White SQL Server Join Estimation using Histogram Coarse Alignment ) .

    • 3

relate perguntas