SQL Server Performance Forum – Threads Archive

gains from storing values in variables?

I have a proc that uses dateAdd() and getDate() throughout. I thought that by storing the derived values in variables I could pick up some performance, but after timing it using statistics time and another timing method I’ve seen, it really didn’t pick up any pace. Rationale in me doing so was that I thought storing the value in a locar var would keep sql server from evaluating the getDate() and the dateAdd()functions multiple times (dateAdd() is getting the same parameters passed to it everytime it’s called in the proc) . This bit of code is used in 5 different places for 5 different tables. It’s not an exact snip, but it gets the point across. In cases like this, will sql server only eval the functions once and use it throughout the transaction? Thx. Btw, just found the board, I see some alot of nice discussions.
updatemyTable
setcol1 =
Case myDateField
When dateAdd(day, -1, getDate()) Then val1
When dateAdd(day, -2, getDate()) Then val2
When dateAdd(day, -3, getDate()) Then val3
When dateAdd(day, -4, getDate()) Then val4
When dateAdd(day, -5, getDate()) Then val5
When dateAdd(day, -6, getDate()) Then val6
When dateAdd(day, -7, getDate()) Then val7
End
where…
andmyOtherDateField between getDate() and dateAdd(day, -7, getDate())

Interesting question. [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] I think I have read once, that SQL Server will evaluate this only once. But, as usual in such cases, I can’t find the source for that statement right now. [<img src=’/community/emoticons/emotion-6.gif’ alt=’

‘ />] – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>)

>>I think I have read once, that SQL Server will evaluate this only once. But, as usual in such cases, I can’t find the source for that statement right now Same case to me also [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Madhivanan Failing to plan is Planning to fail

]]>