CODEWITHSUNDEEP
sql-serverquery-performance
sam
sam

Reputation: 407

How to improve SQL Server query performance

Table Size 32GB Row count 250M

Table DDL 

CREATE TABLE Orders
(
    ID [int] IDENTITY(1,1) NOT NULL,
    server [varchar](50) NULL,
    server_id [int] NOT NULL,
    merchant_id [int] NOT NULL,
    order_id [int] NOT NULL,
    customer_id [int] NOT NULL,
    customer_name [varchar](50) NULL,   
    [amount] [money] NULL,
    order_date [smalldatetime] NULL,
    ship_date [smalldatetime] NULL, 
    order_status [varchar](50) NULL,    
    custom_field_1 [varchar](50) NULL,
    custom_field_2 [varchar](50) NULL,
    custom_field_3 [varchar](50) NULL,
    custom_field_4 [varchar](50) NULL,
    created_at [datetime] NULL

    CONSTRAINT [PK_Orders] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                          IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                          ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

I have following non-clustered index

merchant_id, order_id
order_date

Logically, the order_id, merchant_id make a unique key.

Simple query like below takes almost 30 minutes. 

select 
    sum(amount)
from 
    Orders 
where 
    Order_Date >= getdate() - 7

I have few questions:

Upvotes: 0

Views: 94

Answers (3)

Nadeem
Nadeem

Reputation: 324

You just need to alter the index on Order_Date, make it covering index including Amount column. The same is suggested by @Lamark https://www.simple-talk.com/sql/learn-sql-server/using-covering-indexes-to-improve-query-performance/

Upvotes: 0

SqlZim
SqlZim

Reputation: 38023

Is the PK Right?

Probably. Using this surrogate id for the clustering key keeps the storage overhead lower for all indexes by using a thin 4 byte key instead of the composite 12 byte key of merchant_id, order_id, order_date or 8 byte key of merchant_id, order_id

The clustering key is how each index points back to the rest of the table.

Will making order_id and merchant_id as PK help in performance?

You would have to review the impact on all queries that run against the table to know if it would help or not.

I would focus on evaluating covering indexes for queries that you need to run faster, and if you find a trend where you need those two columns for most of your queries, then maybe.

What are the ideal indexes I should have on this table?

You need to review the queries, execution plans, and current index usage to be able to determine which indexes you need for that table.

Since your order_date is not the first column in your nonclustered index, the optimizer will most likely not use it for your example query.

Even if you have an index on order_date, it is going to have to go back to the table to get the amount. If you include amount in as an included column on the index, it will become a covering index for that query, with no need to go back to the table.

For that example query, you could use something like this to have an index-only query, instead of one with a table lookup: 

create nonclustered index ix_Orders (Order_Date) include (amount);

Upvotes: 1

Yashveer Singh
Yashveer Singh

Reputation: 1977

What you need is index on date . Create a non clustered index on date it will help in the performance . Indexing is very important in query performance . Jus tto start you should have index on those column which are heavily used in where clause in your case date field .

https://www.simple-talk.com/sql/learn-sql-server/sql-server-index-basics/

Upvotes: 0

Related Questions