vmssanthosh (11/15/2010)

---

hi
We 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

vmssanthosh (11/15/2010)

---

I didnt say index is a replacement for foreign key. I says that query performance depends on the indexes not on referential integrity components. even it helps in generating a good execution plan, we always have the option of changing the execution plan using the query hints.

So why we need to sacrifice the insert/update operations performance.

When we define our transactions tables with more foreign keys/constraints, it in turn reduce the insert/update operation performance.

Most of the transactions details will be retrieved using the index columns. then it will be joined with other references to get appropriate information. So retrieving data from transactions table will always be faster and it needs less/no optimization plans.

Regards
VMSSanthosh

neeraj.it (4/25/2013)

---

Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.

Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.

Thanks

neeraj.it (4/25/2013)

---

Based on my experience of a database where million of records are present and being operated for inserted/updated/deleted in a year. We were having proper constraints for data integrity purpose and there was no problem at all in intial time, but later on DML operations getting slower and reached to unacceptable level sometimes failed to execute. Then we removed the constraints to get performance back in the database.

Hence for SELECT operation definitely there is a benefit else DML operations will be slow later on based on amount of data.

Thanks