SQL desatado: 17 formas de acelerar sus consultas SQL

Los desarrolladores de SQL en todas las plataformas están luchando, aparentemente atrapados en un DO WHILEbucle que los hace repetir los mismos errores una y otra vez. Eso es porque el campo de la base de datos todavía es relativamente inmaduro. Claro, los proveedores están haciendo algunos avances, pero continúan lidiando con los problemas más importantes. La simultaneidad, la gestión de recursos, la gestión del espacio y la velocidad todavía afectan a los desarrolladores de SQL, ya sea que estén codificando en SQL Server, Oracle, DB2, Sybase, MySQL o cualquier otra plataforma relacional.

Parte del problema es que no existe una fórmula mágica, y para casi todas las mejores prácticas, puedo mostrarles al menos una excepción. Por lo general, un desarrollador encuentra sus propios métodos favoritos, aunque generalmente no incluyen construcciones para el rendimiento o la simultaneidad, y no se molesta en explorar otras opciones. Tal vez sea un síntoma de falta de educación, o los desarrolladores están demasiado cerca del proceso para reconocer cuando están haciendo algo mal. Tal vez la consulta funcione bien en un conjunto local de datos de prueba, pero falla estrepitosamente en el sistema de producción.

No espero que los desarrolladores de SQL se conviertan en administradores, pero deben tener en cuenta los problemas de producción al escribir su código. Si no lo hacen durante el desarrollo inicial, los administradores de bases de datos simplemente los obligarán a volver y hacerlo más tarde, y los usuarios sufrirán mientras tanto.

Hay una razón por la que decimos que ajustar una base de datos es un arte y una ciencia. Es porque existen muy pocas reglas estrictas que se apliquen en todos los ámbitos. Los problemas que ha resuelto en un sistema no son problemas en otro y viceversa. No hay una respuesta correcta cuando se trata de ajustar las consultas, pero eso no significa que deba darse por vencido.

Hay algunos buenos principios que puede seguir y que deberían producir resultados en una combinación u otra. Los he encapsulado en una lista de lo que se debe y no se debe hacer en SQL que a menudo se pasan por alto o son difíciles de detectar. Estas técnicas deberían darle un poco más de conocimiento sobre las mentes de sus DBA, así como la capacidad de comenzar a pensar en los procesos de una manera orientada a la producción.

1. No use en UPDATElugar deCASE

Este problema es muy común y, aunque no es difícil de detectar, muchos desarrolladores a menudo lo pasan por alto porque el uso UPDATEtiene un flujo natural que parece lógico.

Tome este escenario, por ejemplo: está insertando datos en una tabla temporal y necesita que muestre un cierto valor si existe otro valor. Tal vez esté sacando de la tabla de Clientes y quiera que cualquier persona con más de $ 100,000 en pedidos sea etiquetada como "Preferida". Por lo tanto, inserta los datos en la tabla y ejecuta una UPDATEdeclaración para establecer la columna CustomerRank en "Preferido" para cualquiera que tenga más de $ 100,000 en pedidos. El problema es que la UPDATEdeclaración se registra, lo que significa que tiene que escribir dos veces por cada escritura en la tabla. La forma de evitar esto, por supuesto, es usar una CASEdeclaración en línea en la consulta SQL. Esto prueba cada fila para determinar la condición del monto del pedido y establece la etiqueta "Preferido" antes de que se escriba en la tabla. El aumento de rendimiento puede ser asombroso.

2. No reutilices el código a ciegas

Este problema también es muy común. Es muy fácil copiar el código de otra persona porque sabe que extrae los datos que necesita. El problema es que, con bastante frecuencia, extrae muchos más datos de los que necesita y los desarrolladores rara vez se molestan en recortarlos, por lo que terminan con un gran superconjunto de datos. Por lo general, esto se presenta en forma de una combinación externa adicional o una condición adicional en la WHEREcláusula. Puede obtener enormes ganancias de rendimiento si reduce el código reutilizado a sus necesidades exactas.

3. Extraiga solo la cantidad de columnas que necesita

Este problema es similar al número 2, pero es específico de las columnas. Es muy fácil codificar todas sus consultas en SELECT *lugar de enumerar las columnas individualmente. El problema nuevamente es que extrae más datos de los que necesita. He visto este error decenas y decenas de veces. Un desarrollador realiza una SELECT *consulta en una tabla con 120 columnas y millones de filas, pero termina usando solo de tres a cinco de ellas. En ese punto, está procesando muchos más datos de los que necesita, es una maravilla que la consulta devuelva. No solo está procesando más datos de los que necesita, sino que también está quitando recursos de otros procesos.

4. No lo sumerjas dos veces

Aquí hay otro que he visto más veces de las que debería: se escribe un procedimiento almacenado para extraer datos de una tabla con cientos de millones de filas. El desarrollador necesita clientes que vivan en California y tengan ingresos de más de $ 40,000. Por eso, busca clientes que viven en California y coloca los resultados en una tabla temporal; luego, consulta a los clientes con ingresos superiores a $ 40 000 y coloca esos resultados en otra tabla temporal. Finalmente, une ambas tablas para obtener el producto final.

¿Me estás tomando el pelo? Esto debe hacerse en una sola consulta; en cambio, estás sumergiendo dos veces una mesa supergrande. No sea un idiota: consulte tablas grandes solo una vez siempre que sea posible; descubrirá cuánto mejor se desempeñan sus procedimientos.

Un escenario ligeramente diferente es cuando varios pasos de un proceso necesitan un subconjunto de una tabla grande, lo que hace que la tabla grande se consulte cada vez. Evite esto consultando el subconjunto y persistiéndolo en otro lugar, luego señalando los pasos posteriores a su conjunto de datos más pequeño.

