October 25, 2006 at 11:57 am
Why does:
declare @datevar as datetime
set @datevar = '10/25/06'
select * from history where calldatetime > @datevar
==run at over a minute where
select * from history where calldatetime > '10/25/06'
==run in 4 seconds?
Thanks..
October 25, 2006 at 12:28 pm
Because without knowing exactly which values are going to be passed to avariable the execution plan may be suboptimal.
you can encapsulate the logic on a sp and use the new OPTIMIZE FOR clause or if this is dynamically created then try sp_executesql so that at recompilation time you get the optimal.
Of course all this is assuming that you tested freeing the cache before every run, right?
* Noel
October 25, 2006 at 12:33 pm
I have no idea what you mean by freeing the cache..
October 25, 2006 at 12:37 pm
Don't use on production server :
DBCC FREEPROCCACHE
DBCC DROPCLEANBUFFERS
October 25, 2006 at 12:45 pm
Ah now I see the light, I looked at the estimated execution plan and the CPU IO cost was a lot higher for the statement with the variable.
October 25, 2006 at 11:29 pm
What data type is calldatetime?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 26, 2006 at 8:07 am
The Column is of type DateTime
I looked at the execution plan and when I was using the variable it was using the clustered indexes but when I was using a defined value in the statement it was using a single index that we have on that column, I did a WITH (INDEX(hist_calldatetime_index))
and it executes at the same speed.
October 26, 2006 at 8:12 am
May I ask why the date is not the clustered index on the table?
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply