Optimizing queries for the particular table

Question

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)

Is this homework? The queries seem contrived to be unsargable. — Martin Smith, Mar 26 '15 at 07:46
Hey, this will never bring anything up! `SELECT * FROM CustomersSmallOrders WHERE Left(CustomerName, 4) = 'Levis'`, you're going to compare 5 chars to 4 :) — Evaldas Buinauskas, Mar 26 '15 at 07:47
Not its not homework. It's my manager's way of checking if we are up to date with what our colleagues do daily so if anyone is away, there is someone to replace them. I find it rather irritating way of verifying our skills. Last time the test was based on switches and controllers. — Will, Mar 26 '15 at 07:50
@EvaldasBuinauskas Yes I thought so as well, but I wanted to check with experts. — Will, Mar 26 '15 at 07:54
So to optimise the one pointed out by @EvaldasBuinauskas you could use `SELECT TOP 0 * FROM CustomersSmallOrders` and the plan won't access the table at all. — Martin Smith, Mar 26 '15 at 07:56
@MartinSmith Thank you. Do you happen to have any ideas about the other ones? — Will, Mar 26 '15 at 08:05

score 2 · Accepted Answer · edited Mar 26 '15 at 09:21

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)

I'll forgive you for misspelling my name if you will accept this answer :-) — Zohar Peled, Mar 26 '15 at 09:20
no problem, i would have forgiven you anyway... and thanks to @MartinSmith for improving my answer :-) — Zohar Peled, Mar 26 '15 at 09:26

Optimizing queries for the particular table

1 Answers1