It is fun working with SQL/Application developer where you exchange a lot of technical thoughts to find the root of the issues. Recently, one of the developers optimized the OLTP system stored procedure by putting the query hint OPTION (FAST ‘N’). But, he was not sure enough about the drawback of the query hint. So, he requested to simulate the possible disadvantage of the query hint.

In today’s blog, I will be sharing the simulation result of the query hint OPTION (FAST ‘N’). You may find it useful when you are dealing with similar kind of scenario.

Problem Statement:

The developer used query hint OPTION (FAST ‘N’) to improve the performance of the SP, but he was not sure about the drawback of the query hint.

Issue Background:

The SP, which was optimized by the developer, was executing 1000+ times in a minute and was inserting filtered data (based on input parameter) into a temp table. The number of inserted rows might vary from 1 to 10,000 rows or maybe more.

Important Factors to Consider

The developer highlighted that it is an OLTP system. It means transactions should be completed in very short duration. It also means that execution plan should not experience stale statistics, low memory grant, hash or sort algorithms … many more problems.

Now, let’s understand the definition of the query hint.

What is OPTION (FAST N)

The OPTION (FAST ‘N’) means you are saying to SQL Server that you want to receive the first ‘N’ row as input as quickly as possible, where ‘N’ has to be a positive integer value. After the first ‘N’ rows are returned, the query continues execution and processes its full result set.

Note: This query hint biases plan choice to retrieve a certain number of rows quickly instead of the whole results.

Here is WITH and WITHOUT query hint performance chart;

It proves that when you are dealing with small number of row set, the query hint performs pretty fast. But, you are dealing with a huge number of row set, the query hint caused the query to perform poorly. Let’s go through step-by-step to understand the reason. To demonstrate it, I am using WideWorldImporters database;

Simulation WITHOUT the query hint OPTION (FAST ‘N’)

• Create a stored procedure “dbo.sel_salesVsInvoice_WITHOUT_FAST” inside the WideWorldImporters database and execute the SP 1000 times with input parameter values 100.

USE WideWorldImporters
GO
CREATE PROCEDURE dbo.sel_salesVsInvoice_WITHOUT_FAST @OrderID INT
AS
SELECT s.* 
INTO #t
FROM sales.Orders s
INNER JOIN sales.Invoices i ON s.OrderID = i.OrderID AND s.CustomerID = i.CustomerID
WHERE s.OrderID <=@OrderID
DROP TABLE #t
GO
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITHOUT_FAST 100
GO 1000
SET STATISTICS TIME OFF

• If we look at the execution plan, SQL Server decided to use a nested loops operator because the number of rows was being retrieved just 100 rows/execution. This is expected behavior because SQL Server uses a nested loop operator when it is dealing with a smaller amount of data.

• Now, we are going to process more data. So, let’s recompile the SP first before we execute the SP with different input parameters. The reason I am recompiling the SP because during the simulation, I found when the input parameter value is >184, SQL Server generates a new execution plan and uses a merge operator instead of the nested loop. Once you recompile the SP, let’s execute the batch one-by-one.

USE WideWorldImporters
GO
sp_recompile 'sel_salesVsInvoice_WITHOUT_FAST'
GO

SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITHOUT_FAST 300
GO 1000
SET STATISTICS TIME OFF

---2) Once you finished the previous batch, Execute the batch for input parameter value 3029

SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITHOUT_FAST 3029
GO 1000
SET STATISTICS TIME OFF

---3) Once you finished the previous batch, Execute the batch for input parameter value 6076
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITHOUT_FAST 6076
GO 1000
SET STATISTICS TIME OFF

---4) Once you finished the previous batch, Execute the batch for input parameter value 10158
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITHOUT_FAST 10158
GO 1000
SET STATISTICS TIME OFF

• Below is the execution plan which SQL Server had decided to use. So, it is using a merge operator instead of the nested loop. When we have merge or hash operator, SQL Server will end-up with memory grant problem. This is exactly what we see from the execution plan. There is a memory grant of 1.9MB. So, If you would expect this query to run 1000 times/minute and there is a memory grant that required for each execution. It is not a good thing because it will put a cap on overall throughput.

Simulation WITH the query hint OPTION (FAST ‘N’)

• Create another stored procedure “dbo.sel_salesVsInvoice_WITH_FAST” with the query hint inside the WideWorldImporters database and execute the SP 1000 times with input parameter values 100.

USE WideWorldImporters
GO
CREATE PROCEDURE dbo.sel_salesVsInvoice_WITH_FAST @OrderID INT
AS
SELECT s.* 
INTO #t
FROM sales.Orders s
INNER JOIN sales.Invoices i ON s.OrderID = i.OrderID AND s.CustomerID = i.CustomerID
WHERE s.OrderID <=@OrderID
OPTION (Fast 10)
DROP TABLE #t
GO
 

SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITH_FAST 100
GO 1000
SET STATISTICS TIME OFF

• Let’s check the execution plan now. So, SQL Server is using nested loops again because just 100 rows are being retrieved per execution. But, what is interesting in the execution plan – If you compare the below execution plan with previous (without query hint) execution plan, the estimated number of rows got changed from 47 to 10. It is because the query hint option (FAST 10) biased the execution plan.

• Let’s execute the SP with given parameter value one-by-one. This time we don’t need to recompile the SP because each time the plan is getting biased by the query hint and using the nested loops operator only.

USE WideWorldImporters
GO
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITH_FAST 300	
GO 1000
SET STATISTICS TIME OFF

---2) Once you finished the previous batch, Execute the batch for input parameter value 3029
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITH_FAST 3029
GO 1000
SET STATISTICS TIME OFF

---3) Once you finished the previous batch, Execute the batch for input parameter value 6076
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITH_FAST 6076
GO 1000
SET STATISTICS TIME OFF

---4) Once you finished the previous batch, Execute the batch for input parameter value 10158
SET STATISTICS TIME ON
EXEC dbo.sel_salesVsInvoice_WITH_FAST 10158
GO 1000
SET STATISTICS TIME OFF

Performance Summary WITH and WITHOUT the query hint

To drive the conclusion on the query hint performance, I ran both the SPs seven times. During each run, the SP was being executed 1000 times as one batch. If you follow the below table, it tells you each batch execution timing, passed input parameter value, and also one execution or one batch execution effect the number of rows. So, If we follow “Avg Batch Execution time (In Sec)”, the table shows that the hint is performing poorly when we are processing a huge number of rows.

Next Question – When can we use it?

When you are concerned about the performance of the application instead of concerning about the database performance, you can think of using it. This query hint will immediate return the data to the application even the query didn’t return the complete result set.

Summary

When you are dealing with OLTP system, I don’t think it is a recommended solution to use the query hint. You may say that application is processing a small number of the records but you cannot guarantee when the number of records will be increased from small to huge.

To address this kind of performance problem on the OLTP  system, I would prefer to leverage the index or optimize the code.

If you have faced a similar scenario in your environment, please do share your finding by commenting below. 

Thanks!