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
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.