Azure Synapse Performance optimization

Question

I have a couple of tables with clustered column store index in synapse with hash partition and a view was written on those tables with multiple cte. Since we dont have indexes like clustered index and non clustered index as we used to have in sql which will operate on row level , how can we improve the performance of this view.

Count of this view is 7 million after handling all the filters perfectly and is taking longer time to display the query result.

Any Suggestions on performance optimization techniques in synapse where we dont have pk and fk constraints and row level indexes to work ?

Answer 1

Since you are using Synapse with clustered column store index, try:

• Data Compression -- reduce the size of the data on disk
• Columnstore Index Organized Tables -- can be used to physically cluster data on disk
• Partitioning -- you are already using hash partitioning, try to optimize the partitioning scheme to align with the query patterns of your view
• Materialized Views -- precompute the results of your view and store them in a table
• Query Tuning -- analyze the query execution plan and identify areas for improvement(indexing, query rewriting, using hints to influence the optimizer)

ps. you may need to try multiple techniques to find the optimal solution for your specific use case