lunes, 6 de febrero de 2017

Buenas prácticas para elaborar consultas SQL

Buenas prácticas en construcción de Consultas SQL


Esta entrada es una recopilación de muchos años, en los cuales muchas personas me brindaron tips respecto a cómo implementar consultas en RDBMS con alto rendimiento.

Hace poco algunos compañeros de trabajo me solicitaron que compartiera este conjunto de prácticas con el fin de que fueran evolucionando; Muchos pueden aportar.

Si bien no están todos los posibles tips en afinamiento, si hay un conjunto básico de prácticas a seguir, que espero les sea útil.

Posdata: si tienen otra práctica a incluir, por favor dejen el comentario para incluirla.

El conocimiento no sirve de nada si no se comparte

A continuación la lista de prácticas:



Problema / ContextoSoluciónJustificación
Optimizar el producto cartesiano de las tablas a utilizar en una consulta.Prefiere los JOINS en todas sus variedades (Left, right, inner,etc.) en el FROM antes que en el WHERE.1). Siempre lo primero que hace un motor de BD es ejecutar la sentencia FROM realizando un producto cartesiano.
2). Luego, ejecuta la clausula where.
3). Calcula las filas del SELECT.
4). Si existe un select distinc, ordena y luego elimina las filas duplicadas.
5). Si existe el order by, ordena las filas.

Si se evitan los joins en el where mejorarás el desempeño pues se aprovechará el producto cartesiano.
Siempre las tablas que tienen menos registros van al lado izquierdo en el FROM y en el WHERE. Es fundamental el orden.Ejemplo: FROM TABLA1, TABLA2, TABLA3.

La TABLA1 debe tener menos registros que la TABLA2 y esta menos que la TABLA3. Lo anterior porque recuerda que en el FROM siempre se realiza un producto cartesiano (revisar algebra y cálculo relacional)
Se aplica la teoría de conjuntos, en dónde la tabla con menos registros intercepta a la que tiene más registros, y por tal motivo si se enlaza una tercera se tendrá un conjunto a interceptar más pequeño, agilizando la consulta pues se manejan menos datos.
Nunca uses sentencias LIKE al iniciar un where.Es importante asegurarse que ya se han filtrado los datos para que el conjunto de datos objetivo sea más pequeño y por lo tanto manejable en el caso en que se requiera el uso de LIKE. Es importante recordar que la sentencia LIKE implica que no se usen índices.Las sentencias LIKE no utlizan índices. Evita el LIKE "%AS%", revisa si lo que requieres es un LIKE "%AS" o un LIKE "AS%"
Nunca use DISTINCTUse EXISTS. Por ejemplo: Escriba la consulta como

SELECT d.dept_id, d.dept
FROM dept d
WHERE EXISTS ( SELECT 'X' FROM employee e WHERE e.dept = d.dept);

En lugar de:

SELECT DISTINCT d.dept_id, d.dept
FROM dept d,employee e
WHERE e.dept = e.dept;
Si usas un DISTINCT implicará que primero el motor ejecute la consulta y luego la ordene para luego eliminar los registros duplicados. prefiera un: SELECT X1, X2 FROM (SELECT X1, X2, FROM Y WHERE.....) GROUP BY X1, X2
Evite el ORDER BY
Es un paso adicional a veces innecesario que retarda una consulta.
Use un subconjunto ordenado de la definición de un índice en el WHERE para garantizar que lo utilice.Use un subconjunto ordenado de la definición de un índice para asegurarse que sigue un plan de ejecución deseado.Si le ayudas al motor mucho mejor. Si tienes un índice de una tabla X con los siguientes campos: x1,x2 y x3; en el WHERE debes siempre colocar de arriba a abajo: WHERE x1=? (AND/OR..) x2=? (AND/OR..) x3=? o por ejemplo WHERE x1=? (AND/OR..) x2=?, pues siempre será más rápido que un WHERE x3=? (AND/OR..) x1=?, etc...le facilitarás al motor el establecimiento del plan de datos y el uso de las memorias compartidas de éste (consultas frecuentes)
No use SELECT *Coloque las columnas que requiere.El motor primero lee la estructura de la tabla y luego realiza la consulta. Hace una búsqueda de la meta-data de la tabla y luego si coloca los campos.
Siempre utiliza consultas precompiladasUsa consultas precompiladas en el lenguaje de programación en el que las realices.No es lo mismo SELECT x.x1, x.x2 FROM x WHERE x.x1=1 que SELECT x.x1, x.x2 FROM x WHERE x.x1=2. Mientras que para el motor si es lo mismo SELECT x.x1, x.x2 FROM x WHERE x.x1=? para todos los casos y por tal motivo seguirá un plan de ejecución ya definido.
Utiliza en la mayoría de los casos consultas en batchPor ejemplo usando JDBCEnvían un conjunto de consultas que se ejecutan en el motor de base de datos, en vez de hacer llamados sucesivos ocupando recurso computacional como: memoria, red, cpu.
Si vas a llamar varias veces a una BD para efectúar consultas SQL secuencialmente es preferible que crees un procedimiento almacenado.
Si vas a llamar varias veces a una BD para efectúar consultas SQL secuencialmente es preferible que crees un procedimiento almacenado que contenga todas las consultas y que retorne una estructura con todos los valores que requieras (ahorraras red, memoria...etc).
Si es muy grande el conjunto de datos requerido descarta esta idea a menos que implementes un paginador. Cuando tengas pocas consultas no uses un procedimiento almacenado pues será más costoso.
Es preferible no usar índices para tablas que tienen pocos registros.
Es más costoso trazar el plan de ejecución para la BD que si se efectuará un FULL-DATA.
Defina alias a las tablas en el SELECT con el fin de facilitarle al motor la tarea de averiguar campo por campo a que tabla pertenece.
Se evitan las consultas intronspectivas. Se evitan accesos a la meta-data de la BD.
Evita el casting CAST y el uso de funciones dentro de las consultas.No utilice funciones propietarias del RDBMSReducen el desempeño de una consulta. Además, es difícil afinar las consultas de las funciones.
Siempre implementa la lógica de paginación en BD y no en algún contenedor Web o de lógica.
Evita la utlización de patrones como el ValueListHandler.
Evita el uso de intronspección porque ésto genera una carga innecesaria.Conclusión, no uses ORMs porque están de moda. Úsalos para tabla de parámetros o de configuración, pero para consultas que requieran eficiencia jamás. Siempre los POJOS (ValueObject, DTO, o transfer objects acompañados de un DAO serán más rápidos)No uses HQL de hibernate para consultas complejas o de grandes volúmenes; usa namedQueries.
Bloqueos en Pools de conexiónCierre siempre las conexiones y cursores (resultSets) en Java.Existe un número limitado de sesiones disponibles en una base de datos.
Utilice vistas materializada para consultas recurrentes
La ventaja de una vista es que tienes un select ya hecho y te permite realizar consultas de forma rápida y sencilla. De esta forma, por ejemplo, si en una consulta que haces muy habitualmente hay una función que tarda mucho en ejecutarse, puedes tener la vista materializada con los resultados ya pre-calculados y acceder a los datos rápidamente.

Las vistas materializadas se actualizan o se recrean solas cada cierto tiempo, o sea, que también se pueden ver como una tabla que va actualizando automáticamente sus datos cada cierto tiempo con los resultados de una select.
Evite el uso de UNIONUtilice UNION ALLLa sentencia UNION elimina los duplicados de la unión de dos consultas mientras que el UNION ALL concatena todos los resultados y no elimina duplicados. El desempeño mejorará usando UNION ALL



Obtención de descripciones desde tipologías con el fin de mostrarlas en el SELECT. Ejemplo
SELECT A.A1, A.A2, A.A4
FROM TABLEA A, TIPOLOG T
WHERE A.A1 = ? AND A.A2 = ? AND (T.T1 = A.A3 AND T.T2 = 'A' AND T.T3 = 'B')
En estos casos es mejor hacer una subconsulta en el select si la tabla base de la consulta contiene un gran número de registros y las de tipologías unos pocos.
Recuerde que si la tabla de tipologías es una tabla pequeña no es muy util utilizar índices y que una consulta resuelve el grán número de registros en el from y en el
where efectuando los productos cartesianos allí.

Siguiendo el ejemplo anterior use:
SELECT A.A1, A.A2, (SELECT T.T4 FROM TIPOLOG T WHERE T.T1 = A.A3 AND T.T2 = 'A' AND T.T3 = 'B') as A4
FROM TABLEA A
WHERE A.A1 = ? AND A.A2 = ?


Recomiendo este libro de Anti patrones SQL

Espero les sirva!!!


4 comentarios: