Lesson Learned #249: All started with the phrase: In PowerBI Direct Query is slow-Partitioned table

[Jose_Manuel_Jurado]

In some situations, customers that are using PowerBI and Direct Query reported performance issues depending how the query has been defined by PowerBI. In this scenarion, I would like to share with you how we fixed this performance issue using Partitioning

For this example, I download a demo database Release Wide World Importers sample database v1.0 · microsoft/sql-server-samples · GitHub and duplicate rows of a table Fact.Sale until having 234.767.360 rows.  I choose HyperScale Database tier basically as a medium size database for OLAP. 

In every analysis of performance with PowerBI, if you need to know how many rows we have per table use the following TSQL, instead of using SELECT COUNT() for performance improvements.

SELECT 
    t.NAME AS TableName,
    s.Name AS SchemaName,
    max(CASE i.type WHEN 5 THEN si.rowcnt ELSE p.rows END) AS RowCounts,
    SUM(a.total_pages) * 8 AS TotalSpaceKB, 
    SUM(a.used_pages) * 8 AS UsedSpaceKB, 
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
    sys.tables t
INNER JOIN      
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN      
    sysindexes si ON t.OBJECT_ID = si.id
INNER JOIN 
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
    sys.schemas s ON t.schema_id = s.schema_id
WHERE t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255 
GROUP BY 
    t.Name, s.Name
ORDER BY 
    t.Name, s.Name

Define the report. 

In this situation, we have a report where we need to obtain filtering per Fiscal Month Label by FY2013-Aug, FY2014-Aug and FY2015-Aug the Quantity sold per item. 

PowerBI generates the following TSQL statement.

Using Azure Data Studio and SQL Server Profiler extension we could see the query.

SELECT 
TOP (1000001) *
FROM 
(

SELECT [t3].[Fiscal Month Label] AS [c43],[t5].[Stock Item] AS [c59],SUM(
CAST([t6].[Quantity] as BIGINT)
)
 AS [a0]
FROM 
(
((
select [$Table].[Sale Key] as [Sale Key],
    [$Table].[City Key] as [City Key],
    [$Table].[Customer Key] as [Customer Key],
    [$Table].[Bill To Customer Key] as [Bill To Customer Key],
    [$Table].[Stock Item Key] as [Stock Item Key],
    [$Table].[Invoice Date Key] as [Invoice Date Key],
    [$Table].[Delivery Date Key] as [Delivery Date Key],
    [$Table].[Salesperson Key] as [Salesperson Key],
    [$Table].[WWI Invoice ID] as [WWI Invoice ID],
    [$Table].[Description] as [Description],
    [$Table].[Package] as [Package],
    [$Table].[Quantity] as [Quantity],
    [$Table].[Unit Price] as [Unit Price],
    [$Table].[Tax Rate] as [Tax Rate],
    [$Table].[Total Excluding Tax] as [Total Excluding Tax],
    [$Table].[Tax Amount] as [Tax Amount],
    [$Table].[Profit] as [Profit],
    [$Table].[Total Including Tax] as [Total Including Tax],
    [$Table].[Total Dry Items] as [Total Dry Items],
    [$Table].[Total Chiller Items] as [Total Chiller Items],
    [$Table].[Lineage Key] as [Lineage Key]
from [Fact].[Sale] as [$Table]
) AS [t6]

 LEFT OUTER JOIN 

(
select [$Table].[Date] as [Date],
    [$Table].[Day Number] as [Day Number],
    [$Table].[Day] as [Day],
    [$Table].[Month] as [Month],
    [$Table].[Short Month] as [Short Month],
    [$Table].[Calendar Month Number] as [Calendar Month Number],
    [$Table].[Calendar Month Label] as [Calendar Month Label],
    [$Table].[Calendar Year] as [Calendar Year],
    [$Table].[Calendar Year Label] as [Calendar Year Label],
    [$Table].[Fiscal Month Number] as [Fiscal Month Number],
    [$Table].[Fiscal Month Label] as [Fiscal Month Label],
    [$Table].[Fiscal Year] as [Fiscal Year],
    [$Table].[Fiscal Year Label] as [Fiscal Year Label],
    [$Table].[ISO Week Number] as [ISO Week Number]
from [Dimension].[Date] as [$Table]
) AS [t3] on 
(
[t6].[Delivery Date Key] = [t3].[Date]
)
)


 INNER JOIN 

(
select [$Table].[Stock Item Key] as [Stock Item Key],
    [$Table].[WWI Stock Item ID] as [WWI Stock Item ID],
    [$Table].[Stock Item] as [Stock Item],
    [$Table].[Color] as [Color],
    [$Table].[Selling Package] as [Selling Package],
    [$Table].[Buying Package] as [Buying Package],
    [$Table].[Brand] as [Brand],
    [$Table].[Size] as [Size],
    [$Table].[Lead Time Days] as [Lead Time Days],
    [$Table].[Quantity Per Outer] as [Quantity Per Outer],
    [$Table].[Is Chiller Stock] as [Is Chiller Stock],
    [$Table].[Barcode] as [Barcode],
    [$Table].[Tax Rate] as [Tax Rate],
    [$Table].[Unit Price] as [Unit Price],
    [$Table].[Recommended Retail Price] as [Recommended Retail Price],
    [$Table].[Typical Weight Per Unit] as [Typical Weight Per Unit],
    [$Table].[Photo] as [Photo],
    [$Table].[Valid From] as [Valid From],
    [$Table].[Valid To] as [Valid To],
    [$Table].[Lineage Key] as [Lineage Key]
from [Dimension].[Stock Item] as [$Table]
) AS [t5] on 
(
[t6].[Stock Item Key] = [t5].[Stock Item Key]
)
)

WHERE 
(
([t3].[Fiscal Month Label] IN (N'FY2013-Aug',N'FY2014-Aug',N'FY2015-Aug'))
)

GROUP BY [t3].[Fiscal Month Label],[t5].[Stock Item]
)
 AS [MainTable]
WHERE 
(

NOT(
(
[a0] IS NULL 
)
)

)

• Basically, this query joins the table Dimension.Date with Fact.Sale by Delivery Date Key grouping by Stock Item. As you could see the almost time 95% of this query is filtering the table Fact.Sale to prepare the filter by Fiscal Month Label.

• To solve this issue and improve the query, the suggestion was to change the table Fact.Sale to a partitioned table. 

CREATE PARTITION FUNCTION myDateRangePF (date)
AS RANGE RIGHT FOR VALUES ('2013-01-01','2014-01-01','2015-01-01','2016-01-01')
GO
CREATE PARTITION SCHEME myPartitionScheme 
AS PARTITION myDateRangePF ALL TO ([PRIMARY]) 
GO

ALTER TABLE Fact.Sale DROP CONSTRAINT PK_fACT_SALE

GO
ALTER TABLE Fact.Sale ADD CONSTRAINT PK_fACT_SALE PRIMARY KEY NONCLUSTERED  ([Sale Key])
   WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
         ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX Fact_Sale_IX1 ON Fact.Sale ([Delivery Date Key])
  WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
        ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) 
  ON myPartitionScheme([Delivery Date Key])
GO

• After the implementation of this partitioning, we improved a lot (seconds) the execution because the table is already partitioned, and SQL server only needs to read the partitions associated with the filter.

Other advantages that we have creating these partitions is to reduce our maintenance plans, for example:

• If I need to delete one specific month, instead of running DELETE .. FROM .. WHERE using TRUNCATE with partition - TRUNCATE TABLE (Transact-SQL) - SQL Server | Microsoft Learn
• If I need to update statistics, instead of reading all the rows of my table, update only the most frecuent partitions reducing a lot of time - UPDATE STATISTICS (Transact-SQL) - SQL Server | Microsoft Learn and also you could work with incremental statistics. 
• Finally, you could rebuild the indexes for this specific partition ALTER INDEX (Transact-SQL) - SQL Server | Microsoft Learn

Enjoy!