You need to improve the performance of the query

You generate a daily report according to the following query:

You need to improve the performance of the query.
What should you do?

You generate a daily report according to the following query:

You need to improve the performance of the query.
What should you do?

A.
Drop the UDF and rewrite the report query as follows:
WITH cte(CustomerID, LastOrderDate) AS (
SELECT CustomerID, MAX(OrderDate) AS [LastOrderDate]
FROM Sales.SalesOrder

GROUP BY CustomerID
)
SELECT c.CustomerName
FROM cte
INNER JOIN Sales.Customer c
ON cte.CustomerID = c.CustomerID
WHERE cte.LastOrderDate < DATEADD(DAY, -90, GETDATE())

B.
Drop the UDF and rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (
SELECT s.OrderDate
FROM Sales.SalesOrder
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)

C.
Drop the UDF and rewrite the report query as follows:
SELECT DISTINCT c.CustomerName
FROM Sales.Customer c
INNER JOIN Sales.SalesOrder s
ON c.CustomerID = s.CustomerID
WHERE s.OrderDate < DATEADD(DAY, -90, GETDATE())

D.
Rewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE NOT EXISTS (SELECT OrderDate FROM Sales.ufnGetRecentOrders(c.CustomerID,
90))
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetRecentOrders(@CustomerID int, @MaxAge datetime)
RETURNS TABLE AS RETURN (
SELECT OrderDate
FROM Sales.SalesOrder
WHERE s.CustomerID = @CustomerID
AND s.OrderDate > DATEADD(DAY, -@MaxAge, GETDATE())



Leave a Reply 7

Your email address will not be published. Required fields are marked *


Durga Prasad Palepu

Durga Prasad Palepu

?
My choice is A.

Chris

Chris

Hi guys,
I believe the answer is A and I’ve done some test which shows A could use a clustered index scan to share some loads and the others are using nonclustered index scan. not sure if this is why A is the best answer
but how could we know which column has an index in the table or is this something CTE could help improve in general?
Thanks!

Ray

Ray

The right answer is A

Lorri

Lorri

Why not C ?