Foriegn Keys Harm or Help Performance
-
Hi,
I am considering implementing a ton of foriegn keys on a SQL 2K database with about 150 tables, 8 gigs in size. Does the number of foriegn keys a table has impact performace at all?
Thanks in advance.
-
Yes, a foreign key is placed to enforce referential integrity. When a record is inserted the server will read the records in the Primary Key of the referenced table to make sure object exists, if not then you get an error. Basically, the more foreign keys you have the higher the number of reads on the database and thus the higher amount of IO on the disk subsystem there will be. ANother option which you may want to consider is this:
When I have a support table with a fixed number of values that will not change (or at least not often) I will opt not to use a Foriegn Key but instead use a Check Constraint to validate the column in question fits within the range normally established when using an FK. The major reason is no reads have to be made against the support table thus increasing overall performance.
My suggestion is to look at the FKs you need and try to find Check Constraint options, Stored Procedure options or even Trigger options to reduce the reads on your database. And limit the number of FKs as much as possible in other manners.
-
FKs never help performance. As has been said when inserting data a check has to be made that the PK value exists. Worse is when you delete a record in the primary table. This has to check every table that has a FK to it to check that the PK value is not used, if you haven't indexed these tables (if large) then the delete will take and age and block lots of rows.
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons -
I agree. The downside is that without the fkey you can end up with orphan records. Good coding can prevent that. We run maintenance routines to catch the rest, some can be fixed automatically, some require user input.
Andy
-
You will need to consider how many inserts and updates you will perform, and whether the performance issues out ways to data integrity issues, that might occur without FKs.
Gregory Larsen, DBA
If you looking for SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
-
If I remember right, the performance implications of Foreign Keys was one of the reasons it wasn't a high priority on the MySQL project. However, it's in there now because there was enough screaming.
If you're doing data warehousing, foreign keys don't get you much because the data is static. We use foreign keys on our transactional systems because we can't guarantee the application coding is going to be up to snuff (developers are in a different group) but we're still responsible for data integrity. So it's one of those, "For our own protection," types of things. Your mileage may vary.
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley -
Thanks all for the great feedback.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply