Fast optimizer hint clause

Look at this

SELECT  [OrderId], [CustomerId], [OrderDate]
FROM [Orders]
ORDER BY [OrderDate]

-- Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT  [OrderId], [CustomerId], [OrderDate]
FROM [Orders]
ORDER BY [OrderDate]
OPTION (FAST 1)

--Table 'Orders'. Scan count 1, logical reads 1724, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

__________________________________________________

The fast option says to the optimizer how many records the query should return. So, in this case, the plan is less efficient because the query returns all the rows on the table, and the plan is instructed to 'think' that it will return only one row. the fast hint, is interesting when we want to predict to the optimizer how many rows the query might return on its execution.

 

Another hint may be used to instruct the optimizer about the value of a parameter. Is the Optimized FOR hint. Look at the example:

 

DECLARE @ShipCode nvarchar(20)
SET @ShipCode = N'05022'
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode

-- Table 'Orders'. Scan count 1, logical reads 22, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

DECLARE @ShipCode nvarchar(20)
SET @ShipCode = N'05022'
SELECT [OrderId], [OrderDate]
FROM [Orders]
WHERE [ShipPostalCode] = @ShipCode
OPTION (OPTIMIZE FOR (@ShipCode = N'05022'))

--Table 'Orders'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 

The optimize for option means that the execution plan should be optimized fro the @shipcode variable having the value '05022'. And results in a more efficient execution plan.