MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Why Stored Programs?

with 2 comments

Why should you use stored programs? Great question, here’s my little insight into a situation that I heard about in a large organization.

A very large organization is having a technology argument. In someway, like politics, half-truth drives this type of discussion. This company has hundreds of databases and they’re about half SQL Server and Oracle. The argument (half-truth) states that using T-SQL or PL/SQL yields “spaghetti” code!

It seems like an old argument from my perspective. After all, I’ve been working with T-SQL and PL/SQL for a long time. Spaghetti code exists in every language when unskilled programmers solve problems but the point here is one of software architecture, and an attempt to malign stored programming in general. Let’s examine the merit of the argument against stored programs.

First of all, the argument against stored programs is simply not true. SQL DML statements, like the INSERT, UPDATE, and DELETE statements should maintain ACID compliant interactions with a single table in a database. Unfortunately, the same statements create anomalies (errors) in a poorly designed database.

Stored programs provide the ability to perform ACID compliant interactions across a series of tables in a database. They may also hide database design errors and protect the data from corruption. The same can’t be said for Java or C# developers. Java and C# developers frequently fail to see database design errors or they overlook them as inconsequential. This type of behavior results in corrupt data.

It typically raises cost, errors, and overall application complexity when key logic migrates outside the database. If you’re asking why, that’s great. Here are my thoughts on why:

  1. Making a Java or C# programmer responsible for managing the transaction scope across multiple tables in a database is not trivial. It requires a Java programmer that truly has mastered SQL. As a rule, it means a programmer writes many more lines of logic in their code because they don’t understand how to use SQL. It often eliminates joins from being performed in the database where they would considerably outperform external language operations.
  2. Identifying bottlenecks and poor usage of data becomes much more complex for DBAs because small queries that avoid joins don’t appear problematic inside the database. DBAs don’t look at the execution or scope of transactions running outside of the database and you generally are left with anecdotal customer complaints about the inefficiency of the application. Therefore, you have diminished accountability.
  3. Developing a library of stored procedures (and functions) ensures the integrity of transaction management. It also provides a series of published interfaces to developers writing the application logic. The published interface provides a modular interface, and lets developers focus on delivering quality applications without worrying about the database design. It lowers costs and increases quality by focusing developers on their strengths rather than trying to make them generalists. That having been said, it should never mask a poorly designed database!
  4. Service level agreements are critical metrics in any organization because they compel efficiency. If you mix the logic of the database and the application layer together, you can’t hold the development team responsible for the interface or batch processing metrics because they’ll always “blame” the database. Likewise, you can’t hold the database team responsible for performance when their metrics will only show trivial DML statement processing. Moreover, the DBA team will always show you that it’s not their fault because they’ve got metrics!
  5. Removing transaction controls from the database server generally means you increase the analysis and design costs. That’s because few developers have deep understanding of a non-database programming language and the database. Likewise, input from DBAs is marginalized because the solution that makes sense is disallowed by design fiat. Systems designed in this type of disparate way often evolve into extremely awkward application models.

Interestingly, the effective use of T-SQL or PL/SQL often identifies, isolates, and manages issues in poorly designed database models. That’s because they focus on the integrity of transactions across tables and leverage native database features. They also act like CSS files, effectively avoiding the use of inline style or embedded SQL and transaction control statements.

Let’s face this fact; any person who writes something like “spaghetti” code in the original context is poorly informed. They’re typically trying to sidestep blame for an existing bad application design or drive a change of platform without cost justification.

My take on this argument is two fold. Technologists in the organization may want to dump what they have and play with something else; or business and IT management may want to sidestep the wrath of angry users by blaming their failure on technology instead of how they didn’t design, manage, or deliver it.

Oh, wait … isn’t that last paragraph the reason for the existence of pre-package software? ;-) Don’t hesitate to chime in, after all it’s just my off-the-cuff opinion.

Written by maclochlainn

October 6th, 2012 at 3:48 pm

2 Responses to 'Why Stored Programs?'

Subscribe to comments with RSS or TrackBack to 'Why Stored Programs?'.

  1. While I think your point is valid, it is important to note that the nature of stored programming is that the procedures need to be “melded” into the database at some point. This surely presents some deployment and versioning challenges that aren’t as present in “external” programs.

    Another consideration is that database procedures are an innovation of limitation. When you layer programming logic (conditional evaluation, iteration, etc) not “on top of (external)” but “into (directly installed on)” a database, I think the spaghetti code argument will always be valid to some engineers. Many engineers will consider melding of responsibilities spaghetti-ish, regardless of the performance gain.

    At any rate, I think your perspective is valid: “Spaghetti code exists… when unskilled programmers solve problems”. That said, in my opinion it’s a constant game of trade-off. The cost of external computation and degraded DBA analysis may or may not be worth the inherent organizational overhead of using stored programs.

    Ultimately, it’s a decision that should be weighed carefully.

    Great post!

    Tyler

    19 Oct 12 at 2:00 pm

  2. Tyler,

    The idea of “inherent organizational overhead of using stored programs” is an illusion. Failure to put logic in stored programs places that logic as overhead inside code modules. The maintenance of the embedded transactional control code inside modules is generally much higher than the maintenance cost of stored programs.

    In the case of Oracle stored programs, they run as compiled program units. There performance significantly outperforms Java or other interpreted languages.

    maclochlainn

    21 Oct 12 at 11:56 pm

Leave a Reply