Transient Table Distribution for Azure Synapse Analytics
Using the transient_table_distribution_style configuration option, you can specify if Hyper-Q should use a round-robin or hash distribution for the temp tables it creates. Round-robin is the default.
In previous releases, Hyper-Q created temp tables for emulations using a round-robin distribution, which could cause extensive data movement operations before it could resolve a query. This additional movement of data may slow down your queries, causing performance issues. For example, joining a round-robin table usually requires reshuffling the rows, which is a performance hit.
To learn more about round-robin and hash table distribution, see Guidance for designing distributed tables using dedicated SQL pool in Azure Synapse Analytics in the Microsoft documentation.
The syntax for transient_table_distribution_style is:
transient_table_distribution_style = hash_first_column | round_robin
where:
Parameter | Description |
|---|---|
| Specifies that Hyper-Q use the first column in a CREATE TABLE AS SELECT (CTAS) statement as the distribution hash column. This is the default Teradata behavior when no primary index is explicitly specified in a CTAS or CREATE TABLE statement. To learn more about The CREATE TABLE AS SELECT (CTAS) statement, see CREATE TABLE AS SELECT (CTAS) in the Microsoft documentation. |
| Specifies that Hyper-Q create temp tables using the Azure Synapse round-robin distribution type. |
You can specify transient_table_distribution_style as a global setting in theHyper-Q configuration file (dtm.ini), as part of a query using @dtm_, or using the dtm set and dtm reset commands.
You can specify transient_table_distribution_style as a global setting in the Hyper-Q configuration file (dtm.ini). When set as a global setting, the distribution option you specify will be applied to all temp tables when issuing a CTAS statement.
"gateway".transient_table_distribution_style = hash_first_column
You can specify transient_table_distribution_style as a Hyper-Q hint at the request level.
-- @dtm_transient_table_distribution_style=hash_first_column
If you specify transient_table_distribution_style as a Hyper-Q hint at the request level, it overrides the setting in the dtm.ini file.
You can set and reset transient_table_distribution_style using the dtm set and dtm reset commands on a per session basis.
Using dtm set and dtm reset, you can write a script to create tables and specify the use of either hash_on_first column or round-robin as necessary. When specifying an option using dtm set, it overrides the setting in the dtm.ini file, and will do so until you reset the session using the dtm reset command.
dtm set transient_table_distribution_style hash_first_column;
To reset the session and revert back to the setting as specified in the dtm.ini file, use dtm reset.
dtm reset transient_table_distribution_style hash_first_column;