Optimización de consultas SQL en IRIS

¡Hola a todos los estimados miembros de la comunidad de desarrolladores de InterSystems en español!

Aunque suelo consultar la comunidad de desarrolladores y alguna vez he dejado alguna pregunta por aquí, este será mi primer artículo y qué mejor ocasión para hacerlo que participando en el 3er concurso de artículos técnicos.

El tema que trataré en este artículo será el de las diferentes herramientas que nos proporciona InterSystems IRIS para optimizar tanto las bases de datos como las consultas que ejecutamos así como los nuevos tipos de almacenamiento y de datos.

Optimizando nuestras consultas SQL

Para este artículo he montado en una instancia de InterSystems IRIS Community un pequeño ejemplo importando un CSV con 5 millones de registros de hipotéticas ventas realizadas a lo largo de los años en diferentes ciudades.

La estructura de las tablas será muy sencilla, pero suficiente para realizar las pruebas que necesitamos, disculpad los nombres, pero ya sabéis que no hay nada más permanente que una solución temporal:

Sales.Record

Concurso.Localidad

Vamos a intentar extraer cual es la media histórica de las ventas de una ciudad como Valladolid en un año determinado:

Vamos a ver lo que tarda nuestra consulta en devolvernos la media de ventas mensuales:

El resultado ha sido un poco más de 8 segundos en retornar el valor medio de las ventas mensuales Si veis disponemos de una opción que nos permitirá conocer el plan de ejecución de la consulta (Mostrar plan). Veamos paso a paso que va a hacer nuestra consulta:

Veamos el módulo H que tiene una información bastante intesante:

• Divide master map Concurso.Localidad(L).IDKEY into subranges of IDs.

• Call module A in parallel on each subrange, piping results into temp-file D.
 

Estas dos líneas lo que nos cuentan es que primeramente dividiremos la tabla Concurso.Localidad por rangos de ID y llamaremos al módulo A de forma paralela para cada brango obtenido, esta computación en paralelo implementada por defecto en IRIS acelerará nuestra consulta sin necesidad de indicarlo.

Como veis nuestra consulta es bastante costosa, ¿a qué es debido? Como podéis ver nuestra consulta está recorriendo por entero la tabla Concurso.Ventas con sus 5 millones de consultas y para cada fila le va a aplicar la condición de que la ciudad sea "Valladolid" y el año determinado. Esto como podéis imaginar no es lo más óptimo. ¿Cómo podríamos mejorar el rendimiento?

Añadiendo índices

Como bien sabréis, indexar una columna nos permitirá mejorar el rendimiento de aquellas consultas que contengan una condición sobre dicha consulta, por lo que vamos a probar incluyendo un índice sobre nuestra columna Anyo de nuestra tabla Sales.Record:

Creamos nuestro índice del tipo BITMAP ya que contendrá un conjunto bastante pequeño de diferentes valores y mejorará el rendimiento de nuestras consultas sobre un índice normal (aquí podéis leer la documentación asociada).

Este índice no va a modificar el plan de ejecución, pero lo que hará será acelerar la consulta, veamos el resultado:

Como podemos ver nuestro rendimiento ha mejorado, echemos un vistazo al plan de consulta con el índice incluido:

Como podéis ver, en el módulo B ya no leemos la tabla Concurso.Ventas entera con sus 5 millones de registros, si no que mediante el índice extraemos previamente todos aquellos valores con el Anyo 1996, de tal forma que excluimos de la lectura los no coincidentes.

¿Buena mejora, no? Pero no nos quedemos aquí, podemos mejorar más aún con el...

Optimizador de consultas

InterSystems IRIS nos proporciona un optimizador de consultas por defecto que utilizará determinadas herramientas de cara a definir el plan de consulta, no es lo mismo realizar una consulta directamente sobre la tabla con 5 millones de registros de ventas que hacerlo primero sobre la de localidades y a continuación sobre la de ventas excluyendo los registros que no pertenezcan a la localidad solicitada.

La forma más sencilla de optimizar las tablas es ejecutando un sencillo TUNE TABLE sobre la tabla que queremos optimizar. Este comando nos configurará en la definición de la clase de nuestra tabla los siguientes parámetros:

• ExtentSize: este parámetro contendrá el número de filas de la tabla, podemos definirlo nosotros mismos poniendo el valor que queramos siempre y cuando guarde una proporción con los valores reales respecto a las demás tablas,
• Selectivity: el porcentaje que representa cada distinto valor de la columna respecto al total, es decir, si tenemos sólo 2 tipos de valores su Selectivity será el 50%, si tenemos 64 como el caso de las localidades será 1.56% si no tenemos valores repetidos.
• BlockCount: con el valor estimado de bloques del mapa que usará por cada mapa de SQL basado en el ExtentSize.

Echemos un vistazo a la sección Storage de la clase Concurso.Ventas antes de aplicaciar el tunning a la tabla:

Storage Default
{
<Data name="VentasDefaultData">
<Value name="1">
<Value>%%CLASSNAME</Value>
</Value>
<Value name="2">
<Value>SumaVentasMensuales</Value>
</Value>
<Value name="3">
<Value>%Source</Value>
</Value>
<Value name="4">
<Value>Mes</Value>
</Value>
<Value name="5">
<Value>Año</Value>
</Value>
<Value name="6">
<Value>CodigoCiudad</Value>
</Value>
</Data>
<DataLocation>^Concurso.VentasD</DataLocation>
<DefaultData>VentasDefaultData</DefaultData>
<ExtentSize>2000000</ExtentSize>
<IdLocation>^Concurso.VentasD</IdLocation>
<IndexLocation>^Concurso.VentasI</IndexLocation>
<SQLMap name="AñoIdx">
<BlockCount>-10634</BlockCount>
</SQLMap>
<StreamLocation>^Concurso.VentasS</StreamLocation>
<Type>%Storage.Persistent</Type>
}

Como podéis ver tiene una serie de valores por defecto que no ayudarán a mejorar nuestras consultas sobre ella. Ejecutemos el comando TUNE TABLE para ambas tablas y veamos que consecuencias tiene tanto sobre nuestra clase como sobre nuestra consulta.

Veamos nuestra sección Storage:

Storage Default
{
...
<ExtentSize>6468826</ExtentSize>
<Property name="Año">
<AverageFieldSize>4</AverageFieldSize>
<Histogram>...</Histogram>
<Selectivity>2.9412%</Selectivity>
</Property>
<Property name="CodigoCiudad">
<AverageFieldSize>6.7</AverageFieldSize>
<Histogram>...</Histogram>
<Selectivity>3.3333%</Selectivity>
</Property>
<Property name="Mes">
<AverageFieldSize>3</AverageFieldSize>
<Histogram>...</Histogram>
<Selectivity>8.3333%</Selectivity>
</Property>
<Property name="SumaVentasMensuales">
<AverageFieldSize>5</AverageFieldSize>
<Histogram>...</Histogram>
<OutlierSelectivity>.000393:794574</OutlierSelectivity>
<Selectivity>0.0002%</Selectivity>
</Property>
<SQLMap name="$Ventas">
<BlockCount>-48</BlockCount>
</SQLMap>
...
}

Ahora tanto Concurso.Localidad como Concurso.Ventas están correctamente configuradas, veamos su impacto en el plan de ejecución de la consulta:

La mejora es de un 31% respecto a la consulta anterior, y su coste es menos de la mitad de la consulta original, veamos cuanto tarda ahora nuestra consulta:

Así es, como podéis ver hemos pasado de 8 segundos inicialmente a 4.6 incluyendo los índices adecuados a nuestras tablas a poco menos de 1 segundo aplicando TUNE TABLES.

Podéis consultar en está página más posibles optimizaciones para vuestras consultas como por ejemplo %FirstTable, que forzará al optimizador a leer la tabla seleccionada en primer lugar y que puede resultar interesante para limitar las lecturas sobre aquellas tablas de grandes dimesiones filtradas por valores procedentes de la seleccionada.

Almacenamiento columnar o Columnar Storage

InterSystems IRIS no sólo nos proporciona las anteriores herramientas para sacarle el máximo partido a nuestras consultas, sino que también pone a nuestro alcance la funcionalidad de almacenamiento columnar destinado para casos como el ejemplo utilizado para este artículo, grandes volúmenes de datos superior al millón de registros sobre el que queremos realizar operaciones de agregación como SUM o AVG.

Este almacenamiento columnar lo podemos definir a nivel de tabla o bien a nivel de columna, como más nos interese. Para que se entienda mejor que es el almacenamiento columnar usaré una imagen de la propia documentación de ISC.

Atendiendo a la explicación podréis ver como para nuestro ejemplo puede ser interesante definir la columna de ventas mensuales como un almacenamiento de tipo columnar, para ver las diferencias he vuelto a montar de 0 las tablas sin índices y sin la ejecución del TUNE TABLE.

El único cambio necesario en Concurso.Ventas para que nuestra columna SumaVentasMensuales utilice el almacenamiento columnar será definir la siguiente propiedad:

Property SumaVentasMensuales As %Integer(STORAGEDEFAULT = "columnar");

Comprobemos ahora el plan de la consulta.

El coste de nuestra consulta con almacenamiento columnar es de 65.487.882, mientras que con almacenamiento en filas era de 100.529.682, una mejora sustancial únicamente modificando el tipo de almacenamiento a utilizar. Veamos cuanto tarda la consulta en ejecutarse:

Algo más de 1 segundo comparado con los más de 8 segundos que tardó con el almacenamiento vectorial.

Cierre y despedida

Aquí terminamos este artículo sobre optimización de consultas SQL con InterSystems IRIS que espero os haya resultado de utilidad.