En las últimas versiones de SQL Server y Office hemos visto cómo el motor vertipaq/xvelocity se ha incluido para distintas funcionalidades. Más concretamente hacen uso de este motor los índices columnares en el motor relacional, el modelo tabular de SSAS y PowerPivot dentro de Excel. Además, en algunos casos se combina con tecnología inmemory, como en el modelo tabular de SSAS. En este post vamos a verificar qué rendimiento podemos obtener, dado un mismo conjunto de datos, en un par de operaciones habituales.

Vamos a partir de la base de datos WideWorldImportesDW (nueva bbdd de ejemplo en SQL Server 2016). Como el número de filas que tenemos en la tabla de pedidos (Orders) es reducido, vamos a comenzar ampliando esta tabla hasta 1000 millones de filas. Este volumen de filas (o superior) es habitual en muchos DW a día de hoy. Para generar estas filas nos apoyaremos en los datos existentes y simplemente los reinsertaremos hasta alcanzar el volumen necesario:

-- Quitamos todos los índices menos el columnar
DROP INDEX [FK_Fact_Order_City_Key] ON [Fact].[Order]
GO
DROP INDEX [FK_Fact_Order_Customer_Key] ON [Fact].[Order]
GO
DROP INDEX [FK_Fact_Order_Order_Date_Key] ON [Fact].[Order]
GO
DROP INDEX [FK_Fact_Order_Picked_Date_Key] ON [Fact].[Order]
GO
DROP INDEX [FK_Fact_Order_Picker_Key] ON [Fact].[Order]
GO
DROP INDEX [FK_Fact_Order_Salesperson_Key] ON [Fact].[Order]
GO
DROP INDEX [FK_Fact_Order_Stock_Item_Key] ON [Fact].[Order]
GO
DROP INDEX [IX_Integration_Order_WWI_Order_ID] ON [Fact].[Order]
GO
ALTER TABLE [Fact].[Order] DROP CONSTRAINT [PK_Fact_Order]
GO

-- Multiplicamos el tamaño inicial de 231412 filas a 1851296


insert into [WideWorldImportersDW].[Fact].[Order] (
[City Key] ,
  [Customer Key] ,
  [Stock Item Key] ,
  [Order Date Key],
  [Picked Date Key] ,
  [Salesperson Key] ,
  [Picker Key] ,
  [WWI Order ID] ,
  [WWI Backorder ID] ,
  [Description],
  [Package] ,
  [Quantity] ,
  [Unit Price],
  [Tax Rate],
  [Total Excluding Tax],
  [Tax Amount],
  [Total Including Tax],
  [Lineage Key]
)
select 
[City Key] ,
  [Customer Key] ,
  [Stock Item Key] ,
  [Order Date Key],
  [Picked Date Key] ,
  [Salesperson Key] ,
  [Picker Key] ,
  [WWI Order ID] ,
  [WWI Backorder ID] ,
  [Description],
  [Package] ,
  [Quantity] ,
  [Unit Price],
  [Tax Rate],
  [Total Excluding Tax],
  [Tax Amount],
  [Total Including Tax],
  [Lineage Key]
 from [WideWorldImportersDW].[Fact].[Order]
 go 3 
 
 -- Guardamos estas filas en una temporal
select 
[City Key] ,
  [Customer Key] ,
  [Stock Item Key] ,
  [Order Date Key],
  [Picked Date Key] ,
  [Salesperson Key] ,
  [Picker Key] ,
  [WWI Order ID] ,
  [WWI Backorder ID] ,
  [Description],
  [Package] ,
  [Quantity] ,
  [Unit Price],
  [Tax Rate],
  [Total Excluding Tax],
  [Tax Amount],
  [Total Including Tax],
  [Lineage Key]
into #temp
from 
 [WideWorldImportersDW].[Fact].[Order]
 order by [Order Key]

go

-- Insertamos en batches hasta el tamaño final
insert into [WideWorldImportersDW].[Fact].[Order] (
[City Key] ,
  [Customer Key] ,
  [Stock Item Key] ,
  [Order Date Key],
  [Picked Date Key] ,
  [Salesperson Key] ,
  [Picker Key] ,
  [WWI Order ID] ,
  [WWI Backorder ID] ,
  [Description],
  [Package] ,
  [Quantity] ,
  [Unit Price],
  [Tax Rate],
  [Total Excluding Tax],
  [Tax Amount],
  [Total Including Tax],
  [Lineage Key]
)
select 
[City Key] ,
  [Customer Key] ,
  [Stock Item Key] ,
  [Order Date Key],
  [Picked Date Key] ,
  [Salesperson Key] ,
  [Picker Key] ,
  [WWI Order ID] ,
  [WWI Backorder ID] ,
  [Description],
  [Package] ,
  [Quantity] ,
  [Unit Price],
  [Tax Rate],
  [Total Excluding Tax],
  [Tax Amount],
  [Total Including Tax],
  [Lineage Key]
 from #temp
 go 540 
 -- 540 iteraciones de 1851296 filas más las 1851296 que ya teníamos generan 
 -- un total de filas de 1.001.551.136. 1000 millones de filas es algo habitual en un DW actual
 select count(*) from [WideWorldImportersDW].[Fact].[Order]
 go

Las consultas que realizaremos sobre esta base de datos serán las siguientes:

--Query 1
select C.Region, SUM(O.[Unit Price]*O.[Quantity]), count(*) from
 [WideWorldImportersDW].[Fact].[Order] O
 inner join WideWorldImportersDW.dimension.City C on O.[City Key]=C.[City Key]
 group by C.Region
 
 --Query2 
select O.[Order Date Key],SUM(O.[Unit Price]*O.[Quantity]), count(*) from
 [WideWorldImportersDW].[Fact].[Order] O
 group by O.[Order Date Key]
 order by O.[Order Date Key]

La primera consulta nos devolverá el total de los pedidos por región, así como el conteo de registros. En esta consulta estamos cruzando por una dimensión, por lo que se requiere de cierto procesamiento adicional de filtrado combinado con el acceso al índice columnar:

Vertipaq1

La segunda de ellas únicamente accede a la tabla de hechos para agrupar y ordenar por fecha los mismos agregados que teníamos en la consulta anterior:

Vertipaq2

La siguiente tabla muestra la duración media y el consumo medio de CPU tras realizar 3 ejecuciones de cada una de ellas:

QueryCPU (ms)Time (ms)
Query198442616
Query2103443385

Como podemos ver en ambos casos el tiempo total es notablemente inferior al tiempo total. Esto es debido a la presencia de paralelismo en ambos planes de ejecución (MAXDOP=4):Vertipaq4 Vertipaq3

En general el paralelismo es muy eficiente en este tipo de operaciones siempre que el reparto de filas sea equilibrado. En este caso obtenemos un reparto bastante uniforme (entre el 22 y el 28% de filas por thread):

Vertipaq5

La siguiente prueba consistirá será realizar estos mismos cálculos y agregaciones desde un PowerPivot donde hemos realizado la carga de la tabla de hechos Order y la dimensión City. Necesitaremos una máquina con bastante memoria parar poder gestionar este volumen (>28 GB de RAM). Mover este volumen de datos con PowerPivot resulta bastante arduo, por lo que todo apunta a que directamente PowerPivot no está preparado para tratar con este tipo de volúmenes de datos de forma ágil. Con memoria y un poco de paciencia conseguiremos cargar los 1000 millones de registros:

Vertipaq7

Una vez cargado lo que haremos es generar dos tablas pivotes que accediendo al modelo de PowerPivot mimeticen los resultados de las consultas 1 y 2. Los cálculos, como era de esperar, coinciden con los obtenidos en SQL Server en las consultas 1 y 2:

Vertipaq8

Vertipaq13

A falta de otro mecanismo para medir el consumo de CPU de una petición a PowerPivot utilizaremos performance monitor. Las mediciones mediante este sistema no son tan exactas como desde SQL Server, pero nos valdrán. Los resultados que obtenemos respecto a consumo de CPU y a tiempo total de CPU son, aproximadamente, los siguientes:

QueryCPU (ms)Time (ms)
Query1120003500
Query234900001775000

Podemos ver como PowerPivot puede mantener el tipo más o menos en la primera consulta, pero en la segunda que utiliza la agregación por fechas sufre horriblemente en el rendimiento. Durante la segunda consulta existe una fase inicial que utiliza 1 core al 100% y luego otra fase mucho más larga que utiliza 2 cores al 100% alargándose el procesado total a casi media hora:

Vertipaq10 Vertipaq12

Es decir, vemos que además de no utilizarse los 4 procesadores de forma concurrente, como sí ocurre en el caso de los índices columnares en SQL Server, el rendimiento es mucho peor. Probablemente está relacionado con no disponer de modos batch para las operaciones:

Vertipaq11

Una vez hemos realizado los tests con PowerPivot el siguiente paso será cargar un modelo tabular donde cargaremos las mismas dos tablas. La forma más sencilla es simplemente importar nuestro modelo de PowerPivot directamente:

Vertipaq6

Seleccionaremos el modelo y lo cargaremos directamente. Este proceso tiene la ventaja que trabaja con el formato comprimido de las columnas de forma nativa por lo que es sustancialmente más rápido que si cargáramos los datos manualmente desde el origen:

Vertipaq14

Una vez cargado conectaremos Excel contra el modelo tabular:

Vertipaq15

Cuando intentamos crear una tabla pivote contra dicho modelo, nos encontramos con que no podemos crear medidas indirectas por lo que no podemos añadir los cálculos necesarios. Necesitaremos crear un proyecto en VS importando el modelo tabular (con las SSDT instaladas) y o bien creamos medidas manualmente o seleccionamos la propiedad “Summarize By” al valor que necesitemos (en nuestro caso una columna sumada y otra con conteo):

Vertipaq17

Una vez hecho el cambio, realizaremos un redeploy contra nuestro servidor de tabular para que dichas medidas estén disponibles. Configuraremos ahora las mismas Pivot Tables que utilizamos para simular las consultas 1 y 2 en el caso de PowerPivot pero esta vez accediendo al modelo tabular directamente:

Vertipaq19

Vertipaq18

Los tiempos de ejecución y de consumo de CPU son los siguientes:

QueryCPU (ms)Time (ms)
Query11875672
Query245921312

Si resumimos el comportamiento en cada consulta para cada opción queda claro que el modelo tabular es el más rápido con una diferencia importante respecto a los índices columnares y con una diferencia mucho mayor comparado con PowerPivot:

AlternativaQueryCPU (ms)Time (ms)
ColumnarQuery198442616
ColumnarQuery2103443385
PowerPivotQuery1120003500
PowerPivotQuery234900001775000
TabularQuery11875672
TabularQuery245921312

En base a todo esto podemos ver que la opción más lenta para este tipo de operaciones es el modelo PowerPivot. Obviamente este modelo tiene otras ventajas como el poder ser portable directamente en un fichero, pero no parece funcionar bien para volúmenes de datos de un tamaño medio. Es decir, debemos reservar el PowerPivot para volúmenes de datos pequeños.

El modelo tabular en memoria tiene la gran ventaja del mayor rendimiento y que nos aseguramos tiempos de respuesta estables ya que no dependemos del acceso a disco. El modelo con índices columnares en el motor relacional no puede competir en rendimiento al mismo nivel que el tabular, aunque puede que en algunos escenarios donde requiramos el acceso al dato actualizado al último segundo pueda ser un buen “compañero” para un modelo tabular. Es decir, podemos mantener en el tabular, in-memory, aquella información que no varíe rápidamente en el tiempo y pueda soportar nuestro ciclo de refresco y por otra parte con una conexión que fuerce el modo DirectQuery accederíamos al relacional en aquellos casos específicos donde sea necesario.

Rubén Garrigós

Mentor at SolidQ
I am an expert in high-availability enterprise solutions based on SQL Server design, tuning, and troubleshooting. Over the past fifteen years, I have worked with Microsoft data access technologies in leading companies around the world.

Nowadays, I am a Microsoft SQL Server and .NET applications architect with SolidQ. I am certified by Microsoft as a Solution Expert on the Microsoft Data Platform (MSCE: Data Platform) and as a Solution Expert on the Microsoft Private Cloud (MSCE: Private Cloud). As a Microsoft Certified Trainer (MCT), I have taught multiple official Microsoft courses as well as other courses specializing in SQL Server. I have also presented sessions at official events for various Microsoft technologies user groups.
Rubén Garrigós