13
Nov 13

Pildoritas de Bases de Datos: Optimización. Explain Plan

pildoras bbdd

Después de un parón algo más largo (pero no tan largo como el de verano), afrontamos ya el último de los posts de las Pildoritas de Bases de Datos. En él vamos a ver de forma muy básica como lanzar un Explain Plan y las ideas generales para poder interpretarlo, conforme a las cuales actuaremos en la optimización.

El Explain Plan es una funcionalidad que comparten todos los motores de BBDD y aunque cada uno tiene sus particularidades propias, se comparten ciertas ideas troncales para que cualquiera, independientemente de la tecnología, lo pueda entender.

El Explain Plan está asociado a las consultas y nos muestra un análisis de que tipos de recorridos se realizan en las tablas de consultas, lo cual nos sirve para saber si nuestra consulta es más o menos rápida. La forma de saber las diferencias de rendimiento de cada consulta y cada parte de una consulta es el coste.

Hay GUIs que dibujan en forma de árbol los explain plan, pero al final todos tiran de un comando más o menos igual, el EXPLAIN:

ORACLE

EXPLAIN  FOR [LA SELECT A ANALIZAR]

MySQL

EXPLAIN [LA SELECT A ANALIZAR]

Lo que más diferencia un explain plan realizado en diferentes tecnologias son los nombres de los accesos y los tipos de joins, aunque tienen relación suficiente para que uno lo entienda.

En el caso de acceso, hay dos grandes grupos: FULL TABLE SCAN e INDEX SCAN. No obstante dentro de estos grupos, como veremos, existen multitud de subcategorías.

Por otra parte también hay que tener en cuenta también como está haciendo el JOIN entre tablas nuestra BBDD. También veremos cuatro tipos de JOIN.

En ambos casos utilizaré los tipos de Oracle, no obstante otras tecnologías tienen, si no los mismos nombres, si muy parecidos.

Tipos de acceso

Como ya vimos en la otra pildorita de optimización, lo mejor siempre es que nuestra consulta tire de los índices y evitar así los FULL TABLE SCAN, los cuales recorren por completo la tabla y que, como es de imaginar, es el acceso más costoso. Esto no ocurre siempre, ya que a veces si el recorrido del índice es muy costoso un FULL TABLE puede dar mejor resultado.

Los recorridos de índices pueden ser de cuatro tipos en Oracle, aunque, como ya he aclarado antes, en el resto de tecnologías veremos "cosas" parecidas:

Index Unique Scan

El acceso busca una única clave a través de un índice único. Eso significa que siempre la búsqueda devolverá un resultado, en otro caso ya no sería UNIQUE. Un ejemplo claro de este tipo de acceso es el que hacemos cuando buscamos a través de la PK.

Index Range Scan

Como su nombre indica, en este caso no buscamos por un único valor, sino por un rango de ellos, por lo que nos devolverá más de un registro. Esto ocurre cuando usamos los operadores de rango (<, >, <>, <=, >=, BETWEEN).

Index Full Scan

Esta es la peor de las decisiones con índices y para que ocurra deberemos de no filtrar por el índice y que sea el motor el que decida que este acceso es más óptimo que un FULL SCAN, lo cual es raro, ya que, como os comenté en su momento, recorrer un índice es costoso y mejora la optimización sólo si está ordenado y se recorre un rango "pequeño" de registros de la tabla. Ahora bien, si por ejemplo queremos tirar un ORDER BY de una tabla sin filtrado y este orden es el utilizado por el índice, en ese caso entonces el motor decidirá acceder por esta vía.

Estas pautas de accesos son a nivel de lectura de los registros de la tabla. También hay otra parte muy importante que debemos tener en cuenta: Como se realizan los JOINs.

Tipos de JOIN

Nested Loop

