sqlserver - top sql performance

Sorprendente aumento de velocidad SQL (7)

¿Por qué dices que son idénticos? Podrían resolver el mismo problema, pero su enfoque es diferente. Al menos parece que ...

La consulta que usa LEFT optimiza la prueba, ya que ya conoce la longitud del prefijo y etc., por lo que en un programa C / C ++ / ... o sin un índice, un algoritmo que use IZQUIERDA para implementar un cierto comportamiento LIKE sería el más rápido. Pero contrastado con la mayoría de los lenguajes no declarativos, en una base de datos SQL, se realizan muchas optimizaciones de operaciones para usted. Por ejemplo, LIKE probablemente se implemente buscando primero el signo% y si se observa que% es el último carácter en la cadena, la consulta se puede optimizar de la misma manera que con LEFT, pero directamente usando un índice .

Entonces, de hecho, creo que tenías razón después de todo, probablemente sean idénticos en su enfoque. La única diferencia es que el servidor db puede usar un índice en la consulta utilizando LIKE porque no hay una función que transforme el valor de la columna en algo desconocido en la cláusula WHERE.

Acabo de descubrir que el rendimiento del plan de ejecución entre las siguientes dos sentencias seleccionadas es enormemente diferente:

select * from your_large_table where LEFT(some_string_field, 4) = ''2505'' select * from your_large_table where some_string_field like ''2505%''

Los planes de ejecución son 98% y 2% respectivamente. Una pequeña diferencia en velocidad entonces. Estaba realmente sorprendido cuando lo vi.

Siempre he hecho IZQUIERDA (xxx) = ''yyy'', ya que se lee bien. De hecho, descubrí esto al verificar el SQL generado por LINQ contra mi SQL hecho a mano. Supuse que el comando LIKE sería más lento, pero de hecho es mucho más rápido.

Mi pregunta es por qué la IZQUIERDA () es más lenta que la LIKE ''% ..''. ¿Después de todo, son idénticos?

Además, ¿hay un CPU golpeado usando LEFT ()?

En términos más generales, nunca se debe usar una función en el lado IZQUIERDO de una cláusula WHERE en una consulta. Si lo hace, SQL no usará un índice; tiene que evaluar la función para cada fila de la tabla. El objetivo es asegurarse de que su cláusula Where sea " Sargable "

Algunos otros ejemplos:

Bad: Select ... WHERE isNull(FullName,'''') = ''Ed Jones'' Fixed: Select ... WHERE ((FullName = ''Ed Jones'') OR (FullName IS NULL)) Bad: Select ... WHERE SUBSTRING(DealerName,4) = ''Ford'' Fixed: Select ... WHERE DealerName Like ''Ford%'' Bad: Select ... WHERE DateDiff(mm,OrderDate,GetDate()) >= 30 Fixed: Select ... WHERE OrderDate < DateAdd(mm,-30,GetDate()) Bad: Select ... WHERE Year(OrderDate) = 2003 Fixed: Select ... WHERE OrderDate >= ''2003-1-1'' AND OrderDate < ''2004-1-1''

Hay un gran impacto en el uso de llamadas a funciones en donde las cláusulas como SQL Server deben calcular el resultado para cada fila. Por otro lado, al like que una función de lenguaje integrado que está altamente optimizado.

Lo que sucedió aquí es que el RDBMS no es capaz de usar un índice en el predicado LEFT () y es capaz de usarlo en LIKE, o simplemente hizo la llamada incorrecta en la que sería el método de acceso más apropiado.

En primer lugar, puede ser cierto para algunos SGBDR que la aplicación de una función a una columna impide que se use un método de acceso basado en índices, pero esa no es una verdad universal, ni existe ninguna razón lógica por la que deba serlo. Un método de acceso basado en índice (como el análisis de índice completo de Oracle o el análisis de índice completo rápido) podría ser beneficioso, pero en algunos casos el RDBMS no es capaz de la operación en el contexto de un predicado basado en funciones.

En segundo lugar, el optimizador puede simplemente equivocarse en la aritmética al estimar los beneficios de los diferentes métodos de acceso disponibles. Suponiendo que el sistema puede realizar un método de acceso basado en índices, primero tiene que hacer una estimación del número de filas que coincidirán con el predicado, ya sea a partir de las estadísticas en la tabla, estadísticas en la columna, muestreando los datos en el tiempo de análisis, o estar usando una regla heurística (por ejemplo, "suponga que el 5% de las filas coincidirán"). Luego tiene que evaluar los costos relativos de una exploración de tabla completa o los métodos basados en índices disponibles. Algunas veces obtendrá la aritmética incorrecta, a veces las estadísticas serán engañosas o inexactas, y algunas veces las reglas heurísticas no serán apropiadas para el conjunto de datos.

El punto clave es estar al tanto de una serie de problemas:

¿Qué operaciones puede soportar su RDBMS?
¿Cuál sería la operación más adecuada en el caso de que esté trabajando?
¿La elección del sistema es correcta?
¿Qué se puede hacer para permitir que el sistema realice una operación más eficiente (por ejemplo, agregar una restricción faltante nula, actualizar las estadísticas, etc.)?

En mi experiencia, esta no es una tarea trivial, y a menudo es mejor dejarla a los expertos. O, por otro lado, solo publique el problema en : algunos de nosotros encuentran fascinante esto, ayúdennos.

Parece que la expresión LEFT (some_string_field, 4) se evalúa para cada fila de una exploración de tabla completa, mientras que la expresión "like" usará el índice.

La optimización de "me gusta" para usar un índice si es un patrón anclado de frente es una optimización mucho más fácil que el análisis de expresiones arbitrarias que involucran funciones de cadena.

Si usa una función en una columna con un índice, entonces el db ya no usa el índice (al menos con Oracle de todos modos)
Así que supongo que su campo de ejemplo '' some_string_field '' tiene un índice que no se usa para la consulta con ''LEFT''

Como se menciona en @BradC, no debe usar funciones en una cláusula WHERE si tiene índices y desea aprovecharlos.

Si lee la sección titulada "Use LIKE en lugar de LEFT () o SUBSTRING () en las cláusulas WHERE cuando los índices están presentes" de estas Sugerencias de rendimiento de SQL , hay más ejemplos.

También hace alusión a las preguntas que encontrará en los exámenes de MCSE SQL Server 2012 si le interesa tomarlas también. :-)