Lesson Learned #349: ColumnStoreIndex in a Temporary Table

Posted by

This post has been republished via RSS; it originally appeared at: Azure Database Support Blog articles.

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.



Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.