March 3, 2011 at 9:42 am
I was thinking on the subject of dropping indexes and constraints while doing a large insert. Indexes I feel pretty comfortable with doing and understand the advantages of doing so. However when it comes to constraints, with the exclusion of a primary key constraint which is always a clustered index, is there any performance advantage with dropping these? I'd prefer to leave them in place in order to stop the possibility of inserting records which could violate a constraint ie: unique column value constraints and/or Foreign Key Constraints.
March 4, 2011 at 2:14 am
There will be a performance gain if you disable them. However, the check constraints will be marked as non-trusted when you enable them. So it might be a better idea to drop them and recreate them.
I would leave unique constraints, as checking for duplicates within SSIS can be expensive.
If you're doing an ETL process for BI, I would drop the foreign keys and the check constraints. Even not use them at all. As the ETL is an automated process that fills the tables, it is up to the ETL to do the data validation and to enforce relational integrity.
Indexes are only needed if you are reporting directly on the relational database, otherwise drop them also.
If you drop all these, and set the recovery model of the database to simple, you will have significant performance gains for the INSERTS.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply