Thursday, April 21, 2011

Does Referential Integrity improve performance?

I believe that everybody knows that Referential Integrity is for maintaining the relationship between two entities; Parent and Child. It makes sure that no child records is exist without a parent. Referential Integrity is implemented with Foreign Keys. The foreign key column can be set as Nullable, in that case it allows to have a child without a parent Smile.

While I was going through my one of my old presentations, I found a slide related to this (yes, it is good to keep old presentations, human forgets things). Let me explain it with a code;

image

The above code is executed against AdventureWorks2008 database. Note that it gets records only from Detail table even though Header table is joined. You may say that this join is useless because it does not get data from Header. True, but see, though we have linked the header to detail, as per the plan, SQL Server does not access the header. It knows that referential integrity is implemented and existence of parent records is guaranteed, hence it does not need to access the header. Now let’s remove the relationship between header and detail, and execute the same.

image

Query is same but in this case, SQL Server scans the header table. The reason is it does not know whether records are exist in header for every child, it accesses the header though there is no request for values in header. Can you see, referential integrity is not only for making sure that database is consistence, it improves the performance too.

1 comment:

  1. how much is the performance difference vs size?

    Thanks

    ReplyDelete