November 21, 2002 at 8:07 am
I want to update a column in a table (below). There is no index on the column but there are 10 indexes on the table including a clustered index. (If we pretend it takes no time to drop indexes) I think it is advantageous to drop the clustered index and then run the update and then rebuild it, right? The normal indexes do not seem to degrade perf. on an update much. Am I correct?
update dbo.PHARMACY
set service_rollup= NULL
where service_rollup = 'Drug_Thera_classZ-Antihistamines'
November 21, 2002 at 8:18 am
If the column isn't indexed, there should be no degradation in performance as the index won't need updating.
By the way, each non-clustered index is a pointer to the clustered index, so if you drop the clustered index, each non-clustered index has to be rebuilt. The same is also true when you add a clustered index.
That will be a big performance hit!
November 22, 2002 at 5:59 am
So, inserting a lot of data into a table with a clustered index is so slow because inserting new rows hits every column, INCLUDING THE COLUMN WITH THE CLUSTERED INDEX.
November 22, 2002 at 6:16 am
Well if you update the clustered index it will be slow but also a bad design !
A clustered index shouldn't change like that !
November 22, 2002 at 6:34 am
Other indexes besides Clustered tend to have a high impact on the speed, but this is not always the case. If the data going int will only add data to the end of the table then dropping the Cluster index may make a minimal difference. If however you will be loading data into early parts of the database then dropping the Clustered index and readding afterwards will make a considerable difference, the reason is when a row inserts when the page is full it will split and data moved around thus slowing you down, this occurrs for each individual inserted row. Dropping in means the data goes in as a heap and has not effect on early positions so no page splitting and data reorginzation. However, if you have a lot of data you may also want to set you DB growth to a fixed larger amount to keep file growth to a minimum, and try to pregrow before you start, this will also help you a bit.
Another thing to also keep in mind, when you drop or create the Clustered index it will cause the other to refresh completely so you may want to drop all anyway and add the clustered first then the others when done.
Edited by - antares686 on 11/22/2002 06:36:28 AM
November 22, 2002 at 6:57 am
Some comments on Clustered indexes :
When selecting a column to base your clustered index on, try to avoid columns that are frequently updated. Every time that a column used for a clustered index is modified, all of the non-clustered indexes must also be updated, creating additional overhead
November 22, 2002 at 8:55 am
If the column is not part of an index then the indexes may still need updating if the data you are updating with causes rows to be moved off pages, this causes a page split and thus the index needs updating. In your case setting a column to NULL should not impact indexes (in theory)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply