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

Reference: Pinal Dave (http://blog.sqlauthority.com)

About these ads

2 thoughts on “SQL SERVER – Solution – Challenge – Puzzle – Usage of FAST Hint

  1. Pingback: SQL SERVER – Weekly Series – Memory Lane – #006 « SQL Server Journey with SQL Authority

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s