14

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!

Improve this question
3
  • Some more information on the table structure, the indexes that are in existence today and the look of the data being inserted (is it in a certain order? does that align with the clustered index?) would help. Also I presume this table is unavailable until this process is done? That is good to know to have options for bulk importing.
    – Mike Walsh
    Dec 13, 2012 at 13:57
  • Maybe you should truncate the table insert into it and take a look at what your index fragmentation is to see whether or not you need to.
    – Zane
    Dec 13, 2012 at 14:03
  • v:2008 Standard. Source data is multiple staging tables, prior to this data loads from csv, excel, Oracle and other SQL db's. Table structures are all identical at this stage: 6 char ID, 3 char code, 10 cols of decimal(20,5). Primary key is ID+Code. Data being loaded through insert into and at the moment there is no order by clause, but I could add that if it would help? ID and Code are also indexed separately.
    – BlueChippy
    Dec 16, 2012 at 4:34

2 Answers 2

Reset to default
9

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. :)

Improve this answer
1

Plan Basic with all indexes enabled might be slow and might result in fragmentation.

ALTER INDEX REBUILD on an truncated and therefore empty table serves no purpose, so you need to amend your Plan A. It should be:

  • TRUNCATE
  • Insert
  • ALTER INDEX REBUILD

It might stil be slow, but at least you get sharp indexes.

Plan B is fine. Test all three and see which is fastest and which gives the least index fragmentation. Then decide if rebuilding is worth it.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.