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 / Contexto	Solución	Justificació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 DISTINCT	Use 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 precompiladas	Usa 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 batch	Por ejemplo usando JDBC	Enví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 RDBMS	Reducen 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 introspección porque esto 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) Si decides utilizar ORMs en escenarios en que la arquitectura sea guiada con microservicios evita utilizar ORMs que están diseñados para servidores de aplicaciones. Recomendable el uso de micro ORMs. Ejemplo, en Java JDBI, Hibatis, etc.	No uses HQL de hibernate para consultas complejas o de grandes volúmenes; usa namedQueries. En general no uses lenguajes traductores para el caso en donde el desempeño es clave.
Bloqueos en Pools de conexión	Cierre 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 UNION	Utilice UNION ALL	La 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 = ?
Cierre de conexiones desde clientes SQL	El número de conexiones hacia un motor de bases de datos es limitado. Si estas no se cierran desde un cliente SQL que puede ser una aplicación, una plataforma de reportes, etc. se ocuparán las conexiones causando indisponibilidad. Se debe garantizar que las conexiones se cierren una vez se utilicen para liberarlas y que el pool de conexiones no se agote.	conecction.close()

Recomiendo este libro de Anti patrones SQL

Espero les sirva!!!