Bad Hash Distribution Column Selection On Synapse Analytics Dedicated SQL Pool Will Impact Query Performance.

Microsoft gives guideline for designing Fact Table as stated as follows:

Common design table guideline.

There is also a good reference to read about hash-distribution data on tables (especially for Fact table).

But what if we did not follow the guideline? If we wrongly design, is it really impacted to something? This article will show you on extreme case, if we design hash-distribution with bad strategy.

For our testing, we use capacity tier DWU100c, and we use simple Dim and Fact schema as follows:

CREATE TABLE [DimMember](
 [Code] [varchar](20) NOT NULL,
 [CMID] [numeric](18, 0) NOT NULL 
); 

CREATE TABLE [FactTrade] ( 
 [ExchangeId] [smallint] NOT NULL, 
 [TradeIdentifier] [int] NOT NULL, 
 [BusinessDateId] [smallint] NOT NULL, 
 [ProductId] [int] NOT NULL, 
 [Price] [numeric](18, 2) NOT NULL, 
 [Quantity] [numeric](20, 5) NULL, 
 [SellerMemberId] [int] NOT NULL, 
 [BuyerMemberId] [int] NOT NULL 
); 

On Dim table, we use replicate distribution. On Fact table, we create two tables with same schema and same content (129 millions of rows). The difference is one with round-robin distribution and the other hash distribution using column ExchangeId.

Column ExchangeId has only two possible values: 1 (2 millions of rows) and 2 (127 millions of rows). We choose this column to show performance contrast caused by extreme case when bad distribution strategy is applied.

Get Monce Picard’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

To show distribution on table, we use command:

DBCC PDW_SHOWSPACEUSED('dbo.FactTrade'); 

We can show the result:

Data distribution on table with round-robin mode.

For the sake of space, we only show node 1–26 (column Distribution_Id), but complete result will show node 1–60. The node 27–60 show rows count (column Rows) in around 2,163,200–2,160,600.

Data distribution on table with hash mode on column ExchangeId.

For the sake of space, we only show node 1–25, and node 43 (column Distribution_Id), but complete result will show node 1–60. The node 27–60 show rows count (column Rows) as 0.

We use this query to test the table:

SELECT dcm.code 
  , sum (ft.quantity * ft.price) as totalAmount 
FROM [dbo].[DimMember] dcm 
inner join [dbo].[FactTrade] ft 
on dcm.MemberId = ft.BuyerMemberId 
group by dcm.code 

The table Fact with round robin distribution returns result in around 8 seconds. The table Fact with hash distribution returns result in around 1 minutes. Furthermore, using this guideline we can investigate to understand why result contrast happened.

Distribution run times for a SQL step.

Nodes with most rows need time to process data longer. Node 22 has 127 millions of rows, node 43 has 2 million of rows.

On Fact table, especially when data count is very huge, choosing column for hash distribution is very critical. Understanding our data shape will help designing for best performance result.