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.
Enjoy!
