Prueba de rendimiento con SQL Server 2016
Aquí tenéis una prueba de rendimiento de los índices columnares (Columstore) en soluciones Business Intelligence.
Tras mucho leer sobre la eficiencia de los índices columnares (Columstore) cuando se usan con consultas de agregados típicas de soluciones Business Intelligence me he decidido a hacer una prueba.
Para ello he tomado una tabla procedente de una solución ya en producción desarrollada sobre SQL Server 2008; los datos de la tabla sin índices ni claves eran estos:
CREATE TABLE [dbo].[Avisos](
[AvisoKey] [int] IDENTITY(1,1) NOT NULL,
[HoraAviso] [datetime] NULL, [HoraRecepcionAviso] [datetime] NULL,
[TiempoLlegadaAviso] [bigint] NULL [CodTarjeta] [nvarchar](10) NULL,
[Matricula] [nvarchar](10) NULL,
[NomPersona] [nvarchar](60) NULL,
[NumMensaje] [int] NULL,
[Mensaje] [nvarchar](60) NULL,
[ValPresencia] [int] NULL,
[TextoPresencia] [nvarchar](50) NULL,
[TipoMensaje] [int] NULL,
[NomLector] [nvarchar](40) NULL,
[TipoDispositivo] [int] NULL,
[CodEmpresa] [nvarchar](10) NULL,
[CodTarjetaPortatil] [nvarchar](10) NULL,
[Oculto] [nvarchar](1) NULL,
[TecnologiaLector] [nvarchar](1) NULL,
[CodigoTarjetaVehiculo] [nvarchar](10) NULL,
[Extension] [int] NULL)
ON [PRIMARY]
Espacio de datos: 6.015,477 MB
Espacio de índices: 0,023 MB
Número de filas: 24.587.384
La consulta que he empleado para la prueba es esta:
SET STATISTICS TIME ON
SELECT TOP 5 Mensaje, SUM(TiempoLlegadaAviso), AVG(TiempoLlegadaAviso)
FROM dbo.Avisos
WHERE TiempoLlegadaAviso > 5 AND TipoMensaje=1
GROUP BY Mensaje
He ido ejecutando sucesivamente la consulta en cuatro estados diferentes de indización obteniendo los siguientes resultados:
1 – Tabla sin índices:
Espacio de datos: 6.015,477 MB
Espacio de índices: 0,023 MB
Tiempo de CPU = 10985 ms
Tiempo total = 11100 ms
2 – Tabla con una clave primaria:
ALTER TABLE dbo.Avisos ADD CONSTRAINT PK_Avisos PRIMARY KEY CLUSTERED (AvisoKey)
Espacio de datos: 6.017,742 MB
Espacio de índices: 9,734 MB
Tiempo de CPU = 10562 ms
Tiempo total = 10778 ms
3 – Tabla con un índice agrupado creado para optimizar la consulta:
ALTER TABLE dbo.Avisos DROP CONSTRAINT PK_Avisos CREATE CLUSTERED INDEX CI_Avisos ON dbo.Avisos(TipoMensaje)
Espacio de datos: 6.163,758 MB
Espacio de índices: 18,383 MB
Tiempo de CPU = 9094 ms
Tiempo total = 9178 ms
4 – Tabla con un índice no agrupado columnar creado para cubrir la consulta:
CREATE NONCLUSTERED COLUMNSTORE INDEX NCCI_Avisos ON dbo.Avisos(AvisoKey, NomLector, TiempoLlegadaAviso, TipoMensaje, Mensaje)
Espacio de datos: 6.358,148 MB
Espacio de índices: 212,773 MB
Tiempo de CPU = 47 ms
Tiempo total = 121 ms
Las sucesivas creaciones y borrado de índices se han hecho sin reorganizarlos ni optimizar las estructuras de la tabla de manera alguna. Podemos ver en las cifras de arriba que el espacio empleado por la tabla crece a medida que se crean nuevos índices aunque no lo hace en exceso.
La parte interesante viene en los tiempos empleados en la consulta; podemos ver que la sola creación de una clave primaria y su índice agrupado asociado proporciona un incremento aproximado del 3% en la velocidad de ejecución. No es mucho aunque se trata de un índice, digamos, de uso genérico. En el segundo caso, hemos creado un índice agrupado en una de las columnas del filtro (WHERE) de la consulta; en concreto de aquella para la cual buscamos un valor concreto (TipoMensaje). Para ello hemos tenido que eliminar la clave primaria ya que sólo podemos tener un índice agrupado. En este caso la mejora en la velocidad de ejecución es del 15% sobre la obtenida con sólo la clave primaria.
Finalmente, sin eliminar el índice agrupado, creamos un índice columnar no agrupado que cubra la consulta… y los resultados son espectaculares. La mejora es de un 99%. Llegados a este punto uno me he preguntado si todo el mérito se debía al índice columnar, así que he borrado el índice agrupado y los resultados han sido estos. Espacio de datos: 6.367,406 MB Espacio de índices: 203,68 MB Tiempo de CPU = 781 ms Tiempo total = 986 ms Un empeoramiento del 814%. En cifras absolutas sigue siendo un buen resultado pero, como se ha visto, mejorable.
