June 16, 2004 at 8:59 am
I have a DataMart data repository that is cleared out and populated with the latest data every night. I am wondering the best way to handle the table's indexes with the TRUNCATE TABLE statement. For example, one strategy would be:
1. Drop all indexes
2. Truncate table
3. Populate table
4. Recreate indexes
Another strategy would be to:
1. Truncate table
2. Populate table
3. Rebuild the indexes using the DROP_EXISTING clause of CREATE INDEX.
Other strategies would involve updating statistics or running DBCC DBREINDEX.
Which strategy is the best?
Thank you,
Mike Chabot
June 16, 2004 at 9:05 am
First off the truncate table statement will invalidate the pointer in sysindexes so the data is dropped instantly even from indexes without any rebuilding. I would suggest
Truncate Table
Drop Indexes (Even Clustered if one exists but do after non-clustered are dropped)
Populate Table
Add clustered first (Rebuild of non-clustered indexes automatically occurrs when clustered index is built, altered (design not data) or dropped)
Add non-clustered indexes
This should give you the best result to getting data populated speed wise.
June 16, 2004 at 2:23 pm
Thank you. I guess what the question comes down to is whether it is better to have indexes in place before a massive insert, or whether it is better to build the indexes after the insert is complete. My table has one clustered index and two non-clustered indexes.
I did some brief testing, and my results are below. The fastest solution was the following:
1. Drop non-clustered indexes
2. Truncate table
3. Insert data
4. Create non-clustered indexes
The clustered index is left alone.
According to DBCC SHOW_STATISTICS, the statistics on the clustered index seem to be updated after the above procedure is run. So is there any problem with not touching the clustered index after a TRUNCATE?
The ordering of dropping indexes and truncating the table did not seem to matter much.
The DROP_EXISTING clause in the CREATE INDEX command was the slowest scenario, and it throws an error if the index is not already there.
Scenarios:
Truncate, Insert, with no table indexes
2.1 seconds (quickest possible time)
Truncate,Insert, with the three table indexes untouched
5.5 seconds
Truncate,Insert,Create clustered index with DROP_EXISTING clause
8.4 seconds
Truncate,Drop non-clustered, Drop-clustered, insert, create clustered, create non-clustered
3.8 seconds
Drop non-clustered, drop-clustered, truncate, insert, create clustered, create non-clustered
3.8 seconds
Truncate, Drop non-clustered, (leave clustered), insert, create non-clustered
3.2 seconds
Drop non-clustered, (leave clustered), truncate, insert, create non-clustered
3.2 seconds
June 17, 2004 at 3:57 am
Try this -
http://www.sqldev.net/events.htm theres a power point show called "70 - Best Practices for High Performance Data Loading" "70GertDrapers_slides.zip"
It has some rather handy info and advice.
June 17, 2004 at 7:51 am
Thanks for the link. The presentation provides a lot of information to absorb, and I have to make assumptions to make up for some of the terse wording. It indicates to me that if the data is presorted on the CI, then have the CI in place before the bulk insert, otherwise create the CI (and NCIs) after the insert. Another slide indicates that you should drop and recreate all the indexes if the amount of data added exceeds the amount of existing data by 25%, although this slide may not be relevant if you are truncating the table first.
It gives me a couple more scenarios to try.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply