May 23, 2008 at 12:20 am
Hi,
We are building a centralized Production Support application for our entire organization, in which we are expecting around 15000 tickets/ day (across all projects). The Tickets Table is expected to grow substantially and also it is highly transactional (frequent insert/ updates). There could be around 7 foreign keys in the table.
- We are planning to remove the Foreign Key references, since for every insert/ update, the referential integrity would be checked - which would be a performance hit.
- We are planning to check the referential integrity in the code.
We know the implications in removing the referential integrity from the table. Do you suggest removing the Referential Integrity from table improves performance?
Thanks,
Nags
May 23, 2008 at 6:49 am
That's only one insert every 5.76 seconds. Even if you assume that all the records will come in over a 12 hour period that's only one every 2.5 seconds. Even if it's only an eight hour period you're only looking at one insert every 1.6 seconds.
If it were my system, I couldn't justify removing referential integrity to save 10 or 15 ms on an insert that couldn't possibly need that 10 or 15 ms. Protecting the data is much more important than that.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 23, 2008 at 7:26 am
I agree with Grant with the addition that DRI is one of the main purposes of an RDBMS system so it should do the job at least as efficiently as checking 7 columns in the application. It also enforces the integrity in the case when someone (developer/DBA) makes a change outside the application. I've never been anywhere where this does not happen at least occasionally.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 27, 2008 at 4:29 am
Grant & Jack,
Thanks for your reply. DRI is surely important.
10 to 15 ms for the referential integrity check would not be an issue. Will it be a concern/ suspected to change if the number of concurrent hits to the table (inserts/updates) is huge or the number of records in all the relating tables are large? Recent statistics shows around 2000 tickets/ hour raised in a peak period.
Thanks,
Nags
May 27, 2008 at 6:29 am
It is possible for the RI checks to be performance hit, sure. But if you've set up your tables & indexes appropriately, they shouldn't be. Doing an RI read on an insert should be the least costly part of the insert because generally, depending on tons of factors, the system, etc., it will be going against the cache. The write part of the insert, must go to disk. That remains the most costly part of the process. Don't sacrifice integrity for performance unless you have no choice.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 28, 2008 at 7:36 am
We are planning to check the referential integrity in the code.
Per the above statement, you're going to be doing the same work as the DRI. It could also be more costly depending upon where you intend to do the checking. If outside of the database (middle tier or client), then you're going to be incurring additional trips into and out of the database to get the data to do the checking.
I'd keep the DRI checks. Even if you "trust" the code to "do the right thing" and not put bad data into your database, you'll be thankful down the road when some new developer gets into the code and makes a change.
I always put DRI in all of the databases that I've designed. It has saved my a** more times than I can remember.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply