Início / dba / Perguntas / 47127
Accepted
beeks
Asked: 2013-07-27 20:14:36 +0800 CST

Solução alternativa para o bug do Anti-Semi Join

  • 772

Criei a seguinte consulta do SQL Server, mas ela está encontrando o defeito de junção anti-semi no SQL Server 2005, que resulta em estimativas de cardinalidade imprecisas (1 -- urgh!) e é executado para sempre. Como é um SQL Server de produção de longa data, não posso sugerir facilmente a atualização de versões e, como tal, não posso forçar a dica traceflag 4199 nessa consulta específica.

Estou tendo dificuldade em refatorar o arquivo WHERE AND NOT IN (SELECT). Alguém pode se importar em ajudar? Certifiquei-me de tentar usar as melhores junções com base em pares de chaves em cluster.

SELECT TOP 5000 d.doc2_id
    ,d.direction_cd
    ,a.address_type_cd
    ,d.external_identification
    ,s.hash_value
    ,d.publishdate
    ,d.sender_address_id AS [D2 Sender_Address_id]
    ,a.address_id AS [A Address_ID]
    ,d.message_size
    ,d.subject
    ,emi.employee_id
FROM assentor.emcsdbuser.doc2 d(NOLOCK)
INNER JOIN assentor.emcsdbuser.employee_msg_index emi(NOLOCK)
    ON d.processdate = emi.processdate
    AND d.doc2_id = emi.doc2_id
INNER LOOP JOIN assentor.emcsdbuser.doc2_address a(NOLOCK)
    ON emi.doc2_id = a.doc2_id
    AND emi.address_type_cd = a.address_type_cd
    AND emi.address_id = a.address_id
INNER JOIN sis.dbo.sis s(NOLOCK) ON d.external_identification = s.external_identification
WHERE d.publishdate > '2008-01-01'
    **AND d.doc2_id NOT IN (
        SELECT doc2_id
        FROM assentor.emcsdbuser.doc2_address d2a(NOLOCK)
        WHERE d.doc2_id = d2a.doc2_id
            AND d2a.address_type_cd = 'FRM'
        )**
OPTION (FAST 10)

Observe que a Employee_MSG_Indextabela tem 500m de linhas, doc21,5b de linhas, SIS~500m de linhas.

Qualquer ajuda seria apreciada!

sql-server sql-server-2005

2 respostas

  1. Best Answer

    Como é um SQL Server de produção de longa data, não posso sugerir facilmente atualizar as versões

    O bug de estimativa de cardinalidade anti-semijunção pode ser reproduzido em todas as versões do SQL Server de 2005 a 2012 inclusive . Todos exigem o sinalizador de rastreamento 4199 para ativar a correção, portanto, a atualização não resolveria seu problema sem ativar o 4199 (embora haja muitos outros bons motivos para atualizar a partir de 2005, é claro).

    ... como tal, não posso forçar a dica traceflag 4199 nesta consulta específica.

    Se apenas uma consulta específica for afetada, você poderá usar OPTION (QUERYTRACEON 4199)para habilitar o sinalizador de rastreamento apenas para essa consulta. Esta dica de consulta é documentada e tem suporte para uso com 4199 e se aplica a partir do SQL Server 2005 Service Pack 2 em diante.

    Essa dica é executada DBCC TRACEON (4199)de maneira eficaz DBCC TRACEOFF (4199)em torno da consulta e, como resultado, requer permissão de administrador do sistema. Se isso for um problema, adicione a dica usando um guia de plano .

    Você também deve testar todo o seu sistema com 4199 ativado em toda a instância . As regressões do plano são possíveis, mas no geral você pode achar que as várias correções do otimizador habilitadas por esse sinalizador valem a pena. Todas as correções futuras do processador de consulta que afetam o plano exigem que esse sinalizador seja ativado.

    Tudo o que disse...

    Conforme mencionado na resposta do ypercube , o bug requer duas ou mais colunas de junção para se manifestar (entre muitos detalhes). A redundância em sua NOT INcláusula faz com que o otimizador veja duas comparações de colunas (embora logicamente haja apenas uma), expondo assim o bug.

    A remoção dessa redundância 'resolverá' o problema dessa consulta específica, embora outras consultas que realmente tenham mais de um predicado de junção ainda sejam vulneráveis .

    Exemplo

    Para ilustrar, aqui está um exemplo baseado na postagem do blog CSS vinculada na pergunta (mas com um script completo!):

    CREATE TABLE dbo.tst_TAB1
(
    c1 integer NOT NULL, 
    c2 integer NOT NULL, 
    c3 integer NOT NULL
);

CREATE TABLE dbo.tst_TAB2
(
    c1 integer NOT NULL, 
    c2 integer NOT NULL, 
    c3 integer NOT NULL
);

CREATE INDEX i ON dbo.tst_TAB1 (c1, c2);
CREATE INDEX i ON dbo.tst_TAB2 (c1, c2);

    Dados de amostra:

    INSERT dbo.tst_TAB1
    (c1, c2, c3)
SELECT 
    number, number, number
FROM master.dbo.spt_values
WHERE 
    [type] = N'P' 
    AND number BETWEEN 1 AND 2047;

INSERT dbo.tst_TAB2 (c1, c2, c3)
VALUES (1, 1, 1);

    Consulta de teste usando NOT INcom predicado redundante:

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        -- This is redundant!
        WHERE
            t2.c1 = t1.c1
    );

    O plano de execução estimado mostra uma estimativa de 1 linha após a anti-semi join:

    Estime 1 linha

    Nota lateral: Na verdade, este é um exemplo de outro bug (raro). Escrever a WHEREcláusula como t1.c1 = t2.c1em vez de t2.c1 = t1.c1permite que o otimizador veja que os dois predicados de junção são de fato os mesmos e o bug não se manifesta.

    A mesma consulta com OPTION (QUERYTRACEON 4199):

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        WHERE
            t2.c1 = t1.c1
    )
OPTION (QUERYTRACEON 4199);

    O plano de execução estimado agora mostra uma estimativa de 2.046 linhas , o que é exatamente correto:

    Planeje com TF 4199

    Também podemos remover o predicado redundante:

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
    );

    O plano de execução usa uma otimização adicional não relacionada (o Stream Aggregate), mas o ponto importante é que a estimativa pós-junção esteja correta sem ter que habilitar 4199:

    Sem predicado redundante

    Múltiplas colunas anti-semi-junção

    É possível expressar uma anti-semi join em várias colunas usando a NOT INsintaxe. Esses casos exigirão 4199. Por exemplo, a próxima consulta une c1e c2:

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    );

    O plano de execução mostra a estimativa incorreta de 1 linha:

    Múltiplas colunas de junção

    Com 4199, o problema é resolvido:

    SELECT
    T1.c1
FROM tst_TAB1 AS t1 
WHERE
    t1.c1 NOT IN 
    (
        SELECT 
            t2.c1 
        FROM tst_TAB2 AS t2
        WHERE
            t2.c2 = t1.c2
    )
OPTION (QUERYTRACEON 4199);

    Múltiplas colunas de junção com 4199

    Outras sintaxes

    É melhor evitar o uso NOT INdessa maneira, principalmente pelos motivos mencionados nos livros on-line:

    Aviso BOL

    Esse problema foi escritoNOT IN muitas vezes. Existem muitas sintaxes alternativas disponíveis, das quais é minha preferência pessoal. Observe que alterar a sintaxe não evitará o erro de estimativa de cardinalidade:NULLsNOT EXISTS

    SELECT
    T1.c1
FROM dbo.tst_TAB1 AS t1 
WHERE 
    NOT EXISTS 
    ( 
        SELECT 1 
        FROM dbo.tst_TAB2 AS t2 
        WHERE 
            t2.c1 = t1.c1 
            AND t2.c2 = t1.c2
    );

    Essa união anti-semi de duas colunas produz a estimativa de 1 linha e requer 4199 para corrigi-la. Os planos de execução são exatamente os mesmos vistos anteriormente, portanto não os repetirei. A NOT EXISTSsintaxe evita o NULLsproblema com NOT IN.

    Outras observações

    Concordo com as outras observações de ypercube.

    • Espalhar NOLOCKdicas sobre cada tabela em uma consulta é um mau cheiro de código. Se a consulta realmente tolerar READ UNCOMMITTEDa semântica da transação, defina o nível de isolamento explicitamente.

    • TOPsem ORDER BYé outro sinal de código ruim. TOPrequer uma ORDER BYcláusula para definir o que TOPsignifica. Nunca confie no comportamento observado, use um nível superior explícito ORDER BYpara obter uma garantia.

    • INNER LOOP JOINe dicas de junção em geral, implicam uma FORCE ORDERdica de consulta. Isso limita severamente a liberdade do otimizador e geralmente é mal compreendido e mal aplicado. Nunca use dicas que você não entende completamente.

    • 14

  2. O link que você forneceu diz que o bug afeta apenas junções com mais de uma coluna:

    Observe que você só enfrenta esse problema quando várias colunas de junção estão envolvidas na junção, como no exemplo acima.

    E não consigo entender por que você escreveu NOT INdessa maneira (adicionando a d.doc2_id = d2a.doc2_idcondição na subconsulta). É redundante (a menos que as colunas unidas sejam anuláveis ​​- são?), Então você pode escrever o NOT INcomo:

    AND d.doc2_id NOT IN (
    SELECT d2a.doc2_id
    FROM assentor.emcsdbuser.doc2_address d2a
    WHERE d2a.address_type_cd = 'FRM'
    )

    ou com NOT EXISTS:

    AND NOT EXISTS (
    SELECT 1
    FROM assentor.emcsdbuser.doc2_address d2a
    WHERE d.doc2_id = d2a.doc2_id
        AND d2a.address_type_cd = 'FRM'
    )

    Experimente ambos e verifique se o problema de estimativa de cardinalidade foi resolvido.

    Outras notas:

    • Você tem um índice sobre address_type_cd?
    • Por que o uso múltiplo NOLOCK?
    • TOPsem ORDER BYpode fornecer resultados diferentes por execução.
    • 6

relate perguntas