Criterios de filter SQL en criterios de unión o cláusula where que es más eficiente

Tengo una consulta relativamente simple que une dos tablas. Los criterios "Dónde" se pueden express en los criterios de unión o como cláusula where. Me pregunto cuál es más eficiente.

La consulta consiste en encontrar las ventas máximas para un vendedor desde el principio hasta que se hayan promocionado.

Caso 1

select salesman.salesmanid, max(sales.quantity) from salesman inner join sales on salesman.salesmanid =sales.salesmanid and sales.salesdate < salesman.promotiondate group by salesman.salesmanid 

Caso 2

 select salesman.salesmanid, max(sales.quantity) from salesman inner join sales on salesman.salesmanid =sales.salesmanid where sales.salesdate < salesman.promotiondate group by salesman.salesmanid 

Nota El caso 1 carece de una cláusula where

RDBMS es Sql Server 2005

EDITAR Si la segunda parte de los criterios de unión o la cláusula where era sales.salesdate <alguna date fija así que no es realmente ningún criterio para unir las dos tablas, eso cambia la respuesta.

No usaría el performance como el factor decisivo aquí, y sinceramente, no creo que haya ninguna diferencia de performance mensurable entre esos dos casos, realmente.

Siempre usaría el caso n. ° 2: ¿por qué? Porque, en mi opinión, solo debe poner los criterios reales que establecen el JOIN entre las dos tablas en la cláusula JOIN, todo lo demás pertenece a la cláusula WHERE.

Solo una cuestión de mantener las cosas limpias y poner las cosas donde pertenecen, IMO.

Obviamente, hay casos con LEFT OUTER JOINs donde la colocación de los criterios hace una diferencia en términos de los resultados que se devuelven; esos casos se excluirán de mi recomendación, por supuesto.

Bagazo

Puede ejecutar el estimador del plan de ejecución y el perfilador SQL para ver cómo se comparan entre sí.

Sin embargo, son semánticamente iguales bajo el capó según este MVP de SQL Server:

http://www.eggheadcafe.com/conversation.aspx?messageid=29145383&threadid=29145379

Prefiero tener cualquier criterio codificado en la unión. Hace que SQL sea mucho más legible y portátil.

Legibilidad: puede ver exactamente qué datos va a get porque todos los criterios de la tabla están escritos allí en la unión. En declaraciones grandes, los criterios pueden estar ocultos dentro de otras 50 expresiones y se pueden perder fácilmente.

Portabilidad: solo puede copyr un fragment de la cláusula FROM y pegarlo en otro lugar. Eso le da las uniones y cualquier criterio que necesite para lograrlo. Si siempre usa ese criterio al unir esas dos tablas, ponerlo en la unión es lo más lógico.

Por ejemplo:

 FROM table1 t1 JOIN table2 t2_ABC ON t1.c1 = t2_ABC.c1 AND t2_ABC.c2 = 'ABC' 

Si necesita get una segunda columna de la tabla 2, simplemente copie ese bloque en el Bloc de notas, busque / repalce "ABC" y listo, y bloque completo nuevo de código listo para volver a pegar.

Adicional: También es más fácil cambiar entre una combinación interna y externa sin tener que preocuparse por ningún criterio que pueda estar flotando en la cláusula WHERE.

Me reservo la cláusula WHERE estrictamente para los criterios de time de ejecución cuando sea posible.

En cuanto a la eficiencia: si se refiere a la velocidad de ejecución, entonces, como todos han declarado, es networkingundante. Si se refiere a una debugging y reutilización más sencilla, entonces prefiero la opción 1.

No creo que encuentres una respuesta finita para esta que se aplique a todos los casos. Los 2 no siempre son intercambiables, ya que para algunas consultas (algunas combinaciones a la izquierda) se obtendrán resultados diferentes al colocar los criterios en la línea DONDE vs la línea DE.

En su caso, debe evaluar ambas consultas. En SSMS, puede ver los planes de ejecución reales y estimados de ambas consultas; ese sería un buen primer paso para determinar cuál es más óptimo. También podría ver el time y el IO para cada uno (establecer el time de las statistics activado, establecer las statistics) y eso también le dará información para tomar su decisión.

En el caso de las consultas en su pregunta, apostaría a que ambos obtendrán el mismo plan de consulta, por lo que en este caso puede que no importe, pero en otros podría generar diferentes planes.

Intenta esto para ver la diferencia entre los 2 …

 SET STATISTICS IO ON SET STATISTICS TIME ON select salesman.salesmanid, max(sales.quantity) from salesmaninner join sales on salesman.salesmanid =sales.salesmanid and sales.salesdate < salesman.promotiondate group by salesman.salesmanid select salesman.salesmanid, max(sales.quantity) from salesmaninner join sales on salesman.salesmanid = sales.salesmanid where sales.salesdate < salesman.promotiondate group by salesman.salesmanid SET STATISTICS TIME OFF SET STATISTICS IO OFF 

Una cosa que quiero decir, finalmente, tal como lo notifiqué, antes de eso … Ambas forms pueden dar el mismo performance o usar los criterios en la cláusula Where, que pueden ser un poco más rápidos, como se encuentra en algunas respuestas.

Pero identifiqué una diferencia, que puede usar para sus necesidades lógicas …

  1. El uso de los criterios en la cláusula ON no filtrará / saltará las filas para seleccionar; en cambio, las columnas de combinación serían nulas en function de las condiciones

  2. El uso de los criterios en la cláusula Where puede filtrar / omitir las filas en los resultados completos

Puede parecer frívolo, pero la respuesta es cualquiera de las consultas para las cuales el analizador de consultas produce el plan más eficiente.

En mi opinión, parecen ser equivalentes, por lo que el analizador de consultas puede producir planes idénticos, pero tendrías que probar.

Ninguno de los dos es más eficiente, ya que el método WHERE se considera la forma antigua de hacerlo ( http://msdn.microsoft.com/en-us/library/ms190014.aspx ). Puedes mirar el plan de ejecución y ver que hagan lo mismo.

¡Familiarícese con el plan de ejecución estimado en SQL Management Studio! Como han dicho otros, está a merced del analizador sin importar lo que haga, así que confíe en sus estimaciones. Supongo que los dos que proporcionaste producirán exactamente el mismo plan.

Si se trata de un bash de cambiar una cultura de desarrollo, elija la que le brinde un mejor plan; para los que son idénticos, sigue la cultura

He comentado esto en otras publicaciones de "eficiencia" como esta (es sincero y sarcástico), si es allí donde residen sus cuellos de botella, entonces chocan cinco contra usted y su equipo.

El caso 1 (criterios en el JOIN) es mejor para la encapsulación, y una mayor encapsulación suele ser algo bueno: disminuyó las omisiones de copyr / pegar a otra consulta, disminuyó errores si luego se convirtió a LEFT JOIN y aumentó la legibilidad (cosas relacionadas juntas y less " ruido "en la cláusula WHERE). En este caso, la cláusula WHERE solo captura los criterios o criterios de la tabla principal que abarcan varias tablas.