sql-serverindexingquery-optimizationquery-performancequery-tuning

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)

Solution

  • 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)