LEFT JOIN OU EXIST no where?

Question

 

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.

 

 

Answer 1

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

Answer 2

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.

Answer 3

Marquinhos,

 

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

Answer 4

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.

Answer 5

Esse site pode te ajudar.

http://www.sqlmagazine.com.br/Colunistas/PauloRibeiro/08_Tunning_ExecutionPlan.asp

Answer 6

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.

 

Answer 7

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...

Answer 8

 

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.

 

 

 

 

Answer 9

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.

Answer 10

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