Does Foreign Key improve query performance?

Question

Suppose I have 2 tables, Products and ProductCategories. Both tables have relationship on CategoryId. And this is the query.

select p.ProductId, p.Name, c.CategoryId, c.Name AS Category
from Products p inner join ProductCategories c on p.CategoryId = c.CategoryId
where c.CategoryId = 1;

When I create execution plan, table ProductCategories performs cluster index seek, which is as expectation. But for table Products, it performs cluster index scan, which make me doubt. Why FK does not help improve query performance?

So I have to create index on Products.CategoryId. When I create execution plan again, both tables perform index seek. And estimated subtree cost is reduced a lot.

My questions are:

1. Beside FK helps on relationship constraint, does it have any other usefulness? Does it improve query performance?

2. Should I create index on all FK columns (liked Products.CategoryId) in all tables?

Answer 1

Foreign Keys are a relational 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.

Answer 2

Hi,

A foreign key is a relational database 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.

Cheers, John

Answer 3

1. As stated here Foreign keys boost performance

2. You should always create indexes on FK columns to reduce lookups. SQL Server does not do this automatically.

regards,

Lieven

Answer 4

You can use it to help make a query more efficient. It does allow you to restructure queries in SQL Server to use an outer join instead of an inner one which removes sql servers necesity of having to check if there is a null in the column. You don't need to put that qualifier in because the foreign key relationship already inforces that for you.

So this:

    select p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
from Products p inner join ProductCategories c on p.CategoryId = c.CategoryIdwhere c.CategoryId = 1;

Becomes this:

SELECT p.ProductId, p.Name, c.CategoryId, c.Name AS Category 
FROM ProductCategories c 
LEFT OUTER JOIN Products P ON
c.CategoryId = p.CategoryId 
WHERE c.CategoryId = 1;

This won't necessarily make a huge performance in small queries, but when tables get large it can be more efficient.

Answer 5

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.

Answer 6

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.

Answer 7

Check this out

http://www.microsoft.com/technet/abouttn/flash/tips/tips%5F122104.mspx