Friday, October 15, 2010

Hammering A Stored Procedures vs Dynamic SQL Debate

While looking for differing viewpoints on stored procedures vs dynamic SQL, I discovered an article by Frans Bouma from a few years ago, wherein he passionately criticizes elements of a post by Rob Howard.

At the time of Bouma’s post I’d had some experience with Microsoft SQL 2000, but I was at a stage where I was doing the SQL equivalent of meatball surgery on databases rather than design. My tasks mainly involved adapting existing code to suit new or revised business logic. As a result, I can relate to aspects of where each person is coming from.

Bouma and Howard both make strong cases for their arguments. Howard, clearly in favor of stored procedures, maintains that a balanced approach should be taken to designing the data access layer to optimize data interchange, and cites stored procedures as an effective tool to abstract the data and improve efficiency and performance. Bouma, a dynamic SQL convert, strongly advocates that paradigm, favoring SQL code emitted by high-level objects to creating discrete stored procedures for specific data management tasks on the fly. He also cites some deep technical details specific to Microsoft SQL which he uses to question some of Howard’s suggested implementations of stored procedures.

At face value, each person argues strongly for their viewpoint, but whether stored procedures or dynamic SQL are in and of themselves superior depends on the design and implementation of whatever system will be hitting the database. As I read each person’s arguments I got the distinct impression that each is somewhat mired in the advantages of their preferred paradigm.

The gist of this argument from years ago can be summed up in each author’s words. Howard states, “Embedded SQL script is very brittle; small changes to the database can have severe impacts on the application.” Bouma says in response to one of the comments in his blog post, “True, it requires some work to get a dynamic query generator right, but once you have one, you don't have to recode it again.”

Both these statements ring true… under a given set of circumstances. If I need code to grab values from a static lookup table, which do I use? Probably either, since unless something major changes in the design, I won’t need to touch the query frequently to modify it. However, what if I need to do something more exotic, like process a payment or mine some data based on input variables that might differ from one query to the next? Again, either methodology could be used for these purposes. It depends I think on how effectively the tools are built to deal with the data, and whether they meet the needs of the design.

Quite frankly, it seemed to me that the authors were each getting too wrapped up in their respective favorites, and arguably from two extremes of the IT spectrum. Howard’s argument seemed somewhat less technical and more geared towards an executive’s ear, with buzzwords about efficiency and performance, whereas Bouma’s deep technical knowledge describes a quintessent engineer, quite eager to implement some project with the tools he is comfortable and happy with.

A hammer is great for driving nails into wood, and for yanking nails out of wood with its secondary nail-removing end. It’s streamlined and clearly purposed for its intended use. Would it be appropriate to use a hammer to spackle a hole in the wall, or break pieces of tile into neat square shapes?

Sure, you might manage to adapt the hammer to the task, using some duct tape to secure a credit card or other stiff piece of whatever to more effectively spackle, or try to direct the blows of the hammer to snap the tile into shape without shattering it.

Would it be efficient? Absolutely not.


No comments: