lsgtechuser
Programmer
I have a Table that has over 6 million records. I need to append data from other tables to this table where they share the same zipcodes. All of the updates I am doing are based on many to one relationships and the update queries have taken over 3 hrs to run. I'm trying to find a way to speed this up and I'm sure i can by changing the indexes/keys/clustered indexes.
A sample layout of a table I am linking the big table to is
9digitZip (unique identifier and primary key)
Specialcode1 (these can be the same for different zips)
Specialcode2 (these can be the same for different zips)
update [6millionrectbl] set MYCODE = TABLE1.Specialcode1
from [6millionrectbl],table1
where [6millionrectbl].zip9= whsetbl.zip9
each record in the 6millrectbl has a unique identifier, but I have not made it the primary key b/c that's not what i am joining on to do these updates. should i make the zipcode on this file a clustered index? i will also be creating reports after this and joining the MYCODE column to another table that has that as the unique identifier. should I also index the MYCODE field on the large table? if so what kind is best to use? I know i haven't been very clear, any help would be greatly appreciated.
A sample layout of a table I am linking the big table to is
9digitZip (unique identifier and primary key)
Specialcode1 (these can be the same for different zips)
Specialcode2 (these can be the same for different zips)
update [6millionrectbl] set MYCODE = TABLE1.Specialcode1
from [6millionrectbl],table1
where [6millionrectbl].zip9= whsetbl.zip9
each record in the 6millrectbl has a unique identifier, but I have not made it the primary key b/c that's not what i am joining on to do these updates. should i make the zipcode on this file a clustered index? i will also be creating reports after this and joining the MYCODE column to another table that has that as the unique identifier. should I also index the MYCODE field on the large table? if so what kind is best to use? I know i haven't been very clear, any help would be greatly appreciated.