Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm bulk inserting rows into a database and I'm finding (through the query analyser) that the foreign key constraints are taking about 20% of the insertion time. I've tried creating a separate index on the tables containing just the key so that the check could hopefully just do an index seek rather than a clustered index seek but this didn't seem to work.

The next thing I was planning on trying was to disable to check constraints temporarily so I was wondering if there is a way to do this on a per transaction basis.

Failing that I will probably try the performance with the constraints removed as they are also enforced in our DataAccess layer so in theory are redundant.

This SO question contains a possible solution, but it's more permanent than temporary.

share|improve this question
    
Please explain what "didn't seem to work" means. Do you mean the new index wasn't used? If so drop and recreate the FK constraints –  Martin Smith Dec 24 '11 at 10:33
    
I do indeed mean that the index wasn't used. I'll have a look at that link. Thanks –  Matthew Steeples Dec 24 '11 at 11:31
    
BTW I have edited the title to say "Temporarily disable checking of constraints" instead of "Temporarily disable check constraints" as that seems more accurate based on the body of the question. Not sure if it will get approved but you might want to clarify this anyway. –  Martin Smith Dec 24 '11 at 14:08

2 Answers 2

Try this and do not forget bring them back! 8-) This is for CHECK constraints

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.check_constraints fk

PRINT @sql

This is, almost the same, for FKeys

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.foreign_keys fk

PRINT @sql

You know what to do with the scripts, yeah?

The best way I think - is to disable constraints right after BEGIN TRANSACTION and enable them back just before COMMIT

Hint - how to enable constraint without checking all the data on table:

CREATE TABLE test (id INT CONSTRAINT chk CHECK (id < 10))
GO 
INSERT test VALUES (1)
GO
ALTER TABLE test NOCHECK CONSTRAINT chk
GO
INSERT test VALUES (10)
GO
ALTER TABLE test WITH NOCHECK CHECK CONSTRAINT chk
GO
SELECT * FROM test
GO
INSERT test VALUES (10)

Only the last statement will generate an error Pay attention on WITH NOCHECK CHECK

But as Martin Smith noticed in the comments - it is the kind of worst practice.

So, the proper way is indexed fkey columns and enabling the constraints just before commit WITH CHECK CHECK, but if you sure about your data and the performance of bulk insert is much more important, than data purity, then you can do checks later, after the transaction already fixed, but this case cannot be called proper

share|improve this answer
    
How will that help? Disabling and Re-enabling them will mean that it has to validate not just the newly added data but all the data in the table. –  Martin Smith Dec 24 '11 at 13:53
    
@ Martin Smith - no, you are wrong, try this script - error will be only on last statement –  Oleg Dok Dec 24 '11 at 14:13
    
CREATE TABLE test (id INT CONSTRAINT chk CHECK (id < 10)) GO INSERT test VALUES (1) GO ALTER TABLE test NOCHECK CONSTRAINT chk GO INSERT test VALUES (10) GO ALTER TABLE test WITH NOCHECK CHECK CONSTRAINT chk GO SELECT * FROM test GO INSERT test VALUES (10) –  Oleg Dok Dec 24 '11 at 14:13
3  
You should never re-enable constraints with NOCHECK this means they are untrusted and cannot be used by the query optimiser as well as meaning of course that invalid data can exist in the database. –  Martin Smith Dec 24 '11 at 14:16
    
Yes, I know that it is a "worst practice", but I just answer the question. As a newbie here I just want to get more points 8-) –  Oleg Dok Dec 24 '11 at 14:26

In order to get your new narrower index used for foreign key constraint validation you will need to drop and re-create the foreign keys.

You will then see in sys.foreign_keys that they are bound to the better index and it will use that for validation.

NB: This isn't documented anywhere but is definitely the way it appears to work!

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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