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 |
|
Simon_L
Starting Member
32 Posts |
 Posted - 2010-05-26 : 06:13:56
|
| Having a huge debate this morning as to whether having RI i.e FK relationships on a database has a negative effect on performance when doing data manipulation due to the overheard of having to look up the RI before doing the aforementioned manipulationI think I might be forced to shoot several developers at this rate so someone point me to some wonderful words of wisdom that can put this to bed once and for all !!!Simon |
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-26 : 06:50:53
|
| Yeah, that's true. I find that deletes are slower due to RI checks.However, I cannot give you a 100% guarantee that my programs have absolutely no bugs (even though all my pigs are fed, and ready to fly!!), and thus having the long-stop of R.I. is important to me.Some folk may lean on R.I. as a crutch for cascade-deletes and the like, too ... that doesn't work too well if you delete the R.I. once the QA is done and you rollout to Production!Sorry, not the link to "wonderful words of wisdom" that you asked for ... |
 |
|
|
dportas
Yak Posting Veteran
53 Posts |
Posted - 2010-05-26 : 10:50:09
|
| Depends on many things. For example if you are updating or deleting rows from a table referenced by a foreign key then it helps to have an index on the foreign keys in other tables.It also depends on what alternative you might have in mind. Either you want to enforce an integrity rule or you don't. Referential integrity is an important aspect of data integrity in most applications. If you want to enforce RI then a foreign key is usually the most efficient way to do it. On the other hand if you and your users/customers truly don't care about some constraint being enforced then why create the constraint in the first place? |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2010-05-26 : 11:24:56
|
IMHO it is a pitty that we have to talk about it.I have NEVER heard that there was a big performance problem and they have solved it by throwing away RI.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2010-05-26 : 11:29:18
|
| My DELETEs would definitely be a lot fast if I threw away RI. (I should try some timings, eh?!)But the other problems I would have, sooner or later, would cost more time ... but also they would cost the company lost revenue and lost customers ... |
|
|
|
Simon_L
Starting Member
32 Posts |
Posted - 2010-05-26 : 11:44:31
|
| personally I think the benefit of any performance increase means nothing compared to the benefits of RI etc but thats not the issue ... unfortunately :-) so yes its a pity we have to talk about it :-) but on the other hand Im trying to gather information to put forward a case to say whether there is an appreciable hit etc and wanted others opinions on the matter.. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2010-05-26 : 11:52:12
|
| Not sure how "wise" these words are (more wise-ass perhaps), but ask your team:-As a developer, is it more important to you that your personal bank transactions are FAST, or ACCURATE?-As a developer, is it more important to you that your credit card transactions are FAST, or ACCURATE?-As a developer, is it more important to you that your life threatening brain surgery is done QUICKLY, or ACCURATELY?All joking aside, the important thing is this: can your company afford to have data inconsistencies, AND will your customers accept such inconsistencies? Especially if it's their money?It could very well be that you can live without RI, but you'd better make sure everyone up your management chain agrees with the consequences. |
|
|
|
|
|
|
|
|
RI having a negative affect on performance