Can Foreign key improve performance?: SQL Server 2000, 2005, 2008

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

1:
2:
3:

Select all 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 
)

1:
2:
3:
4:
5:
6:

Select all 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 )

1:

Select all 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.

Gain Access to all our Tech Resources

Get personalized answers

Ask unlimited questions

Access Proven Solutions

Search 3.2 million solutions

Read In-Depth How-To Guides

1000+ articles, demos, & tips

Watch Step by Step Tutorials

Learn direct from top tech pros

And Much More!

Your complete tech resource

30-day free trial. Register in 60 seconds.

Can Foreign key improve performance?

SQL Server 2000

2005

2008

MS SQL Server

About The Author

More Articles From s_niladri

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MS SQL Server Experts