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&#39;m using SQL Server 2008.

I&#39;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&#39;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&#39;t really make heads or tails of this.

**UPDATE: I&#39;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=&#39;1/2/2009&#39;

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.




All Query Hints Solutions on Gang of Coders

Total of 1 Query Hints Solutions

OPTION (RECOMPILE) is Always Faster; Why?