Can foreign keys hurt query performance

Question

As I understood from this post, there are some scenarios where foreign keys can improve query performance.

I've heard the opposite claim though, that because of referential integrity checks, foreign keys can actually hurt query performance. Under which conditions (if at all) is this true?

---

1) The term query seems to be misleading. I am interested in all kinds of performance penalties.

2) Does anyone have any real-world numbers about the negative impact on INSERT, DELETE or UPDATE statements (I know it depends on the specific system, but nevertheless any kind of real-world measurements would be appreciated)?

Answer 1

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.)

Answer 2

Foreign keys can cause inserts(or some updates) in child tables or deletes from parent tables to take longer. This is a good thing however as it means that it is making sure that the data integrity remains. There is no reason whatsoever to skip using foriegn keys unless you don't want to have useful data. You wil not normally notice much differnce unless you have many foreign keys realted to the same parent table or if you are inserting or deleting many records in one batch. Further, I have noticed, users are more tolerant of a couple of extra seconds in an insert or delete than they are in a select. Users are also not tolerant at all of unreliable data which is what you have without foreign key constraints.

You will need to index them to improve performance on select queries.

Answer 3

If foreign keys had any impact in that way, it would be on INSERTS. The database does the referential checking on foreign keys when records are created/modified, not SELECTed.

Answer 4

I believe the noted post pointed out that putting an index on FK fields improved performance, not simply that a FK relationship improved performance. The existence of a FK on a table should not have any effect on a SELECT query, unless JOIN operations are being done, at which point, the FK relationship AND index on FK fields would improve performance.

Answer 5

Foreign keys will not adversley affect query performance in most cases, and are strongly recommended. By aiding normalization, you will eliminate redundant data, and if you follow up by adding underlying indexes (for the appropriate foreign key) you will get good performance on your queries.

Foreign-keys can help the query optimizer get the best query plans for a given query.

Foreign-key checking is a factor when you update your data (which is a separate consideration - I assume here your concern is query - unless by the word query you imply both).

Answer 6

For INSERT/UPDATE/DELETE the short answer is, "Yes". The database will need to check that the referential integrity is intact and the creation/modification is allowed. Or in DELETE's case, there may be some cascading to be done.

For SELECTs, it's actually quite the contrary. Foreign Keys have a secret added benefit of showing you exactly where you're most likely to be doing complex JOINs and have very commonly used fields. This makes the job of indexing much easier, and you can pretty much guarantee that all of your FK fields should be indexed. This makes SELECTs much faster.

Answer 7

if a foreign key is needed for referential integrity then the presence of the foreign key should form the baseline for performance

you might as well ask if a car can go faster if you don't put seats in - a well formed car includes seats as much as a well formed database includes foreign keys

Answer 8

In theory, yes: data writes need to validate the constraints.

In practice, seldom: unless measured and proved otherwise, you can assume there is no performance impact. Overwhelmingly, performance problems occur due to other problems:

• bad schema design (missing indexes, bad clustered index choice)
• contention (blocking), again due to bad schema design (table scans guarantee lock conflicts)
• bad query design

On a well designed schema and good querries the cost of constraints will start to show up at very high throughput. When this happens, there are preventive measures.

My 2c: Never sacrifice correctness constraints for some elusive performance goals. In the very rare case when the constraints are indeed the problem there are measurements to show that's the case, and as the saying goes: if you have to ask how much it costs, you can't afford it. If you have to ask if constraints can be a problem, you can't remove them (no offence intended).

Answer 9

If you're enforcing referential integrity, INSERTs, and UPDATEs that effect the FK field, will be slower. However, it's usually not much to worry about, especially as a lot of DBs are 80% read/20% write. It's also a price worth paying.

Creating an index on a foreign key is often beneficial, though obviously it how much depends on what SELECT statements you're running.

Generally, you need foreign keys due to normalisation (which avoids duplicate data and synchronisation problems). Normalise to the 3rd degree, and then after analysing real world performance can you consider de-normalising.