Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
kumarich1
Yak Posting Veteran
99 Posts |
 Posted - 2009-07-24 : 09:30:30
|
| I am updating a big table which has 90 million rows.I am doing the update in small chunks by using below script.There is no Index on any of these columns, I even tried adding index to address column , but I did not see any significant improvement.Please advise what else I can do to improve the speed of the update.Set rowcount 10000update dbo.Table set [Address] = StreetNumber + ' '+StreetDirectional + ' '+StreetName+ ' '+ UnitDesignator+ ' ' +UnitNumber from dbo.Table where address is null |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2009-07-24 : 10:14:31
|
| highlight the query in your query editor (SSMS) and hit <ctrl> Lis it doing a scan or a seek?try limiting the # of records affected maybe, but i just tested the same thing on a table with 143 million records and it's doing a seek. the key lookup is going to be the big cost probably. also depends on how many of those records are actually null.the index should help (unless a whole lot of those are null), but remember it needs to get updated too when you perform this operation |
 |
|
|
|
|
|
update very slow