6

In our Datawarehouse context, we have to update some record in fact tables inside our ETL.

One thing we did is to create nonclustered indexes just before the heaviest queries, and drop them afterwards. This lead us to much less time spent scanning tables in queries, and the time spent in building indexes has very low impact (1-2 minutes).

Is this a bad practice?

Note: We cannot partition tables right now.

  • The SQLCAT whitepaper, link dead, on high performance offered this advice on indexes. Never drop the clustered key. If there is only one non-clustered index and you expect more than 100% growth, drop and recreate. If there is more than one non-clustered index and you expect ~10% growth, then dropping and recreating might be faster. As always, evaluate for your system and re-evaluate over time as thresholds may change. – billinkc Jan 21 '15 at 14:58
  • At my last client, as part of our ETL we had two separate stages where we rebuilt our indexes: first being after the staging area was populated and then again after the dimensions were populated. As there was a single snowflaked dimension, we had a specific indexing task in between the outermost table population and the inner node to make the process as efficient as possible. My guidance on DW tuning: Do I have an SLA? Does the data warehouse processing surpass my SLA? If processing goes belly up, and it will, when does the issue need to be resolved in order that my SLA is met? – billinkc Jan 21 '15 at 15:05
9

If it works for you then it's a good practice. There are basically no hard rules for databases.

7

Building temporary indexes for ETL jobs is not necessarily bad practice, as the index builds are fairly quick. Where it might not be so efficient is if you have relatively small incremental updates on very large tables, but it sounds like this is not the case here.

The only caveat is if you expect the tables to grow substantially with time. If they are just work tables for the ETL then it may well be oK. If the tables are fact tables that will accumulate large data volumes over the next 5 years then the index rebuilds may get slower with time.

For staging data, dropping indexes then loading will make bulk loads to staging much quicker, and you may well need to add indexes to the staging tables in order to support queries supplying the ETL process.

2

The only thing which I can think of which could be bad is that while building indexes new inserts/updates on the relevant tables could be problematic.

But if there is no use-case for changing the data while building indexes it is a fast and viable approach. You save time and space.

1

This is a perfectly valid practice, though it's more commonly used for inserts than updates. This is discussed elsewhere on Stack Exchange (e.g., https://softwareengineering.stackexchange.com/a/78598/25946). It's worth confirming, though, that the whole time required for "create index, update, drop index" really is less than making the update without indices.

You should also consider leaving the index in place; obviously this would be even faster than "create, update, drop". Keeping the index in place will slow inserts and deletes slightly, but may help with queries. You may also be able to modify the index you're currently using for ETL only to be useful for querying. If you can provide more detail about your table and queries, we may be able to offer more specific suggestions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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