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())
Can someone please explain how is A faster?
0
0
same question !
0
0
I think maybe B or C are better I would choose C though
0
0
Just beneath, are many completely not associated web-sites to ours, on the other hand, they’re certainly really worth going over.
0
0
One of our guests not too long ago recommended the following website.
0
0
Wonderful story, reckoned we could combine a number of unrelated data, nonetheless genuinely worth taking a look, whoa did one learn about Mid East has got much more problerms at the same time
0
0
Here are some of the internet sites we advocate for our visitors.
0
0
Here are several of the web sites we advocate for our visitors
0
0
usually posts some really intriguing stuff like this. If youre new to this site
0
0
Sites of interest we’ve a link to
0
0
although web-sites we backlink to beneath are considerably not connected to ours, we feel they may be basically worth a go by means of, so have a look
0
0
here are some links to internet sites that we link to since we believe they’re really worth visiting
0
0
Every as soon as inside a even though we pick out blogs that we read. Listed below are the latest web pages that we choose
0
0
here are some hyperlinks to sites that we link to because we consider they’re worth visiting
0
0
the time to study or check out the subject material or web pages we’ve linked to beneath the
0
0
The facts talked about in the post are a few of the most effective readily available
0
0
very few internet websites that take place to be in depth beneath, from our point of view are undoubtedly well worth checking out
0
0
we came across a cool web site that you may possibly love. Take a look for those who want
0
0
please take a look at the web pages we adhere to, including this one particular, as it represents our picks from the web
0
0
just beneath, are various completely not related web-sites to ours, however, they are surely worth going over
0
0
here are some links to internet sites that we link to since we consider they are really worth visiting
0
0
The data talked about in the post are a number of the ideal accessible
0
0
below youll locate the link to some websites that we believe you should visit
0
0