locked
Question on Index creation RRS feed

  • Question

  • I have a table that I rebuild daily due to business processes. This table contains on average 50 million records. Via an ETL process, the table gets loaded and then indexes are created after the data loads. I've noticed that because of the size of the data, it takes a while for the index creation to complete. I'm wondering if there is any differences in creating the indexes before the the table loads or after the table loads. One benefit I've noticed is the speed of index creation when the table contains no data, but I don't know if it is necessary for the table to have data for the index build to be created. Any thoughts?

    Thursday, January 10, 2008 11:06 PM

Answers

  • Index creation will definately be faster with no data. However, it will slow the ETL down most likely and it may need to be rebuild afterwards anyway. You can test it the other way but you are probably doing it the right way.
    Thursday, January 10, 2008 11:55 PM
    Answerer
  • I agree with Jason. While it will certainly be faster to create an index on an empty table, the data load will take longer due to the database having to maintain the indexes. Also, the massive amount of data input will almost certainly result in index fragmentation and slow query performance so you may find you have to rebuild them anyway.


    There are options you could test, for example creating your indexes with a relevant fill factor that may reduce fragmentation of the dataload and reorganising your indexes after the dataload rather than rebuilding them. Check out the topics on FILLFACTOR and ALTER INDEX options in Books Online.

     

    HTH!


     

    Friday, January 11, 2008 7:42 AM

All replies

  • Index creation will definately be faster with no data. However, it will slow the ETL down most likely and it may need to be rebuild afterwards anyway. You can test it the other way but you are probably doing it the right way.
    Thursday, January 10, 2008 11:55 PM
    Answerer
  • I agree with Jason. While it will certainly be faster to create an index on an empty table, the data load will take longer due to the database having to maintain the indexes. Also, the massive amount of data input will almost certainly result in index fragmentation and slow query performance so you may find you have to rebuild them anyway.


    There are options you could test, for example creating your indexes with a relevant fill factor that may reduce fragmentation of the dataload and reorganising your indexes after the dataload rather than rebuilding them. Check out the topics on FILLFACTOR and ALTER INDEX options in Books Online.

     

    HTH!


     

    Friday, January 11, 2008 7:42 AM