Referential Integrity and Performance

There are three ways to implement Referential Integrity in a database system.  And each method results in different database performance.

Primary and Foreign Keys:
Fastest.  Most structured.  Declarative.  No coding required.

in between.

Application code:
Slowest.  Least Structured.  Procedural.  The most coding required.   If the code is found in applications outside of Oracle, network traffic gets involved and is even slower.

No trigger, PLSQL, C, C++, or java code will ever verify referential integrity faster than built in primary and foreign keys.

One Reason:  to move from SQL, to PLSQL, and back, there are two switches that must occur.  A SQL to PLSQL switch.  And back again. These switches add an overhead that the built in SQL does not have.

You also have to add statements for error trapping, such as

I haven’t found these concepts written in many places. My feeling is that they should be one of the first things taught about referential integrity, and a key question on the final if there is one.


Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: