November 9, 2012 at 3:27 am
Hello All, Do you think there can be any impact in performance in the way these two commands are used ? Consider the following:
1. There is huge data in TableB (say 50000000)
2. Temp table #TableA contains around 50000 records
3. Clustered index on column ColP in both tables
4. Difference is only in the way alias is used in second command
update TableA
set Col1 = ColX
from #TableA A
inner join TableB B on A.ColP = B.ColP
update A
set Col1 = ColX
from #TableA A
inner join TableB B on A.ColP = B.ColP
Might be a silly question. 🙂
November 9, 2012 at 3:45 am
I dont think there is any difference in performance, but you could always check the execution plan to make sure!
/>L
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
November 9, 2012 at 6:25 am
I've seen issues with both performance, and with the expected results of the query, being worse in the first one. If you use Update From, make the target of the update the alias you use in the From.
I don't know that the issues I've seen are still true, but they were very true in SQL 2000.
If you're using SQL 2008 (as per the forum you posted in), I recommend switching away from Update From, and using Merge instead.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply