Editorials

How Fast Does a Query Have to Be?

In a recent editorial I made the statement that a developer should write SQL Code to the best of their ability. If the performance was acceptable, then they should simply go nuts with their implementation. Tommy Petersen reminded me of the folly this statement can generate. He says…

“If you are getting the performance you want then go nuts” (Tommy is quoting my editorial)

Problem is that many developers that do not know what performance they should want.

The key phrase in his comment is WHAT performance they SHOULD want. SQL Server is so powerful, and has been optimized over decades, producing incredible performance that covers up a world of poor performing queries. When you have one slow query that is not constantly in use, it may often be inconsequential.

Here is the real world scenario that often occurs. Your database hums along, performing rather nicely. It gets incrementally slower. It’s like putting a frog in a pot, and raising the heat until the water is boiling. If you raise the heat slowly enough, the frog won’t jump out. It will stay in the pot until it is cooked. That is an image of what happens when it comes to poor queries. They stack up, and, over time, can bring your system to its knees.

Does this mean that only people who really study SQL should be allowed to write database queries? There are some shops that take that position. Those are the kind of shops that generally reject automated ORM generators, because those have a tendency to write code that is less than optimal.

What is your opinion? How do you assure that you have the best product that is reasonably created, and mitigate for whatever solution you implement? If you restrict who can write database code, how do you assure that you have enough resources to meet your company’s needs? If you extend the writing to less seasoned individuals, how do you mitigate the impact on resources that are limited, such as your SQL Servers?

Share your thoughts in comments, or drop an email to btaylor@sswug.org.

Cheers,

Ben