Qual consulta SQL é mais rápida? Filtrar em critérios de associação ou cláusula Where?

Compare estas duas consultas. É mais rápido colocar o filtro nos critérios de associação ou na cláusula were. Eu sempre senti que é mais rápido nos critérios de junit, pois reduz o resultado no menor tempo possível, mas não tenho certeza.

Vou fazer alguns testes para ver, mas também queria obter opiniões sobre quais seriam mais claras também.

Consulta 1

SELECT * FROM TableA a INNER JOIN TableXRef x ON a.ID = x.TableAID INNER JOIN TableB b ON x.TableBID = b.ID WHERE a.ID = 1 /* <-- Filter here? */ 

Consulta 2

 SELECT * FROM TableA a INNER JOIN TableXRef x ON a.ID = x.TableAID AND a.ID = 1 /* <-- Or filter here? */ INNER JOIN TableB b ON x.TableBID = b.ID 

EDITAR

Fiz alguns testes e os resultados mostram que é realmente muito próximo, mas a cláusula WHERE é na verdade um pouco mais rápida! =)

Eu concordo absolutamente que faz mais sentido aplicar o filtro na cláusula WHERE , eu estava apenas curioso quanto às implicações de desempenho.

TEMPO DECORRIDO ONDE CRITÉRIOS: 143016 ms
CRITÉRIOS DE JOGO DE TEMPO DECORRIDO : 143256

TESTE

 SET NOCOUNT ON; DECLARE @num INT, @iter INT SELECT @num = 1000, -- Number of records in TableA and TableB, the cross table is populated with a CROSS JOIN from A to B @iter = 1000 -- Number of select iterations to perform DECLARE @a TABLE ( id INT ) DECLARE @b TABLE ( id INT ) DECLARE @x TABLE ( aid INT, bid INT ) DECLARE @num_curr INT SELECT @num_curr = 1 WHILE (@num_curr <= @num) BEGIN INSERT @a (id) SELECT @num_curr INSERT @b (id) SELECT @num_curr SELECT @num_curr = @num_curr + 1 END INSERT @x (aid, bid) SELECT a.id, b.id FROM @aa CROSS JOIN @bb /* TEST */ DECLARE @begin_where DATETIME, @end_where DATETIME, @count_where INT, @begin_join DATETIME, @end_join DATETIME, @count_join INT, @curr INT, @aid INT DECLARE @temp TABLE ( curr INT, aid INT, bid INT ) DELETE FROM @temp SELECT @curr = 0, @aid = 50 SELECT @begin_where = CURRENT_TIMESTAMP WHILE (@curr < @iter) BEGIN INSERT @temp (curr, aid, bid) SELECT @curr, aid, bid FROM @aa INNER JOIN @xx ON a.id = x.aid INNER JOIN @bb ON x.bid = b.id WHERE a.id = @aid SELECT @curr = @curr + 1 END SELECT @end_where = CURRENT_TIMESTAMP SELECT @count_where = COUNT(1) FROM @temp DELETE FROM @temp SELECT @curr = 0 SELECT @begin_join = CURRENT_TIMESTAMP WHILE (@curr < @iter) BEGIN INSERT @temp (curr, aid, bid) SELECT @curr, aid, bid FROM @aa INNER JOIN @xx ON a.id = x.aid AND a.id = @aid INNER JOIN @bb ON x.bid = b.id SELECT @curr = @curr + 1 END SELECT @end_join = CURRENT_TIMESTAMP SELECT @count_join = COUNT(1) FROM @temp DELETE FROM @temp SELECT @count_where AS count_where, @count_join AS count_join, DATEDIFF(millisecond, @begin_where, @end_where) AS elapsed_where, DATEDIFF(millisecond, @begin_join, @end_join) AS elapsed_join 

Em termos de desempenho, eles são os mesmos (e produzem os mesmos planos)

Logicamente, você deve fazer a operação que ainda faz sentido se você replace INNER JOIN por um LEFT JOIN .

No seu caso, isso vai ficar assim:

 SELECT * FROM TableA a LEFT JOIN TableXRef x ON x.TableAID = a.ID AND a.ID = 1 LEFT JOIN TableB b ON x.TableBID = b.ID 

ou isto:

 SELECT * FROM TableA a LEFT JOIN TableXRef x ON x.TableAID = a.ID LEFT JOIN TableB b ON b.id = x.TableBID WHERE a.id = 1 

A consulta anterior não retornará nenhuma correspondência real para a.id diferente de 1 , portanto, a última syntax (com WHERE ) é logicamente mais consistente.

Para junções internas, não importa onde você coloca seus critérios. O compilador SQL transformará ambos em um plano de execução no qual a filtragem ocorre abaixo da junit (ou seja, como se as expressões de filtro aparecessem na condição de junit).

As junções externas são um assunto diferente, uma vez que o local do filtro altera a semântica da consulta.

Tanto quanto os dois methods vão.

  • JOIN / ON é para juntar tabelas
  • WHERE é para filtrar resultados

Enquanto você pode usá-los de forma diferente, parece sempre um cheiro para mim.

Lide com o desempenho quando é um problema. Então você pode olhar para essas “otimizações”.

Com qualquer otimizador de consulta worh um centavo …. eles são idênticos.

É realmente improvável que o posicionamento dessa junit seja o fator decisivo para o desempenho. Não estou intimamente familiarizado com o planejamento de execução do tsql, mas é provável que eles sejam otimizados automaticamente para planos semelhantes.

Regra nº 0: Execute alguns benchmarks e veja! A única maneira de realmente dizer qual será o mais rápido é tentar. Esses tipos de benchmarks são muito fáceis de executar usando o profiler SQL.

Além disso, examine o plano de execução para a consulta escrita com um JOIN e com uma cláusula WHERE para ver quais diferenças se destacam.

Finalmente, como outros já disseram, esses dois devem ser tratados de forma idêntica por qualquer otimizador decente, incluindo aquele embutido no SQL Server.

É mais rápido? Experimente e veja.

Qual é mais fácil de ler? O primeiro para mim parece mais “correto”, já que a condição movida não tem nada a ver com a junit.

Eu acho que o primeiro, porque faz um filtro mais específico sobre os dados. Mas você deve ver o plano de execução , como acontece com qualquer otimização, porque pode ser muito diferente dependendo do tamanho dos dados, do hardware do servidor, etc.