6. Realice datos previos a la etapa

Este es uno de mis temas favoritos porque es una técnica antigua que a menudo se pasa por alto. Si tiene un informe o un procedimiento (o mejor aún, un conjunto de ellos) que hará uniones similares a tablas grandes, puede ser una ventaja para usted preparar previamente los datos uniendo las tablas con anticipación y persistiéndolas en una mesa. Ahora los informes pueden ejecutarse en esa tabla preparada previamente y evitar la combinación grande.

No siempre puede utilizar esta técnica, pero cuando pueda, encontrará que es una forma excelente de ahorrar recursos del servidor.

Tenga en cuenta que muchos desarrolladores solucionan este problema de combinación concentrándose en la consulta en sí y creando una vista de solo vista alrededor de la combinación para que no tengan que escribir las condiciones de combinación una y otra vez. Pero el problema con este enfoque es que la consulta aún se ejecuta para todos los informes que la necesitan. Al preconfigurar los datos, ejecuta la combinación solo una vez (digamos, 10 minutos antes de los informes) y todos los demás evitan la gran combinación. No puedo decirte cuánto amo esta técnica; En la mayoría de los entornos, hay tablas populares que se unen todo el tiempo, por lo que no hay ninguna razón por la que no puedan prepararse previamente.

7. Elimina y actualiza en lotes

Aquí hay otra técnica fácil que se pasa mucho por alto. Eliminar o actualizar grandes cantidades de datos de tablas enormes puede ser una pesadilla si no lo hace bien. El problema es que ambas declaraciones se ejecutan como una sola transacción, y si necesita eliminarlas o si algo le sucede al sistema mientras están funcionando, el sistema tiene que revertir toda la transacción. Esto puede llevar mucho tiempo. Estas operaciones también pueden bloquear otras transacciones durante su duración, esencialmente bloqueando el sistema.

La solución es eliminar o actualizar en lotes más pequeños. Esto resuelve su problema de dos formas. Primero, si la transacción se cancela por cualquier motivo, solo tiene una pequeña cantidad de filas para revertir, por lo que la base de datos regresa en línea mucho más rápido. En segundo lugar, mientras que los lotes más pequeños se comprometen en el disco, otros pueden colarse y hacer algo de trabajo, por lo que la simultaneidad se mejora enormemente.

En este sentido, muchos desarrolladores tienen en la cabeza que estas operaciones de eliminación y actualización deben completarse el mismo día. Eso no siempre es cierto, especialmente si está archivando. Puede extender esa operación todo el tiempo que lo necesite, y los lotes más pequeños ayudan a lograrlo. Si puede tardar más en realizar estas operaciones intensivas, dedique más tiempo y no apague su sistema.

8. Utilice tablas temporales para mejorar el rendimiento del cursor

Espero que todos sepamos a estas alturas que es mejor mantenerse alejado de los cursores si es posible. Los cursores no solo sufren problemas de velocidad, que en sí mismos pueden ser un problema con muchas operaciones, sino que también pueden hacer que su operación bloquee otras operaciones durante mucho más tiempo del necesario. Esto reduce enormemente la concurrencia en su sistema.

Sin embargo, no siempre puede evitar el uso de cursores, y cuando surgen esos momentos, es posible que pueda evitar los problemas de rendimiento inducidos por el cursor realizando las operaciones del cursor contra una tabla temporal. Tomemos, por ejemplo, un cursor que atraviesa una tabla y actualiza un par de columnas en función de algunos resultados de comparación. En lugar de hacer la comparación con la tabla en vivo, es posible que pueda poner esos datos en una tabla temporal y hacer la comparación con eso. Luego, tiene una sola UPDATEdeclaración contra la mesa en vivo que es mucho más pequeña y mantiene bloqueos solo por un corto tiempo.

Hacer un francotirador de las modificaciones de datos de esta manera puede aumentar considerablemente la simultaneidad. Terminaré diciendo que casi nunca necesitas usar un cursor. Casi siempre hay una solución basada en conjuntos; necesitas aprender a verlo.

9. No anide las vistas

Las vistas pueden ser convenientes, pero debe tener cuidado al usarlas. Si bien las vistas pueden ayudar a ocultar grandes consultas de los usuarios y estandarizar el acceso a los datos, puede encontrarse fácilmente en una situación en la que tiene vistas que llaman vistas que llaman vistas que llaman vistas. Esto se denomina vistas anidadas y puede causar graves problemas de rendimiento, especialmente de dos formas:

  • En primer lugar, es muy probable que reciba muchos más datos de los que necesita.
  • En segundo lugar, el optimizador de consultas se rendirá y devolverá un plan de consulta incorrecto.

Una vez tuve un cliente al que le encantaba anidar las vistas. El cliente tenía una vista que usaba para casi todo porque tenía dos combinaciones importantes. El problema fue que la vista devolvió una columna con documentos de 2 MB. Algunos de los documentos eran aún más grandes. El cliente estaba impulsando al menos 2 MB adicionales a través de la red por cada fila en casi todas las consultas que ejecutó. Naturalmente, el rendimiento de las consultas fue abismal.

¡Y ninguna de las consultas utilizó esa columna! Por supuesto, la columna estaba enterrada a siete puntos de vista de profundidad, por lo que incluso encontrarla fue difícil. Cuando eliminé la columna del documento de la vista, el tiempo para la consulta más grande pasó de 2,5 horas a 10 minutos. Cuando finalmente desentrañé las vistas anidadas, que tenían varias uniones y columnas innecesarias, y escribí una consulta simple, el tiempo para esa misma consulta se redujo a subsegundos.