Microsoft Exam 70-461 Topic 4 Question 6 Discussion

Actual exam question for Microsoft's 70-461 exam

Question #: 6
Topic #: 4

You generate a daily report according to the following query:

The Sales.ufn.GetLastOrderDate user-defined function (UDF) is defined as follows:

You need to improve the performance of the query.

What should you do?

ARewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE (SELECT OrderDate FROM Sales.ufnGetLastOrderDate(c.CustomerID)) <DATEADD(DAY, -90, GETDATE())
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetLastOrderDate(@CustomerID int)
RETURNS @OrderTable TABLE (OrderDate datetime)
AS
BEGIN
INSERT @OrderTable
SELECT MAX(s.OrderDate) As OrderDate
FROM Sales.SalesOrder s
WHERE s.CustomerID = @CustomerID
RETURN
END

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

CDrop 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 s
WHERE s.OrderDate > DATEADD(DAY, -90, GETDATE())
AND s.CustomerID = c.CustomerID)

DRewrite the report query as follows:
SELECT c.CustomerName
FROM Sales.Customer c
WHERE (SELECT OrderDate FROM Sales.ufnGetLastOrderDate(c.CustomerID)) < DATEADD(DAY, -90, GETDATE())
Rewrite the UDF as follows:
CREATE FUNCTION Sales.ufnGetLastOrderDate(@CustomerID int)
RETURNS @OrderTable TABLE (OrderDate datetime)
AS
BEGIN
INSERT @OrderTable
SELECT MAX(s.OrderDate) As OrderDate
FROM Sales.SalesOrder s
WHERE s.CustomerID = @CustomerID
RETURN
END

Show Suggested Answer

Suggested Answer: B

We use a Common Table Expression (TBE) to optimize the query with a predicate pushdown.

Predicate pushdown is an optimization that applies conditions (predicates) as early as possible --- ideally preventing any loading of unneeded rows.

References:

https://modern-sql.com/feature/with/performance

by Abel at May 08, 2022, 02:44 PM

Limited Time Offer

25%

Off

Get Premium 70-461 Questions as Interactive Web-Based Practice Test or PDF

Contribute your Thoughts:

Submit Cancel

Currently there are no comments in this discussion, be the first to comment!