are FKs a detriment to performance?
-
especially on large tables? One of my DBAs oversees an Oracle installation of PeopleSoft and noticed that no foreign keys are declared on any of the 15,000+ tables. So we are wondering about the performance implications of FKs in SQL2K...
-
foreign key does not auto create index on the column. This indicates no performance impact.
In my experiece, index is needed for speeding search.
-
Foreign keys do impact performance on inserts and updates on all RDBMs.
Every time the data changes a sub-query has to be run on the parent table to ensure that the new data is valid. Setup a small test system and use profiler to monitor server activity with and without FK's
-
This is a nice thought:
You CAN'T HAVE YOUR CAKE AND EAT IT TOO!!
If you think about performance only then
1. FK are indeed afecting you insert and updates transaction speed!!
2. You Could set up Indexes on the Columns that are going to be used in joins to speed up the select queries
3. Now, You are TRUSTING Referential integrity to the Application and Your DATA QUALITY can be compromised if the application is not bullet proof (which more often than not that's hard to achieve)
So Pick your strategy and off you go ...
HTH
* Noel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply