<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Can Foreign key improve performance?

Published on
19,018 Points
12,018 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

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Join & Write a Comment

This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Other articles by this author

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month