Este es el JOIN típico. Se recorren los registros de A, y luego se prueba que cada uno de los registros de A esté en B. Es por eso que en otros capítulos dábamos las pautas de que hay que intentar siempre filtrar al máximo la tabla de unión (en el caso exacto la A) y que esta tabla fuente tiene que ser, si es posible, la más pequeña de ambas (incluyendo filtrados).

Hash Merge Join

Esto no está en todos los motores, así que no entraremos demasiado en el tema. Simplemente basta saber que este caso es incluso más eficiente que el Nested Loop, ya que la utilización de un Hash Map para la recuperación de datos es mucho más eficiente.

Sort Merge Join

Este es el peor JOIN que podemos encontrar y tiene lugar cuando hay que ordenar previamente el resultado de ambas tablas, lo que genera mayor carga de datos.

Producto Cartesiano

Esto es el terror de las SELECT, ya que recorre ambas tablas por completo. Esto ocurre porque la consulta está mal escrita y falta el JOIN entre ambas tablas.

 

Y con estos conceptos creo que podéis defenderos ante un EXPLAIN PLAN y entenderlo lo suficiente para hacer las tareas de optimización típicas en desarrollos. Claro está este tema puede profundizarse muchísimo. El Explain Plan muchas veces indica al motor como tiene que ejecutar la consulta, es más, si lanzamos un EXPLAIN PLAN - al menos en Oracle - de una consulta, su rendimiento mejorará, ya que el motor lo almacena en caché. Por ejemplo, existe un parámetro, OPTIMIZER_MODE (nuevamente hablo de Oracle), que sirve para indicar si queremos optimizar la consulta para búsquedas de todos los campos, para devolver todos los campos, para seguir reglas definidas por nosotros...pero como digo, esto ya es tarea de un DBA y yo por desgracia sólo soy una humilde (y pobre) desarrolladora.

Y ahora ya sí que sí ponemos punto y final a las Pildoritas de Bases de Datos. Esta ha sido la primera vez que me animo a escribir sobre un tema técnico y aunque seguramente haya cometido más de un fail, deseo de corazón que estas pildoritas os hayan hecho disfrutar una décima parte de lo que yo lo he hecho escribiéndolas. No sólo ha sido un reto el conseguir organizar las ideas para explicarlas de forma comprensible a otros, sino que además me ha servido para volver a refrescar muchas cosas que ya había olvidado.

Algunos pensaréis que es imposible unir contenido técnico y disfrute en la misma frase, pero para que veáis que hablo en serio os diré que ya tengo en mente una segunda serie de Pildoritas en mente, esta vez sobre POO y/o Java. Todavía es una idea embrionaria, y ni siquiera he pensado aún sobre cómo preparar y organizar el temario, ni que incluir en él exactamente. No obstante, me apetece muchísimo seguir haciendo Pildoritas, por lo que esto no es un Adiós, sino un Hasta Luego 🙂

Índice de Pildoritas

Share
30
Oct 13

Pildoritas de Bases de Datos: Optimización. Condiciones SARGABLE

pildoras bbdd

Después de la pildorita de la semana pasada sobre cuestiones de optimización de operadores, aprovecho que tenéis el tema fresco y sobre todo que escribí ambos posts de forma casi simultanea, para desentrañar por fin que es eso de SARGABLE xD

SARGABLE viene del acrónico SARG (Search ARGument) y se refiere a una clausula WHERE que compara una columna con una constante. ¿Y eso que significa? Pues que un WHERE es SARGABLE cuando se puede apoyar en índices para mejorar la respuesta. Los WHERE NON-SARGABLE, en cambio, son aquellos que no pueden ayudarse de índices a la hora de realizar el filtrado de datos. Ahora ya entendeis por qué decía en el tema anterior que había que evitar los operadores NON-SARGABLE.

Como vimos ya por encima, hay algunos operadores que pueden impedir (aunque no siempre) la utilización de índices en un WHERE. Los operadores en cuestión son: “IS NULL”, “<>”, “!=”, “!>”, “!<” (estados dos en caso de que existan para ese motor, ya que no son comunes), “NOT”, “NOT EXIST” , “NOT IN”, “NOT LIKE”. Básicamente todas las que usan NOT)

Además, las expresiones que incluyen una función sobre una columna, comparaciones contra una columna - no es una constante - u operadores con la misma columna en ambos lados, son también NON-SARGABLE.

No obstante, a veces que haya alguna condición con estos operadores no significa necesariamente que el WHERE sea SARGABLE, porque otra condición puede hacer que la consulta utilice un índice que evite el TABLE/INDEX SCAN (es lo que ocurre con WHERE NON-SARGABLE) y realice un COVERED INDEX sin necesidad de utilizar el índice. Esto ocurre cuando el índice incluye los campos devueltos por el SELECT y los del JOIN. Por otra parte, un WHERE puede también estar compuesto por otra clausula que tire de otro índice.

Además, no siempre viene bien hacer un COVERED INDEX, ya que cuando los índices son muy extensos puede aumentar mucho la utilización de CPU y bus de Entrada / Salida, que puede resultar en algunos casos más perjudicial incluso que una query que no utilice índices.

Al igual que en el resto de pildoritas de optimización, vamos a ver una serie de pautas que pueden llevar a mejoras de rendimiento en las consultas.

Pautas para convertir condiciones NON-SARGABLE a SARGABLE.

No utilizar funciones sobre columnas

Como decíamos en la introducción, ésta es una de las causas más comunes de que una condición sea NON-SARGABLE, y muchas veces es perfectamente reescribible a una condición que no se apoye en la columna, sino en una constante (literal, parámetro).

Imaginad que queremos obtener aquellos empleados cuyo Apellido comience por la M. La query sería tal que así:

SELECT NOMBRE, APELLIDO FROM EMPLEADO WHERE SUBSTRING(APELLIDO, 1, 1) = ‘M’

Esta query utiliza sobre la columna APELLIDOS la función SUBSTR, por lo que no es SARGABLE, pero con un pequeño cambio de operador podemos tener el mismo resultado y una query SARGABLE.

SELECT NOMBRE, APELLIDO FROM EMPLEADO WHERE APELLIDO LIKE ‘M%’

Como veis, utilizando el LIKE dejamos de utilizar una función sobre la columna por lo que el WHERE pasa a ser SARGABLE. Más adelante entraremos en consideraciones particulares del operador LIKE, ya que dependiendo de algunos factores de la cadena a comparar, este puede ser NON-SARGABLE.

Otro ejemplo podría ser el de una query que buscara los proyectos que empiezan en menos de un mes de una fecha introducida por parámetro.

SELECT ID_PROYECTO FROM PROYECTO 
WHERE DATEDIFF(FECHA_INICIO , :FECHA_PARAMETRO) < 30

Esta query no es SARGABLE porque se está aplicando una función a una columna. Ahora bien, si pensamos un poco podemos sacar una query equivalente, eso sí, utilizando otro operador.

SELECT ID_PROYECTO FROM PROYECTO 
WHERE FECHA_INICIO > DATE_ADD(:FECHA_PARAMETRO , INTERVAL 30 DAYS)

Operador NOT

El operador NOT siempre es NON-SARGABLE, pero de igual que en el caso anterior, se pueden encontrar formas diferentes de escribir una consulta sin necesidad de usarlo.

Por ejemplo, vamos, a imaginar que nuestra tabla Empleado tiene un campo SALARIO, donde se indica la cantidad de dinero que cobra el trabajador bruto al año.

Si quisiéramos obtener aquellos trabajadores que no cobran más de 20.000€ brutos / años, nuestra primera idea podría ser ésta:

SELECT NOMBRE, APELLIDOS FROM EMPLEADO WHERE NOT SALARIO > 20.000

*También podríamos haber usado el operador !>, que funciona exactamente igual, pero este operador es muy poco común en la mayoría de motores.

Una forma muy sencillita de variar esta consulta y no utilizar el operador NOT es la siguiente:

SELECT NOMBRE, APELLIDOS FROM EMPLEADO WHERE SALARIO <= 20.000

Utilización de índices por otras vías

Pese a estos consejos, muchas veces no podemos deshacernos de las clausulas NON-SARGABLE. En estos casos se pueden definir índices computados, es decir, índices que son resultado de aplicar una función.

Por ejemplo, podríamos definir un índice para las iniciales de apellidos que fuera IDX(SUBSTRING(APELLIDO, 1, 1))

Esta solución sólo debe utilizarse cuando la query se lance constantemente y no haya forma de intentar quitar la función de la propia columna, ya que los índices computados requieren una sobrecarga de la BBDD importante.


Y ya con esto ponemos punto y final a la idea inicial de optimización que os comenté en este post, pero que de nuevo he vuelto a cambiar.

Al hablaros en este tema de cuestiones como TABLE SCAN o COVERED INDEX, he creído conveniente hacer una pildorita más explicando de forma muy básica como lanzar un explain plan e interpretarlo.

Así que nada, todavía queda una pildorita más de BBDD, que odio esto de las despedidas xD

Índice de Pildoritas

Share
23
Oct 13

Pildoritas de Bases de Datos: Optimización. Consideraciones de rendimiento de los operadores

pildoras bbdd

¡Sorpresa! Sí, tras tan sólo una semana de espera vuelven de nuevo las Pildoritas de Bases de Datos. La pildorita de hoy está dedicada a consideraciones de rendimiento de los operadores... OH, WAIT!

Si, dije que primero veríamos los WHERE SARGABLE, pero también que me estaba costando horrores organizarme las ideas y que no era definitivo (ya me imaginaba que esto podía ocurrir xD), así que sintiéndolo mucho, tendréis que esperar un poco más para salir de dudas con el tema del WHERE SARGABLE (culpa vuestra por no haberlo buscado ya 😀 )

Que conste que comencé con el tema de NON-SARGABLE primero, pero al ver que había temas que se me iban a mezclar con los operadores, no me quedó más remedio que darle la vuelta a la idea inicial y comenzar por éste tema.

Como decía (ya me empiezo a liar), en esta pildorita veremos cuestiones sobre el rendimiento de los operadores y posibles alternativas para evitar utilizar aquellos más costosos.

Tanto en esta pildorita como la siguiente hay que tener siempre claro que muchas de estas pautas no son más que simples consejos. La optimización es todo un mundo y muchas veces las pautas típicas de nada sirven y hasta es posible que lo contrario a lo que pensábamos sea la solución. En estos dos temas intentaré dejar siempre claro que esto no son reglas absolutas, pero me gustaría destacarlo para que así lo tengamos siempre presente.

Bueno, no me enrollo más, al lío xD

Rendimiento de operadores en el WHERE

Como decía hace un momento, el rendimiento de los operadores puede variar en cada consulta, pero se pueden clasificar de mejor a peor rendimiento de la siguiente forma:

  • =
  • >, >=, <, <=
  • LIKE
  • <>

A parte del operador en sí, también repercuten en el rendimiento otras cuestiones como el tipo de los operandos utilizados, el orden de estos, etc.

Ordenadas de mejor a peor rendimiento, estas son las otras pautas que también hay que tener en cuenta con respecto a los operadores:

  • Utilizar un literal único en lugar de varios
  • Utilizar un nombre de columna o un parámetro
  • Una expresión multiperando
  • Un número único exacto
  • Un número único no exacto al lado de un operador (date, time)
  • Datos de caracteres o NULL.

No obstante, esto pocas veces tiene mucha importancia ya que no es aplicable cuando el WHERE contiene más de una expresión, así que estos consejos rara vez podrán solucionaros la vida.

Operador IN

Siempre que podamos intentaremos sustituir este operador por cualquiera de las siguientes opciones.

EXISTS

NOT EXISTS, al estar conjugado con el operador NOT es NON-SARGABLE (por ahora sólo basta con saber que es malo xD), pero pese a ello su rendimiento es mejor que el IN muchas veces. Otras veces lo mejor es variar por IN utilizando el conjunto complementario de elementos, o en caso de que sea sólo un caso el comando EXISTS con esos complementarios, ya que es mucho más eficiente que el IN.

Vamos a verlo una vez más con un ejemplo. Imaginemos que queremos ver los datos de empleado de aquellos que no tengan proyecto.

SELECT NOMBRE, APELLIDOS, DNI, TELEFONO, ID_PUESTO, ID_COMPAÑIA 
FROM EMPLEADO WHERE ID_EMPLEADO NOT IN (
SELECT ID_EMPLEADO FROM EMPLEO_HAS_PROYECTO_HAS_DEPARTAMENTO
)

Esta query es transformable a:

SELECT NOMBRE, APELLIDOS, DNI, TELEFONO, ID_PUESTO, ID_COMPAÑIA 
FROM EMPLEADO 
WHERE ID_EMPLEADO EXISTS (
SELECT ID_EMPLEADO FROM EMPLEO_HAS_PROYECTO_HAS_DEPARTAMENTO
)

LEFT OUTER JOIN y chequear por la condición (si es un NOT IN se mirará que sea NULL)

La otra forma de convertir esta query es con un LEFT OUTER JOIN, chequeando que ese campo sea NULL.

De nuevo veamos con el mismo ejemplo la conversión a través del LEFT OUTER JOIN:

SELECT EMP.NOMBRE, EMP.APELLIDOS, EMP.DNI, 
EMP.TELEFONO, EMP.ID_PUESTO, EMP.ID_COMPAÑIA 
FROM EMPLEADO EMP LEFT JOIN EMPLEO_HAS_PROYECTO_HAS_DEPARTAMENTO EPD 
ON EMP.ID_EMPLEADO = EPD.ID_EMPLEADO 
WHERE EMP.ID_EMPLEADO IS NULL

Siempre que queramos comprobar solamente la existencia de un registro utilizaremos el EXISTS en vez del IN.

Si no es viable prescindir del operador IN debemos poner en la parte izquierda los valores más frecuentes de encontrar y al final de la lista los menos frecuentes.

¿Y el operador BETWEEN?

El operador BETWEEN comprueba que un valor se encuentre dentro de un rango, por lo que también puede sustituir en muchas ocasiones a la clausula IN y su rendimiento es mucho mejor. Siempre que podamos escoger, utilizaremos el operador BETWEEN.

SELECT ID_DEPARTAMENTO FROM DEPARTAMENTO WHERE ID_DEPARTAMENTO IN (1, 2, 3, 4)

Por:

SELECT ID_DEPARTAMENTO FROM DEPARTAMENTO WHERE ID_DEPARTAMENTO BETWEEN 1 AND 4

En el caso de que ID_DEPARTAMENTO tenga un índice (cosa casi segura porque es PK), al motor le resultará mucho más fácil calcular el resultado a través del operador BETWEEN.

Utilización del operador LIKE

Cuando utilicemos el operador LIKE debemos de intentar que nuestra cadena de búsqueda no comience jamás por una wildcard (%, *, etc), ya que en estos casos el operador no puede utilizar su índice, lo que le convierte en una clausula NON-SARGABLE y aumenta mucho más su coste.

Operador OR

En la primera pildorita os decíamos que una posible modificación para el UNION era usar OR, pero esto no siempre es verdad. Algunos motores de Bases de Datos como MySQL no utilizan los índices cuando hay operadores OR, por lo que a veces es mejor utilizar un UNION ALL a un OR.

Si tenéis dudas, lo mejor es tirar ambas queries y ver si vuestra tecnología tiene problemas de rendimiento con el OR.

Índice de Pildoritas

Share
16
Oct 13

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

pildoras bbdd
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