A few weeks ago, one of my clients asked me a weird question. The client, who is relatively new to SQL Server, said, “Hey, I just read online that you should never use stored procedures when developing a SQL Server application. The advice was on the home page of a Microsoft MVP, so I figured it would be pretty accurate. What do you think? Should we avoid using stored procedures in the application we’re building?”
The first reaction of the consultant in me was, “Sweet! Without advice like that confusing my clients, I might have to get a real job.” But then, the more compassionate mentor in me rushed to sit my client down and explain the birds and the bees of stored procedures and not believing everything that you read online. Of course, I started the conversation by making it clear that the answer to any question that starts, “What’s the best thing to do” is invariably, “It depends.”
Still, I was intrigued that my customer had said his advice had come from a Microsoft MVP (from the developer community, not a SQL Server MVP) and that he’d seen similar postings on other Microsoft-related development sites. I read some of the articles that my customer was referring to and I was surprised to see that I agreed with many of the insights that the “just say no to procedures” crowd was advocating. However, I disagree with the conclusion of “avoid procedures like the plague” when developing SQL Server applications.
I don’t have enough space to explore the ins and outs of the developers’ arguments and what I believe to be their somewhat faulty conclusions. In fact, the main point of this week’s editorial isn’t to take a firm stand on the use of procedures (off the record, I, of course, use them all the time). Instead, I wanted to make a point about acting on technical advice without understanding the implications of taking the advice.
I’d like to share an anecdote from a project I worked on several years ago, when “agile” development was first becoming popular. It will help me make my point. During the project, I was called in to “fix” a performance problem, which turned out to be related to the agile, object-oriented design of the shopping-cart style application. The developers had created an elegant, encapsulated, easy-to-use-and-maintain development library for managing the shopping-cart. Unfortunately, they took the “object-oriented” idea a bit too far. Every item (e.g., an office chair) had multiple properties (e.g., color, height), and a round trip to the server was required to get the data associated with each property for each item. The main search page of the application would often show as many as 20 items, and each item often had more than 20 properties. So, refreshing a single page during a search often required more than 400 round trips to the server. Needless to say, that didn’t scale well. The basic concepts the developers were trying to design around made sense, but they chose form over function. They created a seemingly elegant application-level architecture with no understanding of what effect those decisions would have on the database tier.
Similarly, the reasonsing behind the blog posts my client was reading are reasonably sound. However it’s dangerous to boil down any set of advice into “This is always the right way,” especially when you don’t fully understand the effect of your absolute rule.
And that’s the primary point I want to make this week. The best initial answer to almost any performance or architecture question is “It depends.” From there, you need to explore the facts and determine what makes sense for a given solution, pulling from your bags of tricks as needed. It’s dangerous to say, “This is always the way to do something,” and it’s dangerous to act on advice unless you understand the implications of that advice. Do you really know the effect of eschewing stored procedure usage for all eternity? Well, until you can make a fully informed and educated decision, I caution you to look before you leap.
