Slow running query in Azure SQL Data Warehouse

Question

I have a very simple query which I am running in Azure SQL Data Warehouse but it is taking around 40sec to execute.

Table definition:

CREATE TABLE dbo.orders 
  ( 
     location_code     VARCHAR(8) NOT NULL, 
     order_date        DATETIME NOT NULL, 
     order_status_code INT NOT NULL, 
     order_type_code   VARCHAR(1) NULL, 
     coupon_code       VARCHAR(8) NULL, 
     coupon_amount     MONEY NOT NULL, 
     subtotal          MONEY NOT NULL, 
     total_amount      MONEY NULL, 
     order_number      INT NOT NULL, 
     customer_code     INT NOT NULL 
  )
 WITH
 (
    DISTRIBUTION = ROUND_ROBIN,
    CLUSTERED COLUMNSTORE INDEX
)

the query is :

SELECT location_code, 
       order_date, 
       order_status_code, 
       order_type_code, 
       coupon_code, 
       coupon_amount, 
       subtotal, 
       total_amount, 
       order_number, 
       customer_code 
FROM   orders WITH (nolock) 
WHERE  order_date >= '2016-04-01' 
       AND order_date <= '2016-04-30' 
       AND order_status_code < 99

There are 13,083,667 records in the table. Could anybody help me in optimizing this. I have provided 100 DWU for this.

thanks in advance.

in ssms, right click the table and 'script it as create to new query window'. Post that script also please, so we can see the table def and indexes — Caius Jard, Aug 16 '17 at 07:41
hello caius,please find below the structure of table: create table Orders( [Location_Code] [varchar](8) NOT NULL,[Order_Date] [datetime] NOT NULL,[Order_Status_Code] [int] NOT NULL,[Order_Type_Code] [varchar](1) NULL,[Coupon_Code] [varchar](8) NULL, [Coupon_Amount] [money] NOT NULL,[SubTotal] [money] NOT NULL,[Total_Amount] [money] NULL,[Order_Number] [int] NOT NULL,[Customer_Code] [int] NOT NULL) WITH(DISTRIBUTION = ROUND_ROBIN,CLUSTERED COLUMNSTORE INDEX) — Deepanshu, Aug 16 '17 at 09:53
@Deepanshu:please take a look at this link on how to improve question:https://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/ — TheGameiswar, Aug 16 '17 at 12:11

wBob · Answer 1 · 2017-08-16T23:11:04.740

As you are using Azure SQL Data Warehouse (ADW), there are certain things you have to do to get good performance from this immensely powerful MPP product:

1. Create statistics

Statistics are not created automatically in ADW so you have to create them. At the very least, create them on all columns used in joins, WHERE clauses, GROUP BY and HAVING. Example stats for your query:

CREATE STATISTICS stat_dbo__orders__order_date ON dbo.orders ( order_date );
CREATE STATISTICS stat_dbo__orders__order_status_code ON dbo.orders ( order_status_code );

Rease this informative article on the topic:

Managing statistics on tables in SQL Data Warehouse

https://learn.microsoft.com/en-us/azure/sql-data-warehouse/sql-data-warehouse-tables-statistics

2. DWU

DWU 100 is the lowest setting on ADW and definitley not suitable for performance testing. Try raising the DWU to eg 400, or 1000. Any more is probably overkill for this simply query with a low amount of rows for an MPP system. Bear in mind the higher DWUs are expensive.

NB NOLOCK is not required as READ UNCOMMITTED is the default isolation level in ADW.

i had created statistics earlier but the performance didn't increase. i have also raised the DMU from 100 to 400 and 1000 but in no vain,performance is stiil the same. — Deepanshu, Aug 17 '17 at 12:14
Can you provide some sample data and idea of how it is distributed? eg do you have 1,2 5 years data etc? I have created a [simple repro script](https://gist.github.com/anonymous/63d29ef2decb8576bd08776c190b19e1) and cannot reproduce this. Could something else be going on? For example, are there any other users logged in running workloads? ADW does have very low concurrency, particularly at lower DWUs, eg max concurrent queries at DWU 400 is only 16. Put the word `EXPLAIN` in front of your query and post the resulting XML. That might shed some light on it. — wBob, Aug 17 '17 at 12:50

Slow running query in Azure SQL Data Warehouse

1 Answers1

1. Create statistics

2. DWU