none
LEFT JOIN OU EXIST no where? RRS feed

  • Pergunta

  •  

    Existe algum estudo sobre a utilização de LEFT JOIN OU EXISTS no where?

    Pergunto isso porque, eu tinha uma query que usava left join e demorava 9 horas para executar, no join eu acessava a tabela da direta através da pk, porém no explain não demonstrava isso e fala que buscava por um indice, porém claro que o resultado nao estava satisfatório alterei então para where arranquei tudo que era join e levei para where e a mesma consulta processou em 22 minutos e dessa vez acessando pela PK. Abaixo exemplifico as querys para mostrar o tipo de alteração que realizei:

     

    Esta consulta busca os pais sem filhos, suponha q estaja pegando tudo pelo PK tabela endereço e filho só como exemplo

     

     

    --- neste modelo resultado 9 horas

    SELECT PAI.NMPAI, PAI.DTNASCIMENTO, ENDEREO.DSENDERECO

     

    FROM PAI INNER JOIN ENDERECO ON

    PAI.CDENDERECO  = ENDERECO.CDENDERECO -- PK

     LEFT JOIN FILHO ON

    PAI.CDPAI =FILHO.CDPAI

     

    WHERE

    FILHO.CDPAI IS NULL

     

    Alterei para este modelo e processou em 22 minutos

     

    SELECT PAI.NMPAI, PAI.DTNASCIMENTO, ENDEREO.DSENDERECO

     

    FROM PAI, ENDERECO

    WHERE

    ENDERECO.CDENDERECO= PAI.CDENDERECO  AND

    NOT EXIST (SELECT CDPAI FROM FILHO WHERE

    FILHO.CDPAI = PAI.CDPAI )

     

     

    Se alguém tiver algum artigo o estudo que fala de performance entre join e where agradeceria.

     

     

    quarta-feira, 1 de outubro de 2008 19:29

Respostas

  • Pelo menos no SQL ele gerou um plano de execução dizendo que irá primeiro fazer o JOIN de PAI vc FILHO depois fazer outro join com ENDERECO e depois aplicar um FILTER para trazer apenas os dados desejados.

    Ele não tem como aplicar o filtro de IS NULL sem antes fazer o join pois ele ainda não "sabe" se o join irá existir ou não concorda? ... portanto neste caso primeiro ele tem que fazer o join para depois saber de CD_Pai da tabela FILHO será null ou não...

    Como o NOT EXISTS para assim que encontra uma linha que corresponde ao join ele não precisa varrer todos os registros da tabela, portanto tai o motivo da diferença de tanto tempo. Ao invez de ter que fazer Todos os Filhos para cada Pai ele faz o join e assim que encontra algum Pai ele para o join e passa para o próximo registro, já o join comum irá continuar a leitura para até o fim da tabela.
    quinta-feira, 2 de outubro de 2008 14:39
  • Show de bola é um assunto gostoso de discutir....rs..rs

    Não poderia aceitar que é melhor porque é por isso corri pra ve se tinha algo ambasado nisso.

    Vlew pela força

    quinta-feira, 2 de outubro de 2008 15:18

Todas as Respostas

  • Olá Marcos,

     

    Normalmente consultas de exceção podem ser feitas com o NOT IN, NOT EXISTS e com o LEFT OUTER JOIN (e o RIGHT também). Com um pouco de criatividade, você também pode contar com as CTEs e o operador EXCEPT no SQL Server 2005 (embora não ache que sejam mais eficientes).

     

    Não há nenhum estudo que diga que um é sempre melhor que o outro. O que percebo é que normalmente o NOT EXISTS e o LEFT OUTER JOIN tem um desempenho bem similar enquanto o NOT IN costuma ficar para trás (além de outros problemas inerentes como possibilidade de retornar dados "errados").

     

    Para o seu caso, considerando que as consultas são semanticamente idênticas, só consigo pensar que a ordem de avaliação das junções esteja diferente (embora o otimizador devesse ser capaz de otimizar isso já que é uma heurística básica).

     

    Em todo caso, eu sugeriria o seguinte para confirmarmos ou refutarmos essa possibilidade. Avalie o desempenho daS consultas abaixo. Ex:

     

    Code Snippet

    SELECT PAI.NMPAI, PAI.DTNASCIMENTO, ENDEREO.DSENDERECO

    FROM (PAI

    LEFT OUTER JOIN FILHO ON PAI.CDPAI =FILHO.CDPAI)

    INNER JOIN ENDERECO ON R.CDENDERECO = ENDERECO.CDENDERECO

    WHERE FILHO.CDPAI IS NULL

     

    SELECT PAI.NMPAI, PAI.DTNASCIMENTO, ENDEREO.DSENDERECO

    FROM (PAI

    INNER JOIN ENDERECO ON PAI.CDENDERECO = ENDERECO.CDENDERECO)

    LEFT OUTER JOIN FILHO ON PAI.CDPAI =FILHO.CDPAI

    WHERE FILHO.CDPAI IS NULL

     

     

    [ ]s,

     

    Gustavo

    quinta-feira, 2 de outubro de 2008 01:58
  • Você tem como criar um script de criação destas tabelas para efetuarmos alguns testes?

    Se possível coloca ai o script de create e insert, e indices exatamente como em sua base,

    Outra coisa, qual Versão e Service Pack você está utilizando.

    Abraço.
    quinta-feira, 2 de outubro de 2008 11:23
  • Marquinhos,

     

    Se possível post o seu script para que possamos ajudar!!!

    quinta-feira, 2 de outubro de 2008 12:13
  • Marquinhos criei um script para efetuar os testes, vou criar um artigo no meu blog com base nele, e explicar quais as diferenças de operadores escolhidos pelo SQL... já adiantando um pouco...

    NO 1 Select usou um FILTER no CDPAI IS NULL, no select 2 ele fez um Hash Join da Pai com a Filho com o resultado fez um sort por CDENDERECo para poder fazer um MERGE com a tabela ENDERECO, no select 3 que foi o pior possível, ele optou por jogar a tabela Filho em um Lazy Spoll ....

    Vou explicar tudo isso com mais calma no artigo..

    Segue o script criado.

    use tempdb
    GO

    IF OBJECT_ID('FILHO') IS NOT NULL
      DROP TABLE FILHO
    GO

    IF OBJECT_ID('PAI') IS NOT NULL
      DROP TABLE PAI
    GO

    IF OBJECT_ID('ENDERECO') IS NOT NULL
      DROP TABLE ENDERECO
    GO

    CREATE TABLE ENDERECO (CDENDERECO Integer Identity(1,1) PRIMARY KEY,
                           DSENDERECO VarChar(250))
    GO

    CREATE TABLE PAI (CDPAI Integer Identity(1,1) PRIMARY KEY,
                      DTNASCIMENTO DateTime,
                      NMPAI VarChar(250),
                      CDENDERECO Integer REFERENCES ENDERECO(CDENDERECO))
    GO

    CREATE TABLE FILHO (CDFILHO Integer Identity(1,1) PRIMARY KEY,
                        CDPAI Integer REFERENCES PAI(CDPAI))

    GO

    INSERT INTO ENDERECO(DSENDERECO) VALUES(NEWID())
    GO 10000

    INSERT INTO PAI(DTNASCIMENTO,NMPAI,CDENDERECO)
    SELECT GetDate(), NEWID(), CDENDERECO FROM ENDERECO
    GO

    INSERT INTO FILHO(CDPAI)
    SELECT CDPAI FROM PAI
    GO

    INSERT INTO PAI(DTNASCIMENTO,NMPAI,CDENDERECO)
    SELECT GetDate(), NEWID(), CDENDERECO FROM ENDERECO

    SELECT PAI.NMPAI,
           PAI.DTNASCIMENTO,
           ENDERECO.DSENDERECO
      FROM PAI
     INNER JOIN ENDERECO
        ON PAI.CDENDERECO  = ENDERECO.CDENDERECO
      LEFT JOIN FILHO
        ON PAI.CDPAI = FILHO.CDPAI
     WHERE FILHO.CDPAI IS NULL
    GO

    SELECT PAI.NMPAI,
           PAI.DTNASCIMENTO,
           ENDERECO.DSENDERECO
      FROM PAI
     INNER JOIN ENDERECO
        ON PAI.CDENDERECO  = ENDERECO.CDENDERECO
     WHERE NOT EXISTS(SELECT CDPAI
                        FROM FILHO
                       WHERE PAI.CDPAI = FILHO.CDPAI)
    GO

    SELECT PAI.NMPAI,
           PAI.DTNASCIMENTO,
           ENDERECO.DSENDERECO
      FROM PAI
     INNER JOIN ENDERECO
        ON PAI.CDENDERECO  = ENDERECO.CDENDERECO
     WHERE PAI.CDPAI NOT IN(SELECT CDPAI FROM FILHO)

    Assim que eu terminar o artigo eu coloco o link aqui... abraço.
    quinta-feira, 2 de outubro de 2008 12:16
  • Galera desculpe, mais

    minha query foi executado no ORACLE, esqueci de falar isso ...

     

    Mais o meu prob está resolvido pq eu fiz a alteração na query, porém procurei na net algo relacionado a performance de join e exists e não achei.

     

    mais passar de 9 horas para 44 minutos.

     

    quinta-feira, 2 de outubro de 2008 13:20
  • Ok, mesmo sendo no Oracle o que acontece é que com o NOT Exists, assim que uma linha for verdadeira o otimizador irá parar a execução e passar para o próximo registro, ao contrario do JOIN que terá que fazer a validação na tabela toda pois pode ser que exista mais de um filho para um pai, entendeu?

    Neste caso o NOT Exists vai ser melhor mesmo... de qq forma é um bom assunto para um post...
    quinta-feira, 2 de outubro de 2008 13:26
  •  

    Fabiano,

    isso sim eu tinha conhecimento que o IN degrada a performance, porque querendo ou não ele não deixa se ser o operador OR bem disfarçado, e isso arrebenda com a pequisa pelo indice.

    select * from pai where (cdendereco = 1 or cdendereco = 2 or cdendereco = 3 or cdendereco = 4 or cdendereco = N )  

    é a mesma coisa que

    select * from pai where cdendereco in (1 ,2 ,3 ,4 ,N  ) 

     

    Por isso estou achando que qdo eu coloco no where um Exists, primeiro ele resolve a tabela PAI, buscando apenas o que apenas não tem filho , com um resultado mais enchuto (falando em liguagem menos técnicia), irá montar os relacionamento entre e PAI e ENDERECO.

     

    Pq se não me engando a consulta é montada da seguinte forma:

    Busca todo o conjuto que atenda a clausula WHERE, depois faz as UNIÕES, INTERSECÇÃO dos conjuntos.

    Se eu comando tudo pelo WHERE o que é o filtro principal, logo tenho um ganho na performance.

    Vale lembra que estou especulando isso, pois não achei nada cientificamente dizendo sobre isso, mais tem lógica.

     

     

     

     

    quinta-feira, 2 de outubro de 2008 13:32
  • Pelo menos no SQL ele gerou um plano de execução dizendo que irá primeiro fazer o JOIN de PAI vc FILHO depois fazer outro join com ENDERECO e depois aplicar um FILTER para trazer apenas os dados desejados.

    Ele não tem como aplicar o filtro de IS NULL sem antes fazer o join pois ele ainda não "sabe" se o join irá existir ou não concorda? ... portanto neste caso primeiro ele tem que fazer o join para depois saber de CD_Pai da tabela FILHO será null ou não...

    Como o NOT EXISTS para assim que encontra uma linha que corresponde ao join ele não precisa varrer todos os registros da tabela, portanto tai o motivo da diferença de tanto tempo. Ao invez de ter que fazer Todos os Filhos para cada Pai ele faz o join e assim que encontra algum Pai ele para o join e passa para o próximo registro, já o join comum irá continuar a leitura para até o fim da tabela.
    quinta-feira, 2 de outubro de 2008 14:39
  • Show de bola é um assunto gostoso de discutir....rs..rs

    Não poderia aceitar que é melhor porque é por isso corri pra ve se tinha algo ambasado nisso.

    Vlew pela força

    quinta-feira, 2 de outubro de 2008 15:18