patternMinor
How much do foreign keys affect performance?
Viewed 0 times
muchforeignaffectkeysperformancehow
Problem
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.)
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.)
Solution
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.
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.
Context
StackExchange Database Administrators Q#31119, answer score: 5
Revisions (0)
No revisions yet.