We are in the process of setting up RI (mostly foreign keys, some sp's) on our SQL Server 2000 DBs. One of the questions that has come into play is how bad the performance hit will be once the RI has been put into place. We are using VFP 7 and C# for our front ends, BTW.
I was just wondering if there was anyone out there who has seen any performance difference between an application with RI and one without. If so, how bad of a hit was it, and what type of RI did you use (FKs, SPs, triggers, etc...). If anyone has any advice on how to reduce the performance hit, that would be appreciated as well!
the question should not be stated in terms of performance of RI versus performance of not having RI
the question should be performance of RI versus performance of doing the same RI actions in application code
the answer should be, let the database run the RI actions
if you are considering giving up RI, you are saying you are willing to forego having valid data, and i don't believe that's a performance question, but an integrity question
let's consider a (somewhat contrived) analogy -- what if your bank said to you "every time we transfer money from your checking account to your savings account, do you want us to make sure the money actually arrived in the savings account? or would you like to give up that assurance in order to get your card out of the ATM a few milliseconds sooner?"
We recently converted all of our data to SQL Server - currently we have NO RI in place. Some of the RI we have planned to put in place is critical, and will go no matter what the impact - we just want to be prepared. Some of it is somewhat redundant and not as necessary, and the dreaded management wants to weigh the pros and cons of putting this RI into place. This why I am asking the performance question.
The application was actually written in VFP 6/7 and was designed to use the native FoxPro tables. once some of the DBCs (there are about 30) started nearing the 2 GB limit, we had to 'tweak' (word used by mgmt, it was more like rewrite) the program to be SQL Server compliant. In the midst of all this our DBA ran screaming for the hills, so I recently got the position. The hired guns who did the data conversion neglected to convert the extensive VFP RI on the old DBs to SQL (along with other insignificant things like memos and field descriptions), and now I am stuck in cleanup mode. Since the RI has to be rebuilt from the ground up, mgmt is taking the oppurtunity to sacrifice data integrity for customer-visible speed.
I've been doing some testing on mass updates with RI hooked and then unhooked, and have noticed about a 7% hit for the essential RI. Not a big deal considering we got a 238% speed increase once we converted to SQL Server DBs (the application goes over a WAN). I haven't wriiten the SPs and triggers for the 'nonessential' portion yet, and mgmnt doesn't want me to until they are comfortable it would 'not be a waste of my' + underpaid + 'time.'