Pildoritas de Bases de Datos: Optimización. Definición de las selects

Vuelven de nuevo las Pildoritas de Bases de Datos tras el extenso parón de verano.

Las vacaciones y la mudanza no han ayudado, pero sobre todo lo que no ha ayudado es mi limitada capacidad de organizarme las ideas en el tema de Optimización. Resulta curioso que en estas pildoritas -que en un primer momento las pensé orientar exclusivamente a optimización- este tema sea el que más me está costando escribir.

Después de mucho organizar todas esas pautas y consejillos varios que he ido aprendiendo con el tiempo, he llegado más o menos a un esquema definitivo:

  • Consejos sobre la definición de las selects
  • WHERE NON SARGABLE
  • Cuestiones de rendimiento en Operadores y Funciones de agregación.

Quizás no sea un buen orden, pero de verdad que no se me ocurre otra manera de organizar todo este batiburrillo de ideas de la forma más clara posible. Si algún conocedor de optimización pasa por aquí y piensa que hay mejores maneras de organizar la información, gustosamente seré todo oidos 🙂

Comencemos pues con el primer capítulo de la parte de optimización: Pautas a la hora de escribir una consulta SELECT.

Aunque parezca mentira muchas veces cosas tan nimias como el orden, el uso de mayúsculas, o los prefijos pueden influenciar de cara al rendimiento. También hay que tener en cuenta ciertas consideraciones sobre el uso de predicados que abordaremos en este post, los cuales vimos en las últimas pildoritas, y que daban por finalizada la parte de SQL.

Como siempre utilizaremos para nuestros ejemplos el modelo de datos definido en nuestra primera pildorita. Si habeis seguido estas pildoritas estoy segura que ya lo habíais abierto xD

Especifica sólo los campos que quieras obtener

Puede parecer obvio, pero mucha gente casca un * y se queda tan pancha. Esto cuando hay varias tablas en juego puede ser un auténtico dolor para el motor de la base de datos.

Escribe siempre igual la query

Algunos motores SQL como Oracle almacenan en memoria las sentencias que ya han sido ejecutadas para así optimizar futuras respuestas, aunque la sentencia tiene que ser idéntica, y eso incluye el orden de los campos, tablas y condiciones, si esta estaba escrita en mayúsculas y minúsculas, etc.

En el caso concreto de que se tiren estas queries en una aplicación como puedes imaginar lo útil sería reutilizar siempre que se pueda la misma query, y para ello utilizaríamos variables BIND (parámetros).

Por ejemplo, imaginemos que tenemos una aplicación con dos funcionalidades diferentes: Una funcionalidad que obtiene los departamentos de Desarrollo y otra que obtiene aquellos de Sistemas.

Cada funcionalidad se transcribiría en las siguientes consultas:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = 'DESARROLLO'
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = 'SISTEMAS'

Cada query es diferente, por lo que el sistema no las tratará como la misma consulta y no será del todo óptimo. Si utilizamos un parametro BIND, en cambio, sí que podremos agrupar ambas consultas en una sola, y que el motor almacene ambos casos como la misma planificación:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = :AREA

Utiliza prefijos / alias.

Una práctica muy extendida es crear alias para las tablas, que no son más que seudónimos para referirse a la misma. Pues bien, si especificamos el nombre de la tabla o en su defecto el alias que se refiere a la misma antes de especificar cada campo, le ahorraremos tiempo al motor de búsqueda, ya que no tendrá que ser él el encargado de identificar a que tabla pertenece cada campo.

En los siguientes ejemplos de este capítulo se utilizan alias para definir la tabla intermedia que relaciona a empleado con proyectos. Además, cuando utilizamos más de una tabla hemos puesto como prefijo el nombre de la tabla a la que pertenece la columna. Aun así, es una buena práctica que lo hagais siempre, no sólo al tener nombres idénticos en diferentes columnas o nombres demasiado largos.

Utiliza TOP / ROWNUM

Si nos vale con un número finito de resultados podemos utilizar las clausulas TOP, que devuelve el primer registro, o ROWNUM, por el cual indicamos exactamente las filas a devolver.

El uso de esta clausula disminuye increiblemente los tiempos de respuesta de la consulta.

Por ejemplo, si queremos obtener el empleado con mayor salario, haremos un ORDER BY, que tiene un coste bastante alto, o utilizaremos en caso de disponer de él el operador MAX. Si limitados los resultados a una fila, que es lo que nos hace falta, podremos mejorar altamente el rendimiento de la query.

Elige buenas condiciones en el WHERE

Otra pauta muy importante es definir en el WHERE las condiciones de más a menos restrictiva e intentar utilizar en dichas condiciones campos que estén contenidos en índices.

Si el índice es compuesto hay que filtrar por todos los campos si es posible, y en caso de no ser así, debe filtrarse por los primeros campos obligatoriamente. Me explico:

Si tengo un índice compuesto tal que IDX(Apellidos, Nombre), es aconsejable filtrar por apellidos y nombre o al menos por apellido. Si filtramos por nombre el índice no se utilizará.

Es más, no uses el WHERE

Los WHERE no son más que joins, y el rendimiento puede mejorar mucho más si en vez de definir las uniones entre claves y las condiciones en el WHERE lo hacemos directamente en la parte del FROM como un INNER JOIN.

El where funciona exactamente igual que la clausula INNER JOIN, con la salvedad de que al motor le cuesta menos procesar las clausulas en el mismo operador, que ir a buscarlas al WHERE.

Para que lo veais mejor utilizaré el primer ejemplo de la pildorita de los joins.

SELECT EMPLEADO.DNI, EMPLEADO.NOMBRE, PUESTO.NOMBRE AS PUESTO
FROM PUESTO, EMPLEADO
WHERE EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

Es equivalente a:

SELECT EMPLEADO.DNI, EMPLEADO.NOMBRE, PUESTO.NOMBRE AS PUESTO
FROM PUESTO INNER JOIN EMPLEADO ON EMPLEADO.ID_PUESTO = PUESTO.ID_PUESTO

A mi en particular me gusta utilizar el WHERE siempre que es posible porque me resulta más claro ver los todos los filtrados en el mismo lugar, que “mezclados” junto a las tablas en el FROM. No obstante, esto es una cuestión de gustos, y para muchos la forma más intuitiva es utilizar los INNER JOIN.

Si tenéis problemas de rendimiento deberíais transformar las condiciones del WHERE en INNER JOINs. Sea como fuere, en el INNER JOIN sólo incluiremos la unión entre indices como condiciones y en el WHERE seguiremos indicando otro tipo de filtrados.

GROUP BY y HAVING cuando haya funciones de agregación.

Siempre que se pueda hay que evitar utilizar clausulas en el HAVING y que esos filtrados se realicen en el WHERE, ya que el coste en el HAVING es superior.

Por otra parte, realizar un GROUP BY sin funciones de agregación es factible, pero devuelve lo mismo que un DISTINCT, y el coste del segundo es menor, así que es absurdo utilizar un GROUP BY y no un DISTINCT en agrupaciones sin función de agregación asociada.

Por ejemplo, imaginemos que queremos averiguar los empleados que han trabajado en un determinado proyecto. Puede darse el caso de que un empleado haya estado más de una vez en el mismo proyecto, por lo que se pueden dar repeticiones.

Por tanto, la primera idea puede ser “agrupemos por dni, así no tendremos repeticiones:

SELECT PROY.DNI 
FROM PROYECTO_HAS_EMPLEADO AS PROY_EMP 
WHERE ID_PROYECTO = :ID_PROYECTO
GROUP BY PROY.DNI

Pero lo dicho, aquí no tenemos ninguna función de agregación, por lo que la query es completamente reescribible con el predicado DISTINCT.

SELECT DISTINCT PROY.DNI 
FROM PROYECTO_HAS_EMPLEADO AS PROY 
WHERE ID_PROYECTO = :ID_PROYECTO

Otra cosa sería si quisieramos sacar el número de empleados por proyecto, por poner un ejemplo. En este caso utilizaremos la función de agregación count, así que no hay más alternativa que agrupar por proyectos (es más, si intentarais escribir una query con una función de agregación sin un GROUP BY vuestro motor daría un error).

SELECT COUNT(PROY.DNI)
FROM PROYECTO_HAS_EMPLEADO AS PROY
WHERE ID_PROYECTO =  :ID_PROYECTO
GROUP BY PROY.ID_PROYECTO

Aún así, el DISTINCT con cuidadito.

Siempre que hacemos agrupaciones, sea con el DISTINCT o con el GROUP BY, debemos evaluar concienzudamente si es realmente necesario o no. Muchas veces vemos datos repetidos y cortamos por lo sano con un DISTINCT, pero estas repeticiones pueden deberse a que no se ha filtrado todo lo bien que se podría o falta algún join entre tablas.

En caso de usar un GROUP BY, se austero.

Intenta que la query tenga el mínimo de campos a devolver, así como el menor número posible de funciones de agregación. Como es obvio tampoco se debe agrupar por columnas que son ya de por sí redundantes.

Por otra parte, mucho cuidado con los JOINS cuando realizamos un GROUP BY. Siempre que se puede hay que intentar convertirlo en una subquery, lo cual mejorará el rendimiento. Si no queda más remedio que hacer el JOIN, hay que meter en el GROUP BY columnas de la misma tabla que la que se haya utilizado en la función de agregación.

Utiliza UNION ALL en vez de UNION

Hacer un UNION es lo mismo que hacer un UNION ALL entre dos queries con el predicado DISTINCT, el cual, como ya comentaba en el punto anterior sobre el DISTINCT, significa mayor carga en el rendimiento.

Si sabemos que los datos no se van a repetir ni necesitan una ordenación concreta, utilizaremos la clausula UNION ALL, que tiene un coste muy inferior.

Por ejemplo, si queremos buscar departamentos que sean del area de Desarrollo y departamentos del area de Sistemas, utilizaríamos esta query:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

Como comentaba arriba, esta query es exactamente igual a utilizar un UNION ALL con la clausula DISTINCT

SELECT DISTINCT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION
SELECT DISTINCT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

Ahora bien, ¿en nuestro modelo pueden existir departamentos que sean tanto del área de Sistemas como de Desarrollo? Si así fuera el caso, el uso de UNION tendría todo el sentido del mundo, pero habitualmente no hay departamentos mixtos, por lo que en este caso no habrá resultados repetidos y lo inteligente es utilizar el predicado UNION ALL, que es mucho más eficiente:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION ALL
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

Aún así tanto UNION como UNION ALL tienen un coste muy elevado, por lo que siempre hay que usarlo cuando sea estrictamente necesario, ya que muchas veces lo utilizamos cuando esa query es perfectamente reescribible en una query simple, ya sea por medio de un JOIN, operadores lógicos OR, etc.

Para ilustrarlo nada mejor que un ejemplo más.

Tenemos esta query con un UNION:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘DESARROLLO’
UNION ALL
SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = ‘SISTEMAS’

La cual es igual a:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA = DESARROLLO OR AREA = ‘SISTEMAS’

O a ésta otra si utilizamos el operador IN:

SELECT NOMBRE FROM DEPARTAMENTO WHERE AREA IN (‘DESARROLLO’, ‘SISTEMAS’)

Vamos, que si tienes algún problema de complejidad con una query que contiene una UNION, lo primero que debes pararte a pensar es si no es posible reescribirla sin utilizar dicho predicado.

Utilizar ORDER BY tampoco es recomendable

El ORDER BY es otro de esos predicados con un coste de rendimiento altísimo.

Siempre que podamos intentaremos hacer esa ordenación de lado del cliente y no en BBDD, pero si no existe más remedio hay que seguir una serie de pautas parecidas a las del GROUP BY.

Hay que intentar utilizar el mínimo número de campos en la ordenación y devolver también lo mínimo posible en la parte SELECT. El tamaño físico de las columnas de ordenación también deben de ser lo más pequeño posible. Por último, hay que utilizar para las ordenaciones campos numéricos en vez de tipos de dato carácter.

 

Y con esto ya hemos terminado la primera parte de optimización SQL. En el próximo capítulo veremos los operadores SARGABLE y NO SARGABLE, explicaremos que demonios significa eso, y que implicaciones tiene. Eso sí, no se si este tema se cubrirá con un sólo post o harán falta más. En su momento ya se verá 🙂

Índice de Pildoritas

Share