CODEWITHSUNDEEP
databaseperformancenhibernate
Felice Pollano
Felice Pollano

Reputation: 33252

ForeignKey and fetching performance

Let's consider the following scenario: I've a "master table" with a "detail" table. The detail table have just a foreign key pointing to the primary one ( not a primary key ). This is the schema that NHibernate generates for me when I map a simple bag. My question is, does the FK itself on the detail table suffices to have queryes without full scan? In other world, having or not having the FK defined on the detail table, does change the performance? I guess yes, but I don't know if I'm right, or where to find a source to explain it.

Upvotes: 0

Views: 41

Answers (1)

Gert Arnold
Gert Arnold

Reputation: 109079

In both cases (FK or no FK) you'll have to create an index on the FK field in the details table to prevent a table scan. In sql server, when creating an FK constraint an index is not created automatically.

See MSDN, "Indexing FOREIGN KEY Constraints".

Upvotes: 1

Related Questions