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