vmssanthosh (11/15/2010)
hiWe mostly do bulk loads in our databases. nearly a lakh data will get inserted/updated in our databases everyday.
In those cases, fk relationship will degrade the insert/update operation. We handles the referential integrity at business rules. Also, we dont have any delete operation for any of our master tables. we does active/deactive for our master tables.
Our indexes are taking care of query performance and we uses index hints to do the same if required.
According to me, it depends upon the way we are maintaining the databases.
My point is it should not misguide the people tat fk's improves the database performance. Most of the transactional databases doesnt require fk relationship.
Regards
VMSSanthosh
Actually, FK's do increase performance. The optimizer will know, and understand, that you have an enforced relationship and take advantage of that information in ways that simply having an index in place will not provide. The fact that you're also talking about using hints as a casual and standard method suggests you have a number of tuning opportunities that you might not be exploring. I'd suggest digging a bit deeper in understanding how this stuff works.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning