Menu
Yes it will improve the performance of you db if you are checking integrity using foreign key instead of running many queries for checking the record is exist in database in your program. Show activity on this post. You can use it to help make a query more efficient.
It's a common mistake to avoid creating foreign keys in a database because they negatively impact the performance. It is true that foreign keys will impact INSERT, UPDATE and DELETE statements because they are data checking, but they improve the overall performance of a database.
Having active foreign keys on tables improves data quality but hurts performance of insert, update and delete operations. Before those tasks database needs to check if it doesn't violate data integrity. This is a reason why some architects and DBAs give up on foreign keys at all.
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.
Foreign Keys can improve (and hurt) performance
As stated here: Foreign keys boost performance
You should always create indexes on FK columns to reduce lookups. SQL Server does not do this automatically.
Edit
As the link now seems to be dead (kudos to Chris for noticing), following shows the gist of why foreign keys can improve (and hurt) performance.
Can Foreign key improve performance
Foreign key constraint improve performance at the time of reading data but at the same time it slows down the performance at the time of inserting / modifying / deleting data.
In case of reading the query, the optimizer can use foreign key constraints to create more efficient query plans as foreign key constraints are pre declared rules. This usually involves skipping some part of the query plan because for example the optimizer can see that because of a foreign key constraint, it is unnecessary to execute that particular part of the plan.
A foreign key is a DBMS concept for ensuring database integrity.
Any performance implications/improvements will be specific to the database technology being used and are secondary to the purpose of a foreign key.
It is good practice in SQL Server to ensure that all foreign keys have at least a non clustered index on them.
I hope this clears things up for you but please feel free to request more details.
Your best performance bet is to use Indexes on fields you use frequently. If you use SQL Server you can use profiler to profile a specific database and take the file that outputs and use the tuning wizard to recieve recommendations on where to place your indexes. I also like using profiler to flush out long running stored procedures, I have a top ten worst offenders list I publish every week, keeps people honest :D.
I do not know much about SQL server, but in case of Oracle, having a foreign key column reduces the performance of data-loading. That is because database needs to check the data integrity for each insert. And yes, as it is already mentioned, having an index on foreign key column is a good practice.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With