I have a stored procedure that truncates some tables with around 1.75M rows in each, before inserting new data (based on data in other tables, calculations etc.)
Basic outline is very simple:
- Truncate Tables
- Insert 1.75M rows in 'batches' of around 75,000 per time.
I am wondering if I should explicitly re-build the indexes at any time in this process? e.g.
- Truncate Tables
ALTER INDEX ALL ON xxx REBUILD WITH (FILLFACTOR=90)[or something similar]- Insert 1.75M rows
or perhaps
ALTER INDEX ALL ON xxx DISABLE- Truncate Tables
- Insert 1.75M rows
ALTER INDEX ALL ON xxx REBUILD WITH (FILLFACTOR=90)[or something similar]
Any assistance appreciated…not a DBA – a Dev who knows DB's pretty well is more accurate!
Best Answer
As with most questions of this type, it depends. It's unlikely that you're going to be inserting the data in the "correct" order for all of the indexes involved, which means that all of those indexes are likely to encounter a lot of page splitting during the insert process. So let's assume that you're inserting in clustered index order. You could disable all non-clustered indexes, truncate, do your insert, and then rebuild all of your non-clustered indexes. Of course, trying both approaches will tell you the truth of which is faster regardless of the theory behind it. :)