RI vs Speed

Hello,

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!

Thanks!

-Justinthe 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?"

rudy
http://rudy.ca/|||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.|||i understand, and sympathize :)

having myself been a clueless pointy-haired boss in the past, i can understand where they're coming from

however, i think you still need to make the point about the purpose of RI, which is to ensure data integrity

now, granted, if it's an existing application which is being modified to use a new database, then you have an interesting situation

presumably the existing app already takes care of RI concerns itself (e.g. not inserting an order for a customer that doesn't exist) so adding RI in these circumstances seems rather pointless

in other words, there's little to be gained (in a bottom-line cost-benefit sense) from adding RI constraints to a database for an app that's already working

when framed in this context, i would suggest setting up the database without RI to start with, taking several volume test performance measures, applying RI, and running the performance tests again

i predict the RI overhead will not be noticeable, or at least no more than a 5% increase

your mileage may vary, since so much depends on the actual tables and relationships involved

rudy|||Thanks for your reply :)

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.'