title: OPTION (RECOMPILE) is Always Faster; Why?
body: <p>I encountered an odd situation where appending <code>OPTION (RECOMPILE)</code> to my query causes it to run in half a second, while omitting it causes the query to take well over five minutes.</p>
  
  <p>This is the case when the query is executed from Query Analyzer or from my C# program via <code>SqlCommand.ExecuteReader()</code>. Calling (or not calling) <code>DBCC FREEPROCCACHE</code> or <code>DBCC dropcleanbuffers</code> makes no difference; Query results are always returned instantaneously with <code>OPTION (RECOMPILE)</code> and greater than five minutes without it. The query is always called with the same parameters [for the sake of this test].</p>
  
  <p>I'm using SQL Server 2008.</p>
  
  <p>I'm fairly comfortable with writing SQL but have never used an <code>OPTION</code> 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 <code>OPTION (RECOMPILE)</code> 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 <code>OPTION (RECOMPILE)</code> 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?</p>
  
  <p>Is it highly unusual to have a query that requires a recompilation hint on every single call?</p>
  
  <p>Sorry for the entry-level question but I can't really make heads or tails of this.</p>
  
  <p><strong>UPDATE: I've been asked to post the query...</strong></p>
  
  <pre><code>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)
  </code></pre>
  
  <p>When running the test from Query Analyzer, I prepend the following lines:</p>
  
  <pre><code>declare @feedID int
  select @feedID=20
  
  declare @feedDate datetime
  select @feedDate='1/2/2009'
  </code></pre>
  
  <p>When calling it from my C# program, the parameters are passed in via the <code>SqlCommand.Parameters</code> property.</p>
  
  <p>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.</p>