Is there a performance hit by added nonenforced foreign keys to a SQL Server 2008 database?

Question

I'm working with a database and I want to start using LINQ To SQL with it. The database doesn't have any FKs inside of it right now for performance reasons. We are inserting millions of rows at a time to the DB which is why there aren't any FKs.

So I'm thinking I'm going to add nonenforced FKs to the database to describe the relationships between the tables for my LINQ To SQL but I don't want there to be a performance hit by adding nonenforced foreign keys.

Does anyone know what the effect of this might be?

Update: I'm using LINQ-To-SQL for the nonperformance intesive stuff. 80% of the data access is through stored procs on production. But for writing unit tests and other non performance critical tasks, LINQ-To-SQL makes data access really easy.

Update: Here is how you add a nonenforced FK

ALTER TABLE [dbo].[ACI] WITH NOCHECK ADD CONSTRAINT [FK_ACI_CustomerInformation] FOREIGN KEY([ACIOI]) REFERENCES [dbo].[CustomerInformation] ([ACI_OI]) NOT FOR REPLICATION GO

ALTER TABLE [dbo].[ACI] NOCHECK CONSTRAINT [FK_ACI_CustomerInformation] GO

Answer 1

It may have some impact, especially at those volumes.
However I would test this on a similiar system first, so you can measure the impact, if any.

To be honest though, I would probably use hand written stored procedures for this, so you can optimize them as required, instead of using LINQ to SQL.

Answer 2

The answer can be different for different environments (data/logs on same drive, tempdb on same drive, lots of cache vs little, etc) so the best way to find this out is to benchmark. Create two identical databases, one with fk's and one without. Do your normal million-row-load into each database, and measure your transactions per second. That way you'll know for sure in your own environment.

Answer 3

I realize this is an old question, but I want to comment on how bad a practice it is to create a FK that is not enforced on existing data. If in fact there is a need for a foreign key, you need to fix any bad data before adding the foreign key (which should have been added at design time) not try to ignore it. All you are doing is masking your very serious data integrity problem by refusing to notice it and do something about it. There is the occasional need to do this due to changed requirements, but it should not be considered as a first choice of techniques when adding a foreign key to a table that has data. Finding and fixing the bad data should be.

Data that has no relationship to the PK is useless. If I had a order table with a customer id that no longer existed in the customer table, how would I know who ordered the product? Of course this is why the FKs should have been enforced from the beginning whether you did million row inserts or not. I do multi-million row inserts through SSIS on a daily basis to many many tables that have foreign keys, to use this as a reason for not setting them up in the first place indicates a lack of understanding of database design. Sacrificing your data integrity to speed is ALWAYS a poor idea. Without data integrity, your database is unreliable and therfore useless.

Answer 4

Foreign keys will create non-clustered indexes in your table, which will improve performance of joins on foreign keys.

Extra indexes will decrease the performance of your insert/update/delete/merge statements and will increase table sizes.

http://msdn.microsoft.com/en-us/library/ms191195.aspx

Even when created with NOT FOR REPLICATION the indexes are still present and SQL Server will need to maintain them.

In your case I would either: - use foreign keys and take performance hit or - not use foreign keys in production (goodbye data integrity) and run my tests against a copy of production database for which I would create foreign keys.