January 28, 2015 at 9:32 am
Any idea why the first query (the one with the parameters) shows a Query cost (relative to the batch): 100%, and the second 0% ? The difference narrows slightly as the duration increases (at 200 years it is 79% / 21%). If anything, I would have guessed that the second query would have the greater query cost.
declare @d1 date = cast(dateadd(yy, -1, getdate()) as date), @d2 date = cast(getdate() as date)
select dd.date from dimDate dd
where dd.date between @d1 and @d2
select dd.date from dimDate dd
where dd.date between cast(dateadd(yy, -1, getdate()) as date) and cast(getdate() as date)
Both use a clustered index seek.
Don Simpson
January 28, 2015 at 9:44 am
My guess (someone else might confirm it) is that the first needs to create a safe plan while the other one can create an optimized plan. The use of variables mean that any value can be entered, while the functions have a defined range and any change to the code will generate a different plan.
January 28, 2015 at 12:41 pm
Could the 'safe' plan that incurs a higher cost be helped by updating statistics?
----------------------------------------------------
January 28, 2015 at 1:12 pm
MMartin1 (1/28/2015)
Could the 'safe' plan that incurs a higher cost be helped by updating statistics?
It could, but it won't be the same as the optimal plan.
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply