<

Can Foreign key improve performance?

Published on
17,499 Points
10,499 Views
5 Endorsements
Last Modified:
Approved
Community Pick
We use referential integrity to validate data. But does that really help or hinder performance?  The answers to these questions are Yes, they can and No, they don’t.  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.

Let's take an example to understand the behavior with a foreign key constraint.

Create the following two tables...

create table Employee(EmployeeID int primary key) 

create table EmployeeOrder(OrderID int primary key, EmployeeID int not null constraint fkOrderCust references Employee(EmployeeID)) 

Open in new window


Now run the following query from Query menu and include Actual Execution plan.

Select * from EmployeeOrder eo 
where exists 
( 
select * From Employee e 
where eo.EmployeeID = e.EmployeeID 
) 

Open in new window


You can notice that optimizer did not access Employee table and is not shown in execution plan. This is because the optimizer knows that it is not necessary to execute the EXISTS operator in this query because the foreign key constraint(Trusted constraint) requires all EmployeeOrders to refer to an existing Employee, which is what the WHERE clause checks.

Now drop EmployeeOrder table by executing drop table EmployeeOrder and recreate the table with the following query without foreign key constraint

Create table EmployeeOrder(OrderID int primary key, EmployeeID int not null ) 

Open in new window


Now execute the above select query again and see execution plan.

You can see this time optimizer executes the EXISTS operator and Employee table is shown in execution plan. This is because no foreign key constraint was found and SQL Server could not be sure that all orders actually have valid employee references. Therefore it had to execute the EXISTS operator.

I have found subtreecost of the first query was 0.0376 and the second one is 0.0443. Remember these are empty tables. For a large table, this can make a huge difference in performance.

But for any DML operation i.e. Insert, Update & Delete, foreign key constraint degrades performance as SQL Server needs to validate data with primary table's column. However, the data is referentially correct and that can save some additional time with queries being able to rely on data integrity.
 

5
Comment
Author:s_niladri
1 Comment
 
LVL 44

Expert Comment

by:pcelba
Hi,

additional question to the Foreign key is: Do we need to create an index on columns having foreign key constraint defined?
0

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month