CODEWITHSUNDEEP
sql-serverindexingquery-optimizationquery-performancequery-tuning
Will
Will

Reputation: 59

Optimizing queries for the particular table

I have a table and I'm looking for a way to improve its performance. Below there is a list of queries to be executed.

dbo.CustomersSmallOrders
(
    ID INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    CustomerName VARCHAR(MAX) NOT NULL,
    OrderDate DATETIME NOT NULL,
    Quantity INT NOT NULL,
    Amount DECIMAL(15,2) NOT NULL,
    AvailableAmount DECIMAL(15,2) NOT NULL,
    Type TINYINT NOT NULL,
    Status TINYINT NOT NULL
)

Could you please help me rewrite below queries and add indexes to the table in order to improve its performance? 

SELECT *
FROM CustomersSmallOrders
WHERE Left(CustomerName, 4) = 'Levis'

SELECT *
FROM CustomersSmallOrders
WHERE DateDiff(month, OrderDate, GetDate()) >= 30

SELECT *
FROM CustomersSmallOrders
WHERE Quantity * 2 < 3400

SELECT 
    SUM(CASE 
            WHEN Type = 2 AND STATUS IN (0, 1, 2) THEN Amount 
            ELSE 0 
        END)
    ,SUM(CASE 
             WHEN Type = 1 AND STATUS IN (0, 1) THEN Amount 
             ELSE 0 
         END)
    ,SUM(CASE 
             WHEN Type = 2 AND STATUS IN (0, 1) THEN Amount - AvailableAmount 
             ELSE 0 
         END)
FROM CustomersSmallOrders
WHERE STATUS IN (0, 1, 2)

Upvotes: 2

Views: 43

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82524

Query Improvements:

As Martin Smith said, the 1st query can be converted to 

SELECT TOP 0 * FROM CustomersSmallOrders

Because of the contradiction.

Had it been

WHERE Left(CustomerName, 5) = 'Levis'

then changing the condition to

WHERE CustomerName LIKE 'Levis%'

would keep the query sargable and allow index use.

The 2nd query can be improved by changing the condition and adding an index on OrderDate:

SELECT *
FROM CustomersSmallOrders
WHERE OrderDate <= DATEADD(Mounth, -30, GetDate())

The 3rd one (adding an index on Quantity):

SELECT *
FROM CustomersSmallOrders
WHERE Quantity < 1700

the 4th - adding an index on STATUS that contains also Type, Amount, and AvailableAmount (although I don't understand why he wants the Amount twice. I think it's probably a mistake and he wants only 1 column output):

SELECT 
SUM(CASE 
        WHEN Type = 2 THEN Amount 
        ELSE 0 
    END)
,SUM(CASE 
         WHEN Type = 1 AND STATUS IN (0, 1) THEN Amount 
         ELSE 0 
     END)
,SUM(CASE 
         WHEN Type = 2 AND STATUS IN (0, 1) THEN Amount - AvailableAmount 
         ELSE 0 
     END)
FROM CustomersSmallOrders
WHERE STATUS IN (0, 1, 2)

Upvotes: 2

Related Questions