Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
SamC
White Water Yakist
3467 Posts |
 Posted - 2004-01-05 : 19:11:24
|
| I've read a few posts which suggest that the use of FK constraints in SQL tables doesn't affect query performance. What I've read echoed opinion.Are there any articles around which post measured query performance before and after FK constraints were imposed?Sam |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-05 : 20:29:18
|
| No, that's not true, and that's not what the blog was trying to suggest. Foreign keys DO affect performance in all databases, but the reason(s) for using them have nothing to do with performance. The blog was meant to point out that MySQL has essentially sacrificed data integrity even in features that are supposed to enforce it.As far as performance goes, foreign keys are faster than equivalent triggers, but they cannot work across separate databases as triggers can. While there are arguments for either method, foreign keys are the best way to provide data integrity between related tables, and if your data is important to you it would be bad design to ignore foreign keys for the sake of performance. A fast database is worthless if it delivers inaccurate data. |
 |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-01-05 : 20:39:20
|
| Sounds right.Thanks.. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2004-01-05 : 22:43:40
|
quote:
I've read a few posts which suggest that the use of FK constraints in SQL tables doesn't affect query performance
And if I had read this correctly the first time, I would've said that you are correct.  QUERY performance is not affected by foreign keys, but INSERT/UPDATE/DELETE operations are. These operations need to ensure that they do not violate foreign key constraints; a plain old SELECT statement will never violate a foreign key. Sorry about that. |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2004-01-05 : 23:16:07
|
| Again, it all sounds about right. FK's shouldn't affect query performance, but anything that modifies or writes a FK should take a small hit.Sam |
|
|
|
|
|
|
FK performance