Referential Integrity and Performance


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

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

2)
Triggers:
in between.

3)
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
IF NO_DATA_FOUND THEN

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.

About these ads

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: