Lesson Learned #349: ColumnStoreIndex in a Temporary Table

[Jose_Manuel_Jurado]

Today, we received a service request where our customer got a performance issue reading 20 millions of rows in a temporal report in PowerBI. They provided to their final users multiple ways to filter, for example, grouping or sorting by different columns) using a subset of this data.

Based on this definition that the customer is using a temporary table to manage this subset of data, we suggested to create a columnstore index for this temporal table trying to achive the performance SLA with their users and reduce space and memory usage in Azure SQL Databases. 

-----------------------------------------------------------
-- Create a table for bulk insert process
-----------------------------------------------------------
DROP TABLE IF EXISTS [#T_SaleColumnStoreIndexBulkInsert]

CREATE TABLE [#T_SaleColumnStoreIndexBulkInsert](
	[Sale Key] [bigint] IDENTITY(1,1) NOT NULL,
	[City Key] [int] NOT NULL,
	[Customer Key] [int] NOT NULL,
	[Quantity] [int] NOT NULL,
        index [#SaleColumnStoreIndexBulkInsert_CI] CLUSTERED COLUMNSTORE
) 

INSERT INTO #T_SaleColumnStoreIndexBulkInsert
           ([City Key]
           ,[Customer Key]
           ,[Quantity])
     SELECT TOP 20020000
           [City Key]
           ,[Customer Key]
           ,[Quantity]
      FROM [FACT].[SaleColumnStoreIndex]

Once we have this data, our customer started to execute the reports getting a bery good performance for their internal reports. 

select  Dimension.City.[State Province], 
COUNT( DISTINCT [customer key]) AS UniqueCustomers, 
COUNT( DISTINCT [customer key]) AS Customers, 
SUM(Quantity) As Quantity 
from #T_SaleColumnStoreIndexBulkInsert 
INNER JOIN Dimension.City on Dimension.City.[City Key] = #T_SaleColumnStoreIndexBulkInsert.[City Key]
group by Dimension.City.[State Province]
order by Dimension.City.[State Province]

So, it is possible to create a columnstore index for a temporary table to improve the performance.

Enjoy!