SQL Server provides various hints that can be used to optimize query performance. One such hint is the FAST hint, which can significantly improve the execution time of a query.

In a recent puzzle challenge, we explored the usage of the FAST hint and its benefits. The challenge was to identify the condition in which the FAST hint would be useful. SQL Server expert Brad Schulz provided an excellent solution to this puzzle, which we will discuss in this article.

Let’s consider an example scenario where we have a table of sales orders and we want to retrieve all orders placed on a specific date. Here is the original query without the FAST hint:

USE AdventureWorks;

DECLARE @DesiredDateAtMidnight DATETIME = '2001-07-09';
DECLARE @NextDateAtMidnight DATETIME = DATEADD(DAY, 1, @DesiredDateAtMidnight);

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;

Now, let’s see how the query performs with the FAST hint:

USE AdventureWorks;

DECLARE @DesiredDateAtMidnight DATETIME = '2001-07-09';
DECLARE @NextDateAtMidnight DATETIME = DATEADD(DAY, 1, @DesiredDateAtMidnight);

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);

By adding the FAST hint with a value of 10, we can observe a visible difference in the execution plan. The query with the FAST hint returns results with a much lower cost, indicating improved performance.

It’s important to note that the FAST hint is not a guaranteed performance improvement in all scenarios. It is most effective when used with queries that involve large datasets or complex joins. It instructs the SQL Server query optimizer to prioritize speed over other factors, such as memory usage or parallelism.

However, it’s recommended to use the FAST hint judiciously and perform thorough testing to ensure it provides the desired performance improvement. In some cases, the query optimizer may already choose the most efficient execution plan without the need for the FAST hint.

In conclusion, the FAST hint in SQL Server can be a valuable tool for optimizing query performance. By understanding its usage and experimenting with different scenarios, database professionals can leverage this hint to improve the overall efficiency of their SQL Server queries.

Thank you to Brad Schulz for his insightful blog post on the FAST hint. I encourage you to read his original post for more in-depth information on this topic.