Tell me more ×
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.
up vote 1 down vote favorite
1

I'm reviewing an SQL Server 2008R2 installation having large performance problems. Currently I'm looking into its indexes and foreign keys and have found out that none of the tables have any foreign keys specified. Either the application takes care of all the business logic by itself, or the installation is wrong.

How much do foreign keys affect performance? Or do they exist only for business logic? Are there any cases when not having foreign keys are an advantage? (e.g. this system supports SQL Server, Oracle and DB2.)

share|improve this question
1  
Is it also missing indexes on the join columns (the columns that would be the FK if any was defined)? – Martin Smith Dec 28 '12 at 12:50
@MartinSmith Yes I think so. The database has many tables and I have no real idea of how they join together so I can't swear it's true for all of them, but at least for some it is. – m__ Dec 28 '12 at 14:21

3 Answers

up vote 0 down vote accepted

Foreign Keys are a referential integrity tool, not a performance tool.

Foreign Keys are not automatically indexed and if they are not indexed this can cause performance problems at least in SQL Server

For more detail follow MSDN Article Improving SQL Server Performance

share|improve this answer
up vote 5 down vote

The link provided in bummi's comment has useful information. Depending on what you're doing, FKs can improve performance because SQL Server's query processor can rely on there being matching data in a table.

In what context are you asking your question? Do you have a performance problem already, or are you being cautious?

It's better to add in the FKs and then disable them later (don't remove them, just disable) then to never have them at all.

share|improve this answer
1  
It is important to note that foreign keys can only be used in this capacity if they are trusted. Meaning, if the foreign key was created or enabled with the WITH CHECK clause, and the check was successful. The NOT FOR REPLICATION clause will also render foreign keys "untrusted" on the subscriber side. – Matt M Dec 28 '12 at 13:15
@IanYates Yes, there are performance problems. Foreign Keys are just one thing that could be the problem...have already fixed index rebuild and statistics updates and such. – m__ Dec 28 '12 at 14:26
up vote 0 down vote

Use of FK is data consistency,. it slow down query operations(DML:-insert, update, delete) because it need to perform extra check operations.

Useful to automatic clean up operation for example on delete cascade effect.
Always create index on FK to make query run fast.

If your have some other(easy) to maintain data consistency then why to use FK. (may be you can handle at back-end system)

In my view a better design always allows FK, and its important feature of any DBMS.

share|improve this answer
2  
"It slows down query operations" is simply wrong. – a_horse_with_no_name Jan 2 at 8:31
@a_horse_with_no_name Is it wrong!!..I am very happy if its wrong...But in my understating I feel FK constraint slowdowns query. Please let me know Why this statement is wrong? Specially in MySQL. – Grijesh Chauhan Jan 2 at 8:45
3  
A query (select) will never be slower because of the existance of a FK constraints because no data is written and FK constraints are only validated when data is written. DML (insert, update, delete) might be affected – a_horse_with_no_name Jan 2 at 8:54

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.