CODEWITHSUNDEEP
azure-synapse
Slicc
Slicc

Reputation: 3435

Azure Datawarehouse performance issues

I have a very basic Azure SQL Warehouse setup for test purposes DWU100. It has one table in it with 60 million rows. I run a query of the form:

SELECT 
    SUM(TheValue), GroupId
FROM 
    [dbo].[Fact_TestTable]
GROUP BY
    GroupId

Running this query takes 5 seconds.

Running the same query on a DTU 250 SQL database (equivalent by price), I get an execution time of 1 second.

I'm assuming there must be things I can do to speed this up, can anyone suggest what I can do to improve this?

The group by GroupId above is just an example, I can't assume people will always group by any one particular column.

Upvotes: 0

Views: 138

Answers (1)

Igor
Igor

Reputation: 51

based on your question, it's not clear how is your table designed - are you using ROUND-ROBIN or HASH distributed table design? If you did not choose distribution type during table creation, default table design is round robin. Given your query, choosing HASH distributed table design would likely lead to improved query execution time as this query would converted to local-global aggregation type of query. It's hard to comment exactly what is happening given you did not share query plan. Below is a link to SQL DW documentation that talks about various table design options.

https://learn.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse?view=aps-pdw-2016-au7

hope this helps, Igor

Upvotes: 2

Related Questions