SQL SERVER – Solution – Challenge – Puzzle – Usage of FAST Hint

Earlier I had posted quick puzzle and I had received wonderful response to the same from Brad Schulz. Today we will go over the solution. The puzzle was about the usage of FAST hint, and Brad found a great example.

The puzzle was posted here: SQL SERVER – Challenge – Puzzle – Usage of FAST Hint

The question was in what condition the hint FAST will be useful.

In the response to this puzzle blog post here is what SQL Server Expert Brad Schulz has pointed me to his blog post where he explain how FAST hint can be useful. I strongly recommend to read his blog post over here.

With the permission of the Brad, I am reproducing following queries here. He has come up with example where FAST hint improves the performance.

USE AdventureWorks
GO
DECLARE @DesiredDateAtMidnight DATETIME = '20010709'
DECLARE @NextDateAtMidnight DATETIME = DATEADD(DAY,1,@DesiredDateAtMidnight)
-- Query without FAST
SELECT OrderID=h.SalesOrderID
,h.OrderDate
,h.TerritoryID
,TerritoryName=t.Name
,c.CardType
,c.CardNumber
,CardExpire=RIGHT(STR(100+ExpMonth),2)+'/'+STR(ExpYear,4)
,h.TotalDue
FROM Sales.SalesOrderHeader h
LEFT JOIN Sales.SalesTerritory t ON h.TerritoryID=t.TerritoryID
LEFT JOIN Sales.CreditCard c ON h.CreditCardID=c.CreditCardID
WHERE OrderDate>=@DesiredDateAtMidnight
AND OrderDate<@NextDateAtMidnight
ORDER BY h.SalesOrderID;
-- Query with FAST(10)
SELECT OrderID=h.SalesOrderID
,h.OrderDate
,h.TerritoryID
,TerritoryName=t.Name
,c.CardType
,c.CardNumber
,CardExpire=RIGHT(STR(100+ExpMonth),2)+'/'+STR(ExpYear,4)
,h.TotalDue
FROM Sales.SalesOrderHeader h
LEFT JOIN Sales.SalesTerritory t ON h.TerritoryID=t.TerritoryID
LEFT JOIN Sales.CreditCard c ON h.CreditCardID=c.CreditCardID
WHERE OrderDate>=@DesiredDateAtMidnight
AND OrderDate<@NextDateAtMidnight
ORDER BY h.SalesOrderID
OPTION(FAST 10)

Now when you check the execution plan for the same, you will find following visible difference. You will find query with FAST returns results with much lower cost.

Thank you Brad for excellent post and teaching us something. I request all of you to read original blog post written by Brad for much more information.

Want to try this yourself? Here is how to install AdventureWorks.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.