Pregunta ¿Importa el orden de las columnas en una cláusula WHERE?


¿El orden de las columnas en una cláusula WHERE afecta el rendimiento?

p.ej.

Supongamos que pongo una columna que tiene un mayor potencial de singularidad primero o viceversa.


32
2018-03-13 13:46


origen


Respuestas:


Con un optimizador de consultas decente: no debería.

Pero en la práctica, sospecho que podría.

Solo puedes determinar tus casos midiendo. Y las medidas probablemente cambien a medida que la distribución de datos cambie en la base de datos.


15
2018-03-13 13:48



Para Transact-SQL existe una precedencia definida para los operadores en el condición de la cláusula WHERE. El optimizador puede volver a ordenar esta evaluación, por lo que no debe confiar en el comportamiento de cortocircuito para la corrección. El orden generalmente es de izquierda a derecha, pero la selectividad / disponibilidad de los índices probablemente también sea importante. La simplificación de su condición de búsqueda debería mejorar la capacidad del optimizador para manejarlo.

Ex:

 WHERE (a OR b) AND (b OR c)

podría simplificarse a

 WHERE b OR (a AND c)

Claramente, en este caso, si la consulta se puede construir para encontrar si b se mantiene primero, es posible que pueda omitir la evaluación de ayc y, por lo tanto, se ejecute más rápido. Si el optimizador puede hacer esta simple transformación, no puedo responder (puede hacerlo), pero el hecho es que probablemente no pueda realizar transformaciones arbitrariamente complejas y es posible que pueda efectuar el rendimiento de la consulta reorganizando su condición. Si b es más selectivo o tiene un índice, es probable que el optimizador pueda construir una consulta que lo use primero.

EDITAR: Con respecto a su pregunta sobre pedidos basada en la singularidad, supongo que cualquier sugerencia que pueda proporcionar al optimizador en función de su conocimiento (real, no asumido) de los datos no podría perjudicar. Supongamos que no hará ninguna optimización y construirá su consulta como si necesitara definirla de mayor a menor selectividad, pero no se obsesione hasta que el rendimiento sea realmente un problema.

Citando de la referencia anterior:

El orden de precedencia para los operadores lógicos NO es (más alto),   seguido por AND, seguido por OR. Los paréntesis se pueden usar para anular   esta precedencia en una condición de búsqueda. El orden de evaluación de   los operadores lógicos pueden variar según las elecciones realizadas por la consulta   optimizador.


12
2018-03-13 14:15



Para SQL Server 2000/20005/2008, el optimizador de consultas generalmente le dará resultados idénticos sin importar cómo organice las columnas en la cláusula WHERE. Habiendo dicho esto, a lo largo de los años de escribir miles de comandos T-SQL he encontrado algunos casos de esquina donde el orden alteró el rendimiento. Estas son algunas de las características de las consultas que parecían estar sujetas a este problema:

  1. Si tiene una gran cantidad de tablas en su consulta (10 o más).

  2. Si tiene varias instrucciones EXISTS, IN, NOT EXISTS o NOT IN en su cláusula WHERE

  3. Si está utilizando CTE anidados (expresiones de tabla común) o una gran cantidad de CTE.

  4. Si tiene una gran cantidad de subconsultas en su cláusula FROM.

Aquí hay algunos consejos para tratar de evaluar la mejor manera de resolver el problema de rendimiento rápidamente:

  1. Si el problema está relacionado con 1 o 2, intente reordenar la cláusula WHERE y compare el costo del subárbol de las consultas en los planes de consulta estimados.

  2. Si el problema está relacionado con 3 o 4, intente mover las consultas secundarias y CTE de la consulta y haga que carguen tablas temporales. El optimizador de plan de consulta es mucho más eficiente para estimar planes de consulta si reduce la cantidad de uniones complejas y subconsultas del cuerpo de la instrucción T-SQL.

  3. Si está utilizando tablas temporales, asegúrese de haber especificado las claves principales para las tablas temporales. Esto significa evitar el uso de SELECT INTO FROM para generar la tabla. En su lugar, cree explícitamente la tabla y especifique una LLAVE primaria antes de usar una instrucción INSERT INTO SELECT.

  4. Si está utilizando tablas temporales y MUCHOS procesos en el servidor también usan tablas temporales, entonces puede querer crear una tabla de etapas más permanente que se trunque y vuelva a cargar durante el proceso de consulta. Es más probable que encuentre problemas de contención del disco si está utilizando TempDB para almacenar sus tablas de trabajo / etapas.

  5. Mueva las instrucciones en la cláusula WHERE que filtrará la mayor cantidad de datos al principio de la cláusula WHERE. Tenga en cuenta que si esta es su solución al problema, es probable que tenga un rendimiento bajo nuevamente cuando el plan de consulta se vuelva a confundir sobre la generación y elección del mejor plan de ejecución. Es mejor que encuentre una forma de reducir la complejidad de la consulta para que el orden de la cláusula WHERE ya no sea relevante.

Espero que esta información te sea útil. ¡Buena suerte!


7
2018-03-13 15:31



Todo depende del DBMS, el optimizador de consultas y las reglas, pero generalmente afecta el rendimiento.

Si se ordena una cláusula where de modo que la primera condición reduzca significativamente el resultado, las condiciones restantes solo deberán evaluarse para un conjunto más pequeño. Siguiendo esa lógica, puede optimizar una consulta basada en el orden de condición en una cláusula where.


2
2018-03-13 13:55



En teoria cualquier dos consultas que sean equivalentes deberían producir planes de consulta idénticos. Como el orden de WHERE cláusulas no tiene ningún efecto sobre el significado lógico de la consulta, esto debería significar que el orden de la WHERE la cláusula no debería tener ningún efecto.

Esto se debe a la forma en que funciona el optimizador de consultas. en un muy simplificado visión de conjunto:

  1. El primer SQL Server analiza la consulta y construye un árbol de operadores lógicos (p. Ej. JOIN o SELECT)
  2. A continuación, traduce estos operadores lógicos en un "árbol de operaciones físicas" (por ejemplo, "bucles anidados" o "exploración de índice", es decir, un plan de ejecución)
  3. Luego permuta a través del conjunto de "árboles de operaciones físicas" equivalentes (es decir, planes de ejecución) intercambiando operaciones equivalentes, estimando el costo de cada plan hasta que encuentre el óptimo.

El segundo paso es una forma completamente de nieve: simplemente elige el primer / el árbol físico más obvio que puede, sin embargo, en el 3er paso, el optimizador de consultas puede ver a través de todas árboles físicos equivalentes (es decir, planes de ejecución), y siempre que las consultas sean equivalentes, no importa qué plan inicial obtengamos en el paso 2, el conjunto de planes que todos los planes deben considerarse en el paso 3 es el mismo.

(No recuerdo los nombres reales de los árboles lógicos / físicos, están en un libro, pero desafortunadamente el libro es el otro lado del mundo para mí en este momento)

Consulte la siguiente serie de artículos de blog para obtener más detalles Dentro del Optimizador: Construyendo un Plan - Parte 1

En realidad Sin embargo, a menudo el optimizador de consultas no tiene la oportunidad de considerar todas árboles equivalentes en el paso 3 (para consultas complejas puede haber una gran cantidad de planes posibles), y luego de un cierto tiempo de corte, el paso 3 se acorta y el optimizador de consultas tiene que elegir el mejor plan que haya encontrado hasta ahora - en este caso no todas planes serán considerados.

Hay una gran cantidad detrás de la magia de sceene que asegura que el optimizador de consultas elige selectiva e inteligentemente los planes a considerar, por lo que la mayoría de las veces el plan elige "lo suficientemente bueno", incluso si no es el plan más rápido absoluto, es probable que no sea mucho más lento que el teórico más rápido,

Sin embargo, esto significa que si tenemos un plan inicial diferente en el paso 2 (lo que podría suceder si escribimos nuestra consulta de manera diferente), esto significa potencialmente que se considera un subconjunto diferente de planes en el paso 3, y así En teoria SQL Server puede generar diferentes planes de consulta para consultas equivalentes según la forma en que se escribieron.

En realidad, sin embargo, el 99% de las veces no notarás la diferencia (para muchos planes simples no ser cualquier diferencia, ya que el optimizador realmente considerará todos los planes). Además, no se puede predecir cómo va a funcionar esto y qué cosas pueden parecer sensatas (como poner el WHERE cláusulas en un cierto orden), podría no tener nada como el efecto esperado.


2
2018-04-11 11:38



En el amplia mayoría de los casos, el optimizador de consultas determinará la forma más eficiente de seleccionar los datos que ha solicitado, independientemente del orden del SARGS definido en la cláusula WHERE.

El orden está determinado por factores tales como la selectividad de la columna en cuestión (que SQL Server conoce en función de las estadísticas) y si se pueden usar índices.


0
2018-03-13 15:07



Si está cumpliendo con las condiciones AND, el primero no es verdadero devolverá falso, por lo que el orden puede afectar el rendimiento.


-4
2018-03-13 13:49