When running the test from Query Analyzer, I prepend the following lines: declare int select = 20 declare datetime Where feedid = and feedDate = by t1.acctNo Where feedid = and feedDate = union select acctNo,feedDate as date from datafeed_jnl Where feedid = and feedDate = union select acctNo,feedDate as date from datafeed_money
Select acctNo,tradeDate as date from datafeed_trans UPDATE: I've been asked to post the query. Sorry for the entry-level question but I can't really make heads or tails of this. Is it highly unusual to have a query that requires a recompilation hint on every single call? 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? It apparently creates a new lookup strategy for the query. My understanding from the posts is that OPTION (RECOMPILE) is an expensive operation. 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. The query is always called with the same parameters. 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. This is the case when the query is executed from Query Analyzer or from my C# program via SqlCommand.ExecuteReader().
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.
0 kommentar(er)
