Av rating:
Total votes: 53
Total comments: 8

 send to a friend
 printer friendly version

Attack of the barber-surgeons of IT: A tale of cursed cursors & embedded SQL

“In the days when I contentedly cut procedural code, I was puzzled by the visceral rages of the database specialists; they were like the ship’s cook occasionally going berserk with a meat-cleaver. After I switched to database work, I realized why.”

The development foot soldiers in the IT army are required to have two arrows in their quiver: fluency with the procedural language currently in fashion and a measure of skill in database programming.

Few people are proficient in both. It reminds me of the medieval barbers who cheerfully cut off limbs and performed other surgery as a sideline. If they could cut hair, they reasoned, why not cut the body as well?

The two programming skills, like surgery and cutting hair, require different mindsets. It is not enough to parrot the manual. You have to think in a particular way.

To cut good procedural code, a programmer must envision himself in an alternative universe of objects and automata, performing every action in a way that banishes uncertainty. The database developer, in contrast, provides a clear definition of what the results should be, leaving the detail to the SQL engine. He must live in a world of set logic and intrinsic parallelism, where business processes are redefined in terms of set-based operations.

All developers, and most IT people, adopt an extraordinarily logical way of thinking. This is why there is a communication barrier between business and technology in most commercial enterprises.

Logic, ledgers, consequences, certainty, procedures, regulations and planning are the hallmarks of the technologist, while the businessman lives in a world that values intuition, risk and inspiration. When the two meet, there is often hostility, frustration and confusion. The business side mutters that its IT staff has been beamed down by hostile aliens as an act of vengeance, and the IT staff twitters about the irresponsible lack of thought on the part of its business leaders.

The logic of sets

In addition to his general mindset, a good database programmer sees business tasks in the logic of sets. There is a tremendous satisfaction in working out what business entities exist in a company and the rules that govern them, so as to get into the group mindset and impose language and harmony on a booming, buzzing confusion.

The first stumbling block for the non-specialist programmer who tries his hand at databases is that the work seems so easy, at least initially. After the first name-and-address Access database, there is the temptation to slip SQL onto one’s resume as a current skill, which is rather like describing oneself as a pianist after learning “Chopsticks.”

It is the experienced database designer who holds his head in his hands when confronted with the provisional architecture of a new development, groaning at the implications of what is being asked. For the barber-surgeons of the IT industry, it is just a matter of holding the saw in the correct hand, working fast, and mopping up later.

When the freelance programmer has unfettered access to a database, certain things are liable to happen that will almost certainly spell big trouble for a project. I’m talking about cursors and “embedded” SQL.

The curse of cursors

Cursors were unwisely introduced into SQL Server as a thumb-and-blanket concession to the numbers of dBase II programmers being weaned onto the new product. It enabled them to port their code from a database in which data had an intrinsic order and sequence, to a relational database in which it didn’t.

Its presence in SQL Server is incongruous and unnecessary. I often enjoy pulling out so-called cursor code by the handful for the instant effect of speeding up the database. A programmer who thinks that cursors are necessary is thinking procedurally. In my 15 years of programming SQL Server, I’ve never come across a database operation that requires a cursor.

A peculiar construct

By embedded SQL, I refer to the frightful habit of constructing SQL queries in strings within procedural code and executing them against a database. This is a very bad idea for a number of reasons:

• It means you have to give table-access permissions to users and then deal with the resulting security risks.
• If you change the database model in any way, you have to rewrite the procedural code that relies on the existence of the previous model.
• It runs more slowly because the database has to compile the entire strategy whenever it executes the code.
• You can’t fix deadlocks, poor optimization and so on without going through acres of procedural code, and doing recompilations.
• The procedural programmer has to know how to write good optimal SQL queries that don’t cause unnecessary table locking or deadlocks.
• The database has to be ringed with a defensive battery of constraints and triggers to prevent the worst foolishness from happening.

All access to the database must be by stored procedure. It is such an obvious thing to do that it always gives me a jolt to see embedded SQL. There is even an object interface beloved by java aficionados that will take a java object and save it in the database by constructing a chopped salad of insert and update statements and throwing them at the database, or stock such an object with data by conjuring yards of select statements. This is enough to make any DBA shudder in his sleep.

Many features that we accept as part of a relational database were put there to defend the integrity of the database against the ham-fisted endeavors of the freelance programmer. Checks on foreign keys and uniqueness are excellent in development but are scarcely necessary if creating, updating and deleting are done through well-tested stored procedures. And, they will certainly slow data throughput in a hard-working production system.

If the freelance programmer can be kept outside the gates and all database access is via stored procedures, then a development project is more likely to have a favorable outcome.

Fear, loathing and blame

It is a curious fact that the DBA or database developer is the worst tempered of the development team. He is also loathed and feared within the business. In the days when I contentedly cut procedural code, I was puzzled by the visceral rages of the database specialists; they were like the ship’s cook occasionally going berserk with a meat-cleaver. After I switched to database work, I realized why.

There is no one more persistent and confident in his opinions of how databases should be run than a programmer with a smattering of database knowledge. His beliefs often have a religious zeal that is impervious to logic or practical proof. If he gets his way, a regime of “who dares wins” is then imposed on the design of the database. The database specialist is reduced to plugging leaks, mopping up and ameliorating the worst excesses of procedural thinking.

When the database fails, however, it is the DBA or developer who is dragged in front of management for the ritual tongue-lashing. After all, it is his database, even if he has no knowledge of the offending code that has frozen the terminals of hundreds of users, or wiped out the business’s audit trail.

By the time the consequences of the decision to let the freelance programmer roam unfettered over a database have been felt, the original arguments have faded from memory and the developer is forced to quietly take the blame.