Difference betw Primary Keys and Clustered Indexes and regular Indexes 1

[lsgtechuser]

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.

 

[fhlee]

Hi,
My 2 cents. Index the field(s) that you're doing the joins on both tables. ie; index 6millionrectbl.zip9 and whsetbl.zip9. This should make your queriy run much faster...

As for weather to do [Clustered Index] for 6millionrectbl, it depends on weather the records in 6millionrectbl changes often. If it does, don't, coz a Clustered Index will have a heavy blow on updates (coz it needs to rearrange the records everytime it changes). [Clustered Index] is great for records that don't change and you need to grab a large chunck of it each time. Ie; a table containing country, state, area, city & population of all the places on earth (and it is updated only once a year); and you have a query that goes: give me a list of population of all cities in USA. This case, it make sense to have a Clustered Index on Country, State, Area & City.

Hope this helps. good luck.

~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-~-

 

[thendrickson]

Just a thought. My best guess is fhlee is correct.
However, I would also suggest reading up in BOL on the performance tuning tools available in SQL Server.

Once you have some idea on using those tools, you will be in a much better position to determine what is happening in your stored procedures and to determine the best way to correct performance issues.

 

[Crowley16]

in your post, you say you're using an update statement...

an update statement doesn't actually append any records, just changes existing ones, so if this is the case then a cluster index on the PKey wouldn't hurt.

Also, by your sql statement, you are doing a test on the zip code, hence if you Index this, it will speed things up. Plus, the zip code is a char type right? if you have a unique number index, then that is faster than a char type index, so if possible you might want to introduce a identity field...

--------------------
Procrastinate Now!