Recommended best practice for indexes during loading of data

Question

Hello,

I have an SSIS package that reads incremental data from salesforce and loads into the database tables. No major transformation is being done. The incremental load has an average row count of 1k records. It is executed every hour. In the package, i have a component of dropping all the indexes prior to load and creating them at end of load. The entire graph takes 30 mins to run with almost 25 min just for indexes. Average table row count is around 60k rows. 

- Should i drop and create indexes once a day rather than every hourly load ?

- Should i not worry about messing with the indexes. 

Any recommendations on how to speed up the ETL ?

Answer 1

My suggestion is to experiment, preferably on a test copy of your database.  If you don't drop the indexes until the end of day, what happens?  Look for the obvious.  Does something else take longer?  Also look at the less obvious.  Do you see significant degradation of other queries because the indexes are not being rebuilt hourly.

---

Russel Loski, MCSE Data Platform/Business Intelligence Twitter: @sqlmovers; blog: www.sqlmovers.com

Answer 2

- Should i drop and create indexes once a day rather than every hourly load ?

You take the effort of dropping & rebuild an index for a load of very, very small amount of 1 K records? Don't make any sense.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 3

You have to test and decide which way. There is not a universal answer.

If you are replacing more than X% of a table, then dropping the index and recreating it might be a benefit.  

With only 60K rows, I would not bother.   When you are talking billions of rows, then consider it.

Answer 4

PS.  There is never a reason to drop indexes once a day and recreate them.

Answer 5

Hi doineedanid,

Could you please share the reason that you drop the indexes?

Please refer to CREATE INDEX (Transact-SQL) and DROP INDEX (Transact-SQL).

Best Regards,

Mona

---

""SQL Server related"" forum will be migrated to a new home on Microsoft Q&A SQL Server!
We invite you to post new questions in the "SQL Server related" forum’s new home on Microsoft Q&A SQL Server !
For more information, please refer to the sticky post.