rendimiento rapidas queries plan optimizar optimización mejorar mas inner ejecución consultas sql-server optimization

rapidas - ¿Cuáles son las mejores técnicas de optimización de rendimiento de SQL Server?



optimizar inner join sql server (14)

"La optimización temprana es la raíz de todo mal"

En términos de programación de bases de datos, creo que esta cita no tiene sentido. Es extremadamente costoso volver a escribir toda la aplicación porque a los desarrolladores no les importa escribir un código eficiente la primera vez. Se debe pensar en todos los códigos t-sql en términos de cómo afectará el rendimiento de la base de datos en segundo lugar (la integridad de los datos es, por supuesto, primero). El rendimiento debe triunfar sobre todo excepto la integridad de datos.

Sí, hay cosas de optimización que no debes hacer hasta que tengas problemas, pero algunas cosas se deben hacer de forma rutinaria y no se corrigen más tarde. No se necesita más tiempo para escribir un código que tenga más posibilidades de ser eficiente que un código que no será una vez que comprenda cómo está afectando la eficiencia con el código incorrecto. La discusión de Cervo sobre el código del cursor es un ejemplo. Las acciones basadas en conjunto son casi siempre mucho más rápidas que las soluciones de cursor, por lo que los cursores no deberían escribirse inicialmente cuando una solución basada en conjunto lo haga. Casi siempre me toma menos tiempo escribir una solución basada en conjuntos que escribir un cursor, pero la única manera de hacerlo es escribir nunca los cursores.

Y no hay ninguna razón para usar select * en lugar de especificar sus nombres de campo. En MSSQL puedes arrastrar esos nombres desde el explorador de objetos para que no puedas decirme que es muy difícil hacerlo. Pero al especular solo con los campos que realmente necesita, ahorra recursos de red y recursos del servidor de bases de datos y recursos del servidor web. Entonces, ¿por qué un programador debe tomar la opción perezosa de seleccionar * y preocuparse por la optimización posterior?

Lo mismo con los índices. Usted dice que hace un conjunto mínimo de índices. Dependiendo de cómo defina el mínimo, podría estar bien, pero es fundamental tener índices en todas las claves externas y no me gustaría insertar una base de datos que no tuviera índices en algunos campos que están más a menudo en el cláusulas. Si sus usuarios son clientes externos y no internos, no se quejarán de lo lento que es su sitio, se irán a otro lado. Solo tiene sentido tener la capacidad de planificar el acceso eficiente a la base de datos desde el principio.

Una de mis principales preocupaciones acerca de no considerar la eficiencia desde el principio es que las primeras veces que las cosas son demasiado lentas las empresas tienden a simplemente tirar más equipo al tema que a la melodía de rendimiento. En el momento en que la gente comienza a ajustar el rendimiento, tiene una base de datos de varios gigabytes o más con muchos clientes descontentos que están obteniendo tiempos de espera más que resultados. En este punto, a menudo casi todo en la base de datos debe ser reescrito y, mientras tanto, está perdiendo clientes. Recuerdo haber brindado soporte en una compañía con una aplicación comercial, literalmente demoró diez minutos para que los representantes del servicio al cliente pasaran de una pantalla a otra mientras intentaban ayudar a los clientes que ya estaban disgustados por teléfono. Puede imaginar cuántos clientes perdió la empresa debido a consultas de bases de datos mal diseñadas en el producto comercial que no pudimos cambiar.

Siempre he adoptado el enfoque de implementar primero la base de datos con un conjunto mínimo de índices y luego agregar / cambiar índices según lo dicte el rendimiento.

Este enfoque funciona razonablemente bien. Sin embargo, todavía no me dice dónde podría mejorar el rendimiento. Solo me dice que el rendimiento es tan malo que los usuarios se quejan de él.

Actualmente, estoy en el proceso de refactorizar objetos de base de datos en muchas de nuestras aplicaciones.

Entonces, ¿no debería molestarme en buscar mejoras en el rendimiento ya que "la optimización prematura es la raíz de todo mal"?

