November 22, 2007 at 2:58 am
In our data warehouse (SS2K5) SSIS ETL procedure, most tables are truncated before data is reloaded (using a basic Data Flow OleDBSource -> OleDBDestination). Now, after data is reloaded, indexes are rebuilt using the Rebuild Index Task. This is a quite time consuming procedure for large fact tables with many indexes.
My question is: Is there any point in performing an index rebuild for these tables, where data is only inserted after truncation (no updates or deletes)?
The question is really a more general SqlServer question: Will an index that is rebuilt after all data has been inserted (no updates or deletes) be any better than that which was built during the inserts? Is the indexing procedure capable of optimizing an index based on the total set of data, i.e. by 'higher level' data analysis?
November 22, 2007 at 3:31 am
Please give me one more chance to understand how your SSIS package works.
Your package do the following tasks:
1. Truncate,
2. Transfer data
3. Reindex.
Is your package works this way?
November 22, 2007 at 3:37 am
Marek Powichrowski (11/22/2007)
Your package do the following tasks:1. Truncate,
2. Transfer data
3. Reindex.
Is your package works this way?
Yes, correct. And the question is if reindexing is just waste of time.
November 22, 2007 at 3:51 am
Second question: after truncation all indexes in destination tables are fully operational (during insertion) or are removed?
November 22, 2007 at 4:28 am
All indexes are fully operational after truncation and during inserts/data transfer, which is what makes me suspect the index rebuild is not needed.
For the record: I have not developed these ETL packages myself. The responsibility has recently been handed over to me, and I have just begun digging into them to see how they can be optimized.
November 22, 2007 at 4:41 am
vebjornwa (11/22/2007)
All indexes are fully operational after truncation and during inserts/data transfer, which is what makes me suspect the index rebuild is not needed.
Yes, I think the same. But reindexing may improve performance (but not huge amount) and it may be done form time to time (may be at midnight on saturday).
November 22, 2007 at 4:42 am
Try to do reindexing as separate task, not as ETL task.
November 22, 2007 at 4:52 am
Marek Powichrowski (11/22/2007)
vebjornwa (11/22/2007)
All indexes are fully operational after truncation and during inserts/data transfer, which is what makes me suspect the index rebuild is not needed.Yes, I think the same. But reindexing may improve performance (but not huge amount) and it may be done form time to time (may be at midnight on saturday).
The tables we are talking about are truncated and filled every night, so doing reindexing from time to time (once a week, as suggested) would be meaningless (?). What I suspect is that the index that results from an index rebuild will be identical to the index that exists after data insertion is complete. (Recall that after truncation, only inserts are performed, never any updates or deletes.)
November 22, 2007 at 5:36 am
Yes, indexes should be clear after truncation and never be rebuilded during all lifecycle of your DW (till next truncation). So there is no need to reindex indexes in your ETL process.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply