Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question

migrated from stackoverflow.com Dec 13 '12 at 13:37

This question came from our site for professional and enthusiast programmers.

    
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 '12 at 13:57
    
Three questions: (1) Which version of SQL Server? (2005/2008...); (2) Which edition? (Standard/Enterprise...); (3) What is the source of the inserted data (query/staging table/text file...) –  Paul White Dec 13 '12 at 14:03
    
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 '12 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 '12 at 4:34
add comment

1 Answer 1

up vote 1 down vote accepted

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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