Why Synapse Analytics chooses a much bigger table to broadcast when joining

user_7827937 0
Viewed 337

I have two table. table 1 is around 800 rows and the other table has 80.000.000 rows.

I make a simple join between the table. It really frustrates me that it keeps broadcasting the big table when joining, instead of the small one, causing serious performance problem.

Even if I use option such as "MERGE JOIN", it still broadcasts the big table. (I come from Spark background, and if I understand right, it should shuffle (to sort merge) instead of keeping broadcasting anything.)

The execution plan looks like this.

enter image description here

I never have such problem with such simple joins when working with Spark. Can somebody help me with this.

azure-synapse
1 Answers

In order to avoid data movement operators in execution plans you should set up your table distribution properly. The best practice here would be to REPLICATE your smaller table and to HASH distribute your large table on a suitable column that provides good distribution.

Some sample DDL:

CREATE TABLE fact.yourBigTable (

    ...

)
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH( someColumn )
);


CREATE TABLE dim.yourSmallTable (

    ...

)
WITH
(
    CLUSTERED INDEX ( someColumn ),
    DISTRIBUTION = REPLICATE
);

Obviously you have to experiment with your workload and data to find the combination. Generally I would say updating columnstore indexes isn't a great idea: consider CTAS or another alternative.

user_15275040
Related