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 |
|
Ghanta
Yak Posting Veteran
96 Posts |
 Posted - 2011-06-10 : 15:50:02
|
| I need to run an update on a huge table using data from another table... tables are joined using 4 columns on each. Should I create one Index using all those columns or is it efficient to create separate indexes ... one for each column?To give you the idea of number of records in those tables:Table to be updated has 506242508 recordsTable used to update has 5015556 recordsThanks in advance for help. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 15:57:35
|
One index of 4 columns, although I doubt it will help with the number of rows you're updating. Since you're doing an update it will have to scan the table anyway. |
 |
|
|
Ghanta
Yak Posting Veteran
96 Posts |
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-06-10 : 16:05:16
|
Rob I am thinking of doing it in batches of 5000 records? What do you think is a better solution? Thanks!quote:
Originally posted by robvolk
One index of 4 columns, although I doubt it will help with the number of rows you're updating. Since you're doing an update it will have to scan the table anyway.
|
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2011-06-10 : 16:08:03
|
| Hard to say, my suggestion is to try it and see what happens. |
|
|
|
Ghanta
Yak Posting Veteran
96 Posts |
Posted - 2011-06-10 : 16:11:00
|
Thank you.quote:
Originally posted by robvolk
Hard to say, my suggestion is to try it and see what happens.
|
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-06-10 : 16:41:30
|
| http://sqlinthewild.co.za/index.php/2010/09/14/one-wide-index-or-multiple-narrow-indexes/--Gail ShawSQL Server MVP |
|
|
|
|
|
|
Multiple Indexes or Index with Multiple Column