Section 2.8. Action-Packed SQL Statements

2.8. Action-Packed SQL Statements

SQL is not a procedural language. Although procedural logic can be applied to SQL, such approaches should be used with caution. The confusion between procedural and declarative processing is most frequently seen when data is required to be extracted from the database, processed, and then re-inserted back into the database. When a programor a function within a programis provided with some input value, it is all too common to see that input value used to retrieve one or several other values from the database, followed by a loop or some conditional logic (usually if...then...else) being applied to yet other statements applied to the database. In most cases, this behavior is the result of deeply ingrained bad habits or a poor knowledge of SQL, combined with a slavish obsession with functional specifications. Many relatively complex operations can be accomplished in a single SQL statement. If the user provides some value, try to get the result set that is of interest without decomposing the process into multiple statements fetching intermediate results of only minimal relevance to the final output.

There are two main reasons for shunning procedural logic in SQL:

Any access to the database means crossing quite a number of software layers, some of which may include network accesses.

Even when no network is involved, there will be interprocess communications; more accesses mean more function calls, more bandwidth, and more time waiting for the answer. As soon as those calls are repeated a fair number of times, the impact on process performance can become distinctly perceptible.

Procedural means that performance and future maintenance burdens fall to your program.

Most database systems incorporate sophisticated algorithms for executing operations such as joins, and for transforming queries so as to execute them in a more efficient way. Cost-based optimizers (CBOs) are complex pieces of software that have sometimes grown from being totally unusable when originally introduced to becoming mature products, capable of giving excellent results in most cases. A good CBO can be extremely efficient in choosing the most suitable execution plan. However, the scope of operation of the CBO is the SQL statement, nothing more. By doing as much as possible in a single statement, you shift the burden of achieving the best possible performance from your program to the DBMS kernel. You enable your program to take advantage of any improvement to the DBMS code, and therefore you are indirectly shifting a large part of the future maintenance of your program to the DBMS vendor.

As ever, there will be exceptions to the general rule that you should shun procedural logic, where in some cases procedural logic may indeed help make things faster. The monstrous all-singing-and-dancing SQL statement is not always a model for efficiency. However, the procedural logic that glues together successive statements that work on the same data and hit the same rows can often be pushed into one SQL statement. The CBO can consider a single statement that stays close to the sound rules of the relational model as a whole and can execute it in the most efficient way.

Leave as much as you possibly can to the database optimizer to sort out.