Contenido
Las Razones Para Optimizar Sus consultas SQL
Que el tiempo es dinero es una máxima por todos nosotros conocida, esto se ha vuelto mucho más significativo en la era de internet y las comunicaciones virtuales, se espera que los programas y las páginas web funcionen de forma óptima, muchas veces esto significa código bien implementado, manejo de recursos y una clara idea de los procesos que involucra un sistema de tipo cliente/servidor.
En internet, esta última opción es obligatoria, muchas veces nuestra página manejará contenidos dinámicos, por lo que ha de involucrar una Base de Datos (que llamaremos BD de ahora en adelante) que nos entregará información y será parte importante del esquema que puede hacer de nuestro sitio un éxito.
Si bien puede que haya escuchado que los procesadores actuales son mucho muy potentes y capaces de manejar cantidades impresionantes de información, es algo demostrado que éste factor es de menor importancia comparado con la velocidad de los algoritmos usados, por lo cual es imperativo que se tenga especial cuidado en la optimización, cuando su sitio precise de todos los recursos necesarios para atender miles de consultas por minuto será evidente la necesidad de un sistema capaz de manejar la demanda.
Teoría de la optimización
Existen varias maneras de optimizar las BD y las consultas. A menudo las BD no son bien diseñadas y no están normalizadas. Esto puede afectar de forma radical la velocidad de su BD. Como regla general aprenda las 3 formas normales y aplíquelas en todo momento. Existen diseños que van mas allá de la tercera forma normal, éstos generalmente se usan para hacer aún más rápida una BD, pero esto es hecho por Administradores con profundo conocimiento del funcionamiento de los motores de DB, si ud es uno de ellos, entonces sabe lo que hace; pero como regla general el mejor consejo es mantenerse dentro de la tercera forma normal, la normalización más allá de la 3° forma normal se realiza mucho más adelante, no al momento del diseño. El diseño es fundamental y le ahorrará mucho trabajo luego.
Sólo consulte lo que realmente necesite
Filtre tanto como pueda, la cláusula WHERE es la más importante a la hora de la optimización; NUNCA use «SELECT *;» debe especificar los campos que necesita, esto hará su consulta más rápida y llevará a ahorrar ancho de banda (recuerde que Ud está enviando paquetes por una red, ¿Porqué habrá de enviar lo que no necesita?).
Sea cuidadoso con el JOIN, las expresiones JOIN son caras en términos de tiempo, asegúrese de que está usando todas las claves relativas a las tablas que refiere y no aplique join en tablas que no necesita, siempre trate de unir campos indexados. El tipo de join usado es muy importante también; familiarícese con los tipos (INNER, OUTER, LEFT, RIGHT….)
Las consultas son rápidas, generalmente ud podrá recuperar cantidad de información en menos de un segundo, incluso usando joins, ordenando y calculando. Una regla de oro es que si su consulta toma más de un segundo, ud deberá optimizarla. Su trabajo comenzará con las consultas más necesarias o más usadas, y luego proceda con las que más tiempo toman.
Agregue remueva o modifique índices.
Si sus consultas realizan búsquedas de tablas completas, deberá pensar en el uso de índices para sus tablas, el uso de ellos y de un filtrado adecuado pueden resolver la mayor parte de las consultas que toman demasiado tiempo. Todas las claves primarias precisan de índices para realizar los joins más rápidamente, esto también significa que todas las tablas precisan de clave primaria. Ud puede agregar índices en campos que usa seguido para el filtrado con cláusulas de tipo WHERE.
Especialmente usted precisa usar índices en campos de tipo entero, booleano y numérico; en cambio no le será de mucha ayuda en campos de tipo Blob, VarChar y Long String. El lado negativo de los índices es que debe tener mucho cuidado con su uso en tablas que se actualizan muy seguido, en estos casos el mantenimiento de los índices puede consumir más tiempo del que realmente ahorran. Otra herramienta excelente en el mundo de internet son las tablas de tipo read-only allí los índices funcionan en su mayor potencial, ya que raramente les necesitará realizar mantenimiento.
Procedimientos Almacenados
Los procedimientos almacenados son una alternativa rápida y mejor a las consultas por las siguientes razones:
1.- Los procedimientos almacenados son compilados (El código SQL es interpretado) haciéndolas una opción mucho más rápida.
2.- El ahorro de Ancho de banda es importante pues se pueden realizar varias consultas en un mismo procedimiento, además el procedimiento se mantiene en el servidor hasta que el resultado final es obtenido, y es esto únicamente lo que se envía al cliente.
3.- Los procedimientos almacenados corren dentro del servidor, que usualmente es más rápido.
4.- Los cálculos en código (VB, Java, C++) no son tan rápidos como en PA en la mayoría de los casos.
5.- Mantiene el código de acceso a la BD separado de la capa de presentación, lo que la hace más fácil de mantener (modelo de 3 capas) y muchísimo mas segura ante ataques como inyección SQL pues no se tiene acceso directo al código.
Se puede afinar una BD de varias formas, usando estadísticas de optimización, corriendo opciones de optimización, realizando tablas de sólo lectura, etc. Usualmente este trabajo lo realiza un Administrador de BD.
En la mayoría de los Sistemas Gestores de Bases de Datos existen herramientas de optimización MySQL tiene la herramienta MySQL query analyzer, que le permitirá ejecutar consultas y observar su incidencia, para determinar lo que el SGBD hace con sus consultas.
La optimización en la Práctica
Si se quiere consultar el nombre y el salario de los empleados del departamento de ventas:
EJEMPLO 1
Consulta original
Select * from Empleados; (luego se mostró la información correspondiente mediante código if Dept=Ventas.)
Corregido:
Select Nombre, Salario From Empleados Where Dept='Ventas'; En la versión corregida, se filtra la información en la BD lo que resulta más rápido, además sólo se solicita la información necesaria, lo que enviará sensiblemente menos información y por lo tanto mejorará la performance.
EJEMPLO 2
Original
Select Nombre, Salario From Empleados where Dept='Ventas' Order By Salario; Realmente necesita la cláusula Order by? A menudo se utiliza esta opción para revisar si los datos retornados son correctos, remuévala si no la necesita, ahora si es necesaria úsela en la consulta no en la página.
EJEMPLO 3
Original
For i=1 to 2000 call query : Select Salario From Empleados Where EMpID= Parametro(i); Corregido:
Select Salario From Empleados Where EmpID >=1 and EmpID <= 2000; La primera opción involucra gran transmisión de datos lo que hará mas lento el sistema completo, siempre debe realizar el trabajo en la consulta o el procedimiento almacenado, si obliga al sistema a llevar y traer información pierde valioso tiempo. No importa si ud piensa que el proceso es tan complicado que es mejor manejarlo en el código de su aplicación, pero rara vez lo es.
EJEMPLO 4 (unión débil)
Se tienen 2 tablas Ordenes y Clientes. Los Clientes pueden tener varias órdenes.
Original:
Select O.Precio, C.Nombre From Ordenes O, Clientes C; Corregido:
Select O.Precio, C.Nombre From Ordenes O INNER JOIN Clientes C ON O.ClienteID=C.ClienteID; Conclusión
La optimización de consultas es un trabajo extremadamente importante en la gestión y mantenimiento de una base de datos, muchas veces en BD de tamaño importante una consulta mal manejada puede hacer imposible el trabajo mas trivial, cabe recordar que algunas consultas pueden hacer crecer de forma exponencial la cantidad de registros de retorno , cuando debería devolver unos pocos, (el crecimiento en realidad correspondería al producto cartesiano de los registros trabajados, pero no viene al caso).