ETL Load - Dropping Indexes and Constraints
+2
BrianJarrett
AzeemFarooqui
6 posters
Page 1 of 1
ETL Load - Dropping Indexes and Constraints
Hi,
I am currently working on an ETL solution using BODI and SQL Server 2005. Our data warehouse is very small (no more than 5mb) currently and expected growth over the next year is not going to exceed 15mb.
Based on the above volume estimates does it make sense to drop existing indexes/constraints when performing the ETL load into the fact table?
I'd appreciate other peoples comments and views on this.
Regards
Azeem
I am currently working on an ETL solution using BODI and SQL Server 2005. Our data warehouse is very small (no more than 5mb) currently and expected growth over the next year is not going to exceed 15mb.
Based on the above volume estimates does it make sense to drop existing indexes/constraints when performing the ETL load into the fact table?
I'd appreciate other peoples comments and views on this.
Regards
Azeem
AzeemFarooqui- Posts : 6
Join date : 2009-02-23
Re: ETL Load - Dropping Indexes and Constraints
I would imagine that you could get by without dropping indexes and constraints pretty easily right now, given the small size of your warehouse. Inevitably it'll grow though, and leaving these in place during the load could cause a slowdown later on. It might be worth designing for it now to save yourself the possible work down the road. It's a judgment call really, so mine is just an opinion.
BrianJarrett- Posts : 61
Join date : 2009-02-03
Age : 50
Location : St. Louis, MO
Delete only index
You should not delete constraints for integrity check, delete only indexes.
krishna- Posts : 1
Join date : 2009-04-14
Enforce data integrity in the ETL, not using database RI constraints...
krishna wrote:You should not delete constraints for integrity check, delete only indexes.
Working in DEV is harder with constraints on...
steve_waters- Posts : 4
Join date : 2009-04-23
Re: ETL Load - Dropping Indexes and Constraints
According to "The Data Warehouse ETL Toolkit (Kimball and Caserta)", referential integrity should be enforced by the ETL process, not by constraints.krishna wrote:You should not delete constraints for integrity check, delete only indexes.
Rik Declercq- Posts : 10
Join date : 2009-02-03
ETL Load - Dropping Indexes and Constraints
For the amount of data you have, 15mb, I don't see a benefit of dropping the indexes or the constraints . Worst come to worst I'd drop and recreate the indexes. That is not a lot of data we're talking about.
You may ask,whether you should code so that even if the data is TB it will work. Yes true, but by then I'm sure your requirements will change.
When you say its an issue in dev,. what is the issue you're facing?
IF you try to enforce RI through ETL, and your code doesn't trap a scenario, you then have bad data. Yes you can test, have QA done, etc.. but underlying fact is that if something changes in your data and your code does not handle the RI ,you're in problems.
Has anyone bench marked the difference in timing by processing RI in ETL to having RI in tables. If so please could you share some numbers.It would be interesting to see the gains.
You may ask,whether you should code so that even if the data is TB it will work. Yes true, but by then I'm sure your requirements will change.
When you say its an issue in dev,. what is the issue you're facing?
IF you try to enforce RI through ETL, and your code doesn't trap a scenario, you then have bad data. Yes you can test, have QA done, etc.. but underlying fact is that if something changes in your data and your code does not handle the RI ,you're in problems.
Has anyone bench marked the difference in timing by processing RI in ETL to having RI in tables. If so please could you share some numbers.It would be interesting to see the gains.
tropically- Posts : 13
Join date : 2009-05-12
![-](https://2img.net/i/empty.gif)
» Best Practice For Indexes
» Reverse Key Indexes
» Constraints make a bad performance, but why?
» Sybase Indexes
» Constraints on Dimensions
» Reverse Key Indexes
» Constraints make a bad performance, but why?
» Sybase Indexes
» Constraints on Dimensions
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|