Removing Referential Integrity on Stable Application

Removing Referential Integrity on Stable Application

I have read through some somewhat related questions, but did not find the specifics related to my question.

If I have a stable application that is not going to be changed and it has been thoroughly tested and used in the wild... one might consider removing referential integrity / foreign key constraints in the database schema, with the aim to improve performance.

Without discussing the cons of doing this, does anyone know how much of a performance benefit one might experience? Has anyone done this and experienced noticeable performance benefits?

Answer by Lokesh for Removing Referential Integrity on Stable Application

It will vary from application to application. So "how much" will be a relative term. Performance benefit will come while inserting or deleting records.

So if you have big insertion or deletion operations which are taking time then it might help but i will not suggest you to drop it even if your application is stable because in future development this might lead to big issues.

Answer by Ed Heal for Removing Referential Integrity on Stable Application

The benefits (however small) with be insignificant to the cons.

If performance is a problem check the indexes. Throw more hardware its way. There are a host of techniques to improve performance.

I know you said not to mention the cons - but you should consider them. The data is a very valuable asset and ensuring its validity keeps your business going. If the data becomes invalid you have a huge problem to fix it.

Answer by Thomas W for Removing Referential Integrity on Stable Application

Referential integrity constraints may [in some databases, not SQL Server] automatically create indexes on those FKs; delivering much better performance in queries on those terms.

These indexes often help query performance, giving potentially large boost to efficiency. They also provide additional information to the optimizer, enabling better query plans.

If performance were an issue, there are many other things (caching, prepared stmts, bulk inserts) I would look at before removing referential integrity. But, if you had large numbers of active indexes & were reaching serious limits on insert speed, it might be considered as a last option.

Answer by Jens Schauder for Removing Referential Integrity on Stable Application

From my experience with Oracle:

Foreign Keys provide information to the optimizer ("you're going to find exactly one match on this join"), so removing those might result in (not so) funny things happening to your execution plans.

Foreign Keys do perform checks, which costs performance. I have seen those to use up a big chunk of execution time on batch processing (hours on jobs running for large chunks of a day), causing us to use deferred constraints.

Since dropping foreign keys changes the semantic (think cascade, think the application relying on not being able to remove a master entry which gets referenced by something else, at least in the situation of concurrent access) I would only consider such a step when foreign keys are proven to dominate the performance in this application.

Answer by ta.speot.is for Removing Referential Integrity on Stable Application

one might consider removing referential integrity / foreign key constraints in the database schema, with the aim to improve performance ... [does] anyone know how much of a performance benefit one might experience

You've given us no information about your database schema or how it's used, so I'll be conservative and estimate your performance benefit could be between between ??% (give or take).

Removing foreign keys can improve performance from the point of view that they don't have to be checked.

Removing foreign keys can reduce performance from the point of view that the query plan generation can't trust them and can't take the same shortcuts it would if they were trusted. See Can you trust your constraints? for a SQL Server example.

Foreign keys have more than just performance implications (e.g. ON DELETE CASCADE). So trying to remove them to improve performance without considering exactly what functionality you are removing is na?ve at best.

Answer by srutzky for Removing Referential Integrity on Stable Application

It is not really a fair question in the context of "only speak to the performance gains and not the drawbacks" of this decision (or likely most / all decisions). Since you can't have the pros without the cons you need to know the full extent of both in order to make a truly informed decision. And for this particular question, since there is at best only one benefit (I say "at best" since the performance gain is not as guaranteed as most people would like to believe), then we have little to discuss if we can't talk about the drawbacks (but we can at least start out with the benefit :).

BENEFITS:

• Performance: removing Foreign Keys could get you a performance gain on DML statements (INSERT, UPDATE, and DELETE), but any specifics as to how much is highly dependent on the size of the tables in question, indexes, usage patterns (how often are rows updated and are any of the FK fields updatable; how often are rows inserted and/or deleted), etc. While some questions of best practice can be stated to "nearly always" have a performance gain, any effects on performance related to any change can only be determined through testing. With regards to the typical performance gains related to removing FKs, that is something you are not likely to see until you get into relatively large numbers of rows (as in Millions or more) and bulk operations.

DRAWBACKS:

• Performance: most people would not expect to see that performance could be negatively impacted by removing FKs, but it could very well happen. I am not sure how all RDBMS's work, but the Query Optimizer in Microsoft SQL Server uses the existence of FKs (that are both Enabled and Trusted) to short-cut certain operations between tables. Not having properly defined FKs prohibits the Optimizer from having that added insight, sometimes resulting in slower queries.

• Data Integrity: the primary responsibility of the database is to ensure data integrity. Performance is secondary (even if a very close second). You should never sacrifice the main goal for a lower-priority goal, especially since performance gains can be achieved via other methods, such as: indexes, more/faster CPU, more/faster RAM, etc. Once your data is bad, you might not be able to correct it. With this in mind:

  • Don't ever trust that an application is "stable" or won't change. Unless the software is obsolete and nobody has the source code to make a change, it more than likely will change.
  • Don't trust that you have found all of the bugs in the code yet, no matter how "thoroughly tested" you believe it is. The app might appear stable now, but who is to say that a problem won't be discovered later. If you have more than 10 lines of code in your app, it is doubtful that it is 100% bug free.
  • Even if the app code doesn't change, can you guarantee that no other app code will be written against the DB? If this is software that leaves your control (i.e. is NOT SaaS), can you stop anyone who has installed it from writing their own custom code to add functionality that they want that was not provided in your app? It happens. And even in SaaS companies, other departments might try writing tools against the DB (such as Support who needs to do an operation to help customers). Anyone considering removing FKs is likely to not have set up permissions / security to prevent such a thing.

• Ability to fix / update: The app might be "stable" now, but companies often change direction and decisions. FKs give guidance as to the rules by which the data lives. Even if no data integrity issues are happening now, if there comes a time when the app will have new features added (or bugs fixed), not having the FKs defined will make it more likely that bugs will be introduced due to lack of "documentation" that would have been provided by the FKs.

Answer by Stuart Ainsworth for Removing Referential Integrity on Stable Application

Trying to decide between performance and validity is like choosing which arm you'd rather live without. As others have pointed out, there's better ways to address performance concerns (like index optimization, hardware, query tuning). In any well-designed database system, the performance impacts of reduced referential integrity should be minimal.

Fatal error: Call to a member function getElementsByTagName() on a non-object in D:\XAMPP INSTALLASTION\xampp\htdocs\endunpratama9i\www-stackoverflow-info-proses.php on line 72