OPTION (RECOMPILE) is Always Faster; Why?

user_1040766 0
Viewed 276615

I encountered an odd situation where appending OPTION (RECOMPILE) to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes.

This is the case when the query is executed from Query Analyzer or from my C# program via SqlCommand.ExecuteReader(). Calling (or not calling) DBCC FREEPROCCACHE or DBCC dropcleanbuffers makes no difference; Query results are always returned instantaneously with OPTION (RECOMPILE) and greater than five minutes without it. The query is always called with the same parameters [for the sake of this test].

I'm using SQL Server 2008.

I'm fairly comfortable with writing SQL but have never used an OPTION command in a query before and was unfamiliar with the whole concept of plan caches until scanning the posts on this forum. My understanding from the posts is that OPTION (RECOMPILE) is an expensive operation. It apparently creates a new lookup strategy for the query. So why is it then, that subsequent queries that omit the OPTION (RECOMPILE) are so slow? Shouldn't the subsequent queries be making use of the lookup strategy that was computed on the previous call which included the recompilation hint?

Is it highly unusual to have a query that requires a recompilation hint on every single call?

Sorry for the entry-level question but I can't really make heads or tails of this.

UPDATE: I've been asked to post the query...

select acctNo,min(date) earliestDate 
from( 
    select acctNo,tradeDate as date 
    from datafeed_trans 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_money 
    where feedid=@feedID and feedDate=@feedDate 

    union 

    select acctNo,feedDate as date 
    from datafeed_jnl 
    where feedid=@feedID and feedDate=@feedDate 
)t1 
group by t1.acctNo
OPTION(RECOMPILE)

When running the test from Query Analyzer, I prepend the following lines:

declare @feedID int
select @feedID=20

declare @feedDate datetime
select @feedDate='1/2/2009'

When calling it from my C# program, the parameters are passed in via the SqlCommand.Parameters property.

For the purposes of this discussion, you can assume that the parameters never change so we can rule out sub-optimal parameter smelling as the cause.

query-hints compilation sql-server-2008 sql-server sql
6 Answers

Necroing this question but there's an explanation that no-one seems to have considered.

STATISTICS - Statistics are not available or misleading

If all of the following are true:

  1. The columns feedid and feedDate are likely to be highly correlated (e.g. a feed id is more specific than a feed date and the date parameter is redundant information).
  2. There is no index with both columns as sequential columns.
  3. There are no manually created statistics covering both these columns.

Then sql server may be incorrectly assuming that the columns are uncorrelated, leading to lower than expected cardinality estimates for applying both restrictions and a poor execution plan being selected. The fix in this case would be to create a statistics object linking the two columns, which is not an expensive operation.

user_2331520

OPTION (RECOMPILE) is used in real word generation scenarios. I have used it to eliminate the smell of parameters and optimize big queries. This may be the answer to your problem, but the signs are that optimizing for the unknown (like local variables) may also solve the problem.

I certainly do not avoid the option just because there was a time bug that was fixed several years ago. The main risk of OPTION (RECOMPILE) is when it is used improperly, such as high-frequency requests.

user_167186670
Related