picture by shinichi via Flickr
I often work with programmers to build applications. It never ceases to amaze me that many programmers look at SQL as a store and retrieve mechanism and see nothing more. Their approach is to write programming code for all their logic. It’s not just a matter of programmers being unable to write complex SQL. It’s also a matter of them not imagining what is possible.
It is important to balance the roles of the DBMS and programming. There are several benefits of off-loading logic to SQL.
- Less development effort. SQL is at least 10 times faster to write than programming code. One reason is that SQL is much more concise.
- Fewer bugs. Declarative code (SQL) is easier to debug than imperative code (programming). With SQL it’s easier to think of all the odd cases that could break logic and test them. Also the database products that implement SQL are nearly error free. These products have quality as good as any software that I’ve ever seen.
- Greater understandability. SQL is easier to understand than programming code, especially if the SQL is well written. The fact that SQL is concise helps its understandability.
- Faster performance. DBMSs have robust query optimizers that strive to choose the best algorithms for accessing data. With programming you have to devise your own algorithms and they’re likely to be less effective.
There are several ways that you can offload functionality to SQL. Most DBMSs have preprocessors that let you interleave SQL with programming. Another approach is to encapsulate database logic as stored procedures. Programming code can treat stored procedures like any other invocation and be isolated from SQL details. Referential integrity actions provide yet another means for tapping SQL capability.
We’ve offloaded logic to SQL for many of our projects. Here are some examples.
- For a large medical data warehouse project, we wrote SQL to compute readmissions per the latest CMS guidelines. The calculations are entirely in SQL. The SQL took much less time to write than legacy programming code and is easier to revise. More importantly, SQL yields correct answers in contrast to the legacy code.
- For another project we had to convert a bill-of-material from one format (an indented list) to another (multiple parent-child pairings). This would have been tedious to program. A natural implementation would have involved recursion for the various levels. With SQL we assumed a fixed number of levels and were able to write the SQL in a day.
- Instead of storing an object per record, soft-coding stores an object with multiple records, one for each value. A colleague implemented soft-coding for a mechanical parts business. They support a wide variety of parts with different attributes for each kind of part. Their soft-coded software is highly flexible and the performance is fast. His company has built their software architecture around soft-coding.
A database layer illustrates both proper and improper use of SQL. A database layer hides the database with in-memory data structures that mediate read and write. A CAD application is a proper usage. It reads all data into memory, lets the user manipulate the data, and then writes the data upon an explicit save.
Transaction applications are often an improper usage. With a layer it’s cumbersome to effect fine-grained transactions such as a posting to an account. Also it is difficult to manage multi-user contention. Stored procedures offer a better approach for handling database access for transactional applications.