Is There a Severe Performance Hit for Using Foreign Keys in SQL Server

Is there a severe performance hit for using Foreign Keys in SQL Server?

There is a tiny performance hit on inserts, updates and deletes because the FK has to be checked. For an individual record this would normally be so slight as to be unnoticeable unless you start having a ridiculous number of FKs associated to the table (Clearly it takes longer to check 100 other tables than 2). This is a good thing not a bad thing as databases without integrity are untrustworthy and thus useless. You should not trade integrity for speed. That performance hit is usually offset by the better ability to optimize execution plans.

We have a medium sized database with around 9 million records and FKs everywhere they should be and rarely notice a performance hit (except on one badly designed table that has well over 100 foreign keys, it is a bit slow to delete records from this as all must be checked). Almost every dba I know of who deals with large, terabyte sized databases and a true need for high performance on large data sets insists on foreign key constraints because integrity is key to any database. If the people with terabyte-sized databases can afford the very small performance hit, then so can you.

FKs are not automatically indexed and if they are not indexed this can cause performance problems.

Honestly, I'd take a copy of your database, add properly indexed FKs and show the time difference to insert, delete, update and select from those tables in comparision with the same from your database without the FKs. Show that you won't be causing a performance hit. Then show the results of queries that show orphaned records that no longer have meaning because the PK they are related to no longer exists. It is especially effective to show this for tables which contain financial information ("We have 2700 orders that we can't associate with a customer" will make management sit up and take notice).

Can foreign keys hurt query performance

I'm assuming that for INSERT queries, constraints - including foreign key constraints - will slow performance somewhat. The database has to check that whatever you've told it to insert is something that your constraints allow it to insert.

For SELECT queries, foreign key constraints shouldn't make any changes to performance.

Since INSERTS are almost always very quick, the small amount of extra time won't be noticeable, except in fringe cases. (Building a several gigabyte database, you might want to disable constraints and then re-enable later, as long as you're sure the data is good.)

Does Foreign Key improve query performance?

Foreign Keys are a referential integrity tool, not a performance tool. At least in SQL Server, the creation of an FK does not create an associated index, and you should create indexes on all FK fields to improve look up times.

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

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.

Do missing foreign keys in the database have an effect on sql generated by EF?

Negative impact on performance.

I can think of two effects of the presence of foreign keys.

1. A tiny negative impact on inserts and updates, because the keys have to be checked. However, compared to everything else taking place in one complete database roundtrip this effect is totally negligible. Absolutely no reason to refrain from using them. It will never ever outweigh the benefits of data integrity.
2. A tremendous performance gain when foreign keys are set up with cascaded deletes and updates.

In short, there is no reason for deliberately omitting foreign keys.

Of course, legacy can't always be undone overnight. If there is any room for changes in the database schema, I'd go for it. If not, you may consider manually adding common associations in the edmx model. These associations will not be erased by updating the model from the database.

Foreign keys when cascades aren't needed

You must to do it. If it will touch performance in write -- it's a "pixel" problem.

Main performance problems are in read -- FKs could help query optimizer to select best plan and etc. Even if you DBMS(-s) (if you provide cross-DBMS solution) will gain from it now -- it can happen later.

So answer is -- yes, it's not only aestetics.

Does introducing foreign keys to MySQL reduce performance

Assuming:

1. You are already using a storage engine that supports FKs (ie: InnoDB)
2. You already have indexes on the columns involved

Then I would guess that you'll get better performance by having MySQL enforce integrity. Enforcing referential integrity, is, after all, something that database engines are optimized to do. Writing your own code to manage integrity in Ruby is going to be slow in comparison.

If you need to move from MyISAM to InnoDB to get the FK functionality, you need to consider the tradeoffs in performance between the two engines.

If you don't already have indicies, you need to decide if you want them. Generally speaking, if you're doing more reads than writes, you want (need, even) the indicies.

Stacking an FK on top of stuff that is currently indexed should cause less of an overall performance hit than implementing those kinds of checks in your application code.

Why should i create foreign key constraint?

I'd say develop the application with foreign key constraints in place, then measure the performance, and if performance is an issue, measure the difference between the DB with FK constraints and without, and if they prove to be a performance issue, consider eliminating them.

It is unlikely that you'll find that they are the source of any performance issues, and I wouldn't recommend omitting them entirely from the outset on the guess that it will lead to better performance.

You don't need them, in the same way that you don't need to validate input, wear safety belts, etc.

Why should i create foreign key constraint?

I'd say develop the application with foreign key constraints in place, then measure the performance, and if performance is an issue, measure the difference between the DB with FK constraints and without, and if they prove to be a performance issue, consider eliminating them.

It is unlikely that you'll find that they are the source of any performance issues, and I wouldn't recommend omitting them entirely from the outset on the guess that it will lead to better performance.

You don't need them, in the same way that you don't need to validate input, wear safety belts, etc.

Does a foreign key automatically create an index?

A foreign key is a constraint, a relationship between two tables - that has nothing to do with an index per se.

But it is a known fact that it makes a lot of sense to index all the columns that are part of any foreign key relationship, because through a FK-relationship, you'll often need to lookup a relating table and extract certain rows based on a single value or a range of values.

So it makes good sense to index any columns involved in a FK, but a FK per se is not an index.

Check out Kimberly Tripp's excellent article "When did SQL Server stop putting indexes on Foreign Key columns?".

---

Related Topics