Al refaccionar el código de la aplicación, el desarrollador busca constantemente formas de mejorar la calidad del código. ¿Hay alguna manera de buscar constantemente mejoras en el rendimiento de la base de datos también? Si es así, ¿qué herramientas y técnicas ha encontrado que son más útiles?

He jugado brevemente con el "Asesor de ajuste de motor de base de datos", pero no creo que sea útil en absoluto. Tal vez solo necesito más experiencia interpretando los resultados.

Asegúrese de que esté utilizando perfiles utilizando volúmenes de producción, en términos de número de filas y carga. Las consultas y sus planes se comportan de forma diferente bajo diferentes escenarios de carga / volumen

Después de su perfil, coloque las consultas que ve como problemáticas en el Analizador de consultas SQL y muestre el plan de ejecución. Identifique porciones de las consultas que realizan escaneos costosos de tablas y vuelva a indexar esas tablas para minimizar este costo.

Pruebe estas referencias:

Optimizando SQL
Cómo Optimizar Consultas

Es posible que desee comprobar la fragmentación interna y externa de los índices actuales y soltarlos, recrearlos o reorganizarlos.

La creación de perfiles es clave, pero cuando se usa un conjunto de perfiles, DEBE asegurarse de que sea un conjunto de datos de prueba preciso, de lo contrario, las herramientas de ajuste no podrán obtener un resultado preciso de lo que se necesita.

¡También los objetos de administración con fragmentación y un informe de uso en 2005 son muy útiles!

Mi enfoque es reunir comandos contra el servidor o la base de datos en una tabla usando SQL Server Profiler. Una vez que tenga eso, puede realizar consultas en función de los tiempos de ejecución max y avg, los tiempos de CPU max y avg, y (también muy importante) el número de veces que se ejecutó la consulta.

Como trato de poner todo el código de acceso a la base de datos en procedimientos almacenados, es fácil para mí desglosar las consultas. Si usa SQL en línea podría ser más difícil, ya que un cambio en un valor en la consulta lo haría parecer una consulta diferente. Puede intentar solucionar esto utilizando el operador LIKE para colocar los mismos tipos de consultas en los mismos contenedores para calcular los agregados (máximo, promedio, recuento).

Una vez que tenga una lista de los "10 principales" problemas potenciales, puede comenzar a verlos individualmente para ver si la consulta puede ser modificada, un índice puede ayudar o hacer un cambio menor en la arquitectura. Para llegar al top 10, intente ver los datos de diferentes maneras: promedio * para el costo total durante el período, máximo para el peor delincuente, simplemente promedio, etc.

Finalmente, asegúrese de monitorear durante diferentes períodos de tiempo si es necesario. El uso de la base de datos puede ser diferente en la mañana cuando todos están ingresando y ejecutando sus informes diarios que a mediodía cuando los usuarios ingresan datos nuevos. También puede decidir que, aunque un proceso nocturno lleva más tiempo que cualquier otra consulta, no importa, ya que se ejecuta fuera de horario.

¡Buena suerte!

Parece que estás hablando de MS SQL.

Inicie el generador de perfiles y registre las consultas más comunes que ejecuta en la base de datos. Luego ejecute esas consultas con el plan de ejecución activado y verá qué (si hay algo) está desacelerando sus consultas. A continuación, puede ir y optimizar las consultas o agregar más índices en sus campos.

Los libros SQL le brindarán una buena visión general de la funcionalidad de análisis de perfiles y consultas.

Por supuesto, tienes que perfilar tus consultas y mirar el plan de ejecución. Pero las dos cosas principales que aparecen una y otra vez son filtrar tanto como puedas tan pronto como puedas y tratar de evitar los cursores.

Vi una aplicación en la que alguien descargó una tabla completa de eventos de la base de datos a un cliente y luego revisó cada fila uno a uno según algunos criterios. Hubo un ENORME aumento en el rendimiento al pasar los criterios de filtro a la base de datos y hacer que la consulta aplicara los criterios en una cláusula where. Esto es obvio para las personas que trabajan con bases de datos, pero he visto surgir cosas similares. Además, algunas personas tienen consultas que almacenan un conjunto de tablas temporales llenas de filas que no necesitan, que luego se eliminan en una combinación final de las tablas temporales. Básicamente, si elimina de las consultas que pueblan las tablas temporales, hay menos datos para el resto de la consulta y toda la consulta se ejecuta más rápido.

Los cursores son obvios. Si tienes un millón de filas e irás fila por fila, tardarás una eternidad. Si realiza algunas pruebas, si se conecta a una base de datos incluso con un lenguaje dinámico "lento" como Perl y realiza una operación fila a fila en un conjunto de datos, la velocidad seguirá siendo mucho mayor que la de un cursor en la base de datos. Hazlo con algo como Java / C / C ++ y la diferencia de velocidad es aún mayor. Si puede encontrar / eliminar un cursor en el código de la base de datos, se ejecutará mucho más rápido ... Si debe usar un cursor, reescribir esa parte en cualquier lenguaje de programación y sacarla de la base de datos probablemente genere un gran aumento en el rendimiento.

Una nota más sobre los cursores, tenga cuidado con el código como SELECT @ col1 = col1, @ col2 = col2, @ col3 = col3 donde id = @currentid en un bucle que pasa por los ID y luego ejecuta las declaraciones en cada columna. Básicamente esto es un cursor también. No solo eso, sino que el uso de cursores reales suele ser más rápido que esto, especialmente estático y forward_only. Si puede cambiar la operación para establecerla, será mucho más rápido ... Dicho esto, los cursores tienen cabida para algunas cosas ... pero desde el punto de vista del rendimiento, existe una penalización al usarlas en un conjunto basado en enfoques.

También ten cuidado con el plan de ejecución. En ocasiones, estima que las operaciones que toman segundos son muy costosas y las operaciones que toman minutos son muy baratas. Al ver un plan de ejecución, asegúrese de verificar todo insertando SELECCIONAR ''En esta área'', OBTENER () en su código.

perfila tus consultas, no las obvias, sino el complejo que accede a diferentes tablas, vistas, etc. y / o aquellas que devuelven muchas filas de diferentes tablas

Eso te dirá exactamente dónde deberías enfocarte

Mi consejo sería comenzar con las técnicas aplicables a todas las bases de datos y luego probar las específicas de MsSQL.

Optimizar SQL es difícil, y no hay reglas duras y rápidas. Existen muy pocas pautas genéricas que puede seguir, como:

  • El 95% de las mejoras de rendimiento provendrán de la aplicación, no de la configuración del motor del servidor o la base de datos.
  • Primero, diseñe la corrección, ajuste para un rendimiento posterior
  • Reducir viajes a la base de datos
  • Intenta expresar las cosas de una manera que se adapte a tu modelo de datos
  • Ignore los consejos genéricos sobre el rendimiento: sí, en algún momento encontrará un sistema o declaración de SQL donde una de esas reglas no se aplica.

Pero el punto clave es que siempre debes aplicar la regla 80-20. Lo que significa que en cualquier sistema necesita ajustar un 20% (a menudo mucho menos) de su código para las mayores ganancias de rendimiento. Ahí es donde las herramientas proporcionadas por el proveedor generalmente fallan , ya que generalmente no pueden adivinar el contexto de aplicación / negocio de la ejecución.

Aplicar una indexación adecuada en las columnas de la tabla en la base de datos

  • Asegúrese de que todas las tablas en su base de datos tengan una clave principal.

Esto asegurará que cada tabla tenga un índice agrupado creado (y, por lo tanto, las páginas correspondientes de la tabla se clasifican físicamente en el disco de acuerdo con el campo de clave principal). Entonces, cualquier operación de recuperación de datos de la tabla usando la clave primaria, o cualquier operación de clasificación en el campo de clave primaria o cualquier rango de valores de clave primaria especificados en la cláusula where recuperará datos de la tabla muy rápido.

  • Crear índices no agrupados en columnas que son

    Frecuentemente utilizado en los criterios de búsqueda.

    Usado para unir otras tablas.

    Usado como campos clave foráneos.

    De tener alta selectividad (columna que devuelve un bajo porcentaje (0-5%) de filas de un número total de filas en un valor particular).

    Usado en la cláusula ORDER BY.

No use "SELECT *" en una consulta SQL

Se pueden obtener columnas innecesarias que agregarán gastos al tiempo de recuperación de datos. El motor de la base de datos no puede utilizar el beneficio del "Índice cubierto" y, por lo tanto, la consulta se realiza lentamente.

Ejemplo:

SELECT Cash, Age, Amount FROM Investments;

En lugar de:

SELECT * FROM Investments;

Intenta evitar la cláusula HAVING en las declaraciones Select

La cláusula HAVING se utiliza para filtrar las filas después de seleccionar todas las filas y se utiliza como un filtro. Intenta no usar la cláusula HAVING para ningún otro propósito.

Ejemplo:

SELECT Name, count (Name) FROM Investments WHERE Name!= ‘Test’ AND Name!= ‘Value’ GROUP BY Name;

En lugar de:

SELECT Name, count (Name) FROM Investments GROUP BY Name HAVING Name!= ‘Test’ AND Name!= ‘Value’ ;

Intenta minimizar el número de bloques de sub consulta dentro de una consulta

A veces podemos tener más de una sub consulta en nuestra consulta principal. Deberíamos tratar de minimizar el número de bloque de sub consulta en nuestra consulta.

Ejemplo:

SELECT Amount FROM Investments WHERE (Cash, Fixed) = (SELECT MAX (Cash), MAX (Fixed) FROM Retirements) AND Goal = 1;

En lugar de:

SELECT Amount FROM Investments WHERE Cash = (SELECT MAX (Cash) FROM Retirements) AND Fixed = (SELECT MAX (Fixed) FROM Retirements) AND Goal = 1;

Evite columnas innecesarias en la lista SELECT y tablas innecesarias en condiciones de unión

Seleccionar columnas innecesarias en una consulta Select agrega sobrecarga a la consulta real, especialmente si las columnas innecesarias son de tipos LOB. La inclusión de tablas innecesarias en condiciones de combinación obliga al motor de la base de datos a recuperar y recuperar datos innecesarios y aumenta el tiempo de ejecución de la consulta.

No use el agregado COUNT () en una subconsulta para realizar una comprobación de existencia

Cuando usa COUNT (), SQL Server no sabe que está realizando una comprobación de existencia. Cuenta todos los valores coincidentes, ya sea haciendo un escaneo de tabla o escaneando el índice no agrupado más pequeño. Cuando utiliza EXISTS, SQL Server sabe que está realizando una comprobación de existencia. Cuando encuentra el primer valor coincidente, devuelve TRUE y deja de buscar.

Intenta evitar unir dos tipos de columnas

Al unir dos columnas de diferentes tipos de datos, una de las columnas debe convertirse al tipo de la otra. La columna cuyo tipo es más bajo es la que se convierte. Si une tablas con tipos incompatibles, una de ellas puede usar un índice, pero el optimizador de consultas no puede elegir un índice en la columna que convierte.

Intente no usar COUNT (*) para obtener el recuento de registros en una tabla

Para obtener el recuento total de filas en una tabla, generalmente usamos la siguiente instrucción Select:

SELECT COUNT(*) FROM [dbo].[PercentageForGoal]

Esta consulta realizará un escaneo completo de la tabla para obtener el recuento de filas. La siguiente consulta no requerirá un escaneo de tabla completo. (Tenga en cuenta que esto puede no proporcionarle resultados 100% perfectos siempre, pero esto es útil solo si no necesita un recuento perfecto).

SELECT rows FROM sysindexes WHERE id = OBJECT_ID(''[dbo].[PercentageForGoal]'') AND indid< 2

Intente utilizar operadores como EXISTS, IN y JOINS de manera apropiada en su consulta

  • Por lo general, IN tiene el rendimiento más lento.
  • IN es eficiente, solo cuando la mayoría de los criterios de filtro para la selección se colocan en la subconsulta de una declaración de SQL.
  • EXISTS es eficiente cuando la mayoría de los criterios de filtro para la selección se encuentra en la consulta principal de una declaración de SQL.

Intenta evitar el SQL dinámico

A menos que sea realmente necesario, intente evitar el uso de SQL dinámico porque: SQL dinámico es difícil de depurar y solucionar. Si el usuario proporciona la entrada al SQL dinámico, existe la posibilidad de ataques de inyección SQL.

Intenta evitar el uso de tablas temporales

A menos que sea realmente necesario, trate de evitar el uso de tablas temporales. Más bien use variables de tabla. En el 99% de los casos, las variables de tabla residen en la memoria, por lo tanto, es mucho más rápido. Las tablas temporales residen en la base de datos TempDb. Entonces, operar en tablas temporales requiere comunicación entre bases de datos y, por lo tanto, será más lento.

En lugar de la búsqueda LIKE, use la búsqueda de texto completo para buscar datos textuales

Las búsquedas de texto completo siempre superan a las búsquedas LIKE. Las búsquedas de texto completo le permitirán implementar criterios de búsqueda complejos que no pueden implementarse utilizando una búsqueda LIKE, como buscar en una sola palabra o frase (y, opcionalmente, clasificar el conjunto de resultados), buscar una palabra o frase cerca de otra palabra o frase, o buscar en formas sinónimas de una palabra específica. La implementación de la búsqueda de texto completo es más fácil de implementar que la búsqueda LIKE (especialmente en el caso de requisitos de búsqueda complejos).

Intenta usar UNION para implementar una operación "OR"

Trate de no usar "O" en una consulta. En su lugar, use "UNION" para combinar el conjunto de resultados de dos consultas distinguidas. Esto mejorará el rendimiento de la consulta. Mejor utilizar UNION ALL si no se requiere un resultado distinguido. UNION ALL es más rápido que UNION ya que no tiene que ordenar el conjunto de resultados para descubrir los valores distinguidos.

Implementar una estrategia de carga diferida para objetos grandes

Almacene columnas de Objeto grande (como VARCHAR (MAX), Imagen, Texto, etc.) en una tabla diferente a la tabla principal, y ponga una referencia al objeto grande en la tabla principal. Recupere todos los datos de la tabla principal en una consulta y, si es necesario cargar un objeto grande, obtenga los datos de objetos grandes de la tabla de objetos grandes solo cuando sea necesario.

Implementar las siguientes buenas prácticas en Funciones definidas por el usuario

No llame a las funciones repetidamente dentro de sus procedimientos almacenados, disparadores, funciones y lotes. Por ejemplo, puede necesitar la longitud de una variable de cadena en muchos lugares de su procedimiento, pero no llame a la función LEN cuando sea necesario; en su lugar, llame a la función LEN una vez, y almacene el resultado en una variable para su uso posterior.

Implementar las siguientes buenas prácticas en Disparadores

  • Intenta evitar el uso de disparadores. Disparar un disparador y ejecutar el evento desencadenante es un proceso costoso.
  • Nunca use disparadores que puedan implementarse usando restricciones.
  • No use el mismo disparador para diferentes eventos de activación (Insertar, Actualizar y Eliminar).
  • No use código transaccional dentro de un disparador. El desencadenador siempre se ejecuta dentro del alcance transaccional del código que desencadena el activador.

Mi consejo es que "la optimización prematura es la raíz de todo mal" en este contexto no tiene sentido ninguna tontería.

Desde mi punto de vista, todo se trata de diseño: debe pensar en simultaneidad, puntos de acceso, indización, escalado y patrones de uso cuando diseña su esquema de datos.

Si no sabe qué índices necesita y cómo deben configurarse de inmediato sin hacer perfiles, ya ha fallado.

Hay millones de formas de optimizar la ejecución de consultas que están muy bien, pero al final del día los datos aterrizan donde se lo indica.