Professional SQL Server™ 2005 Programming

23.6. Organizing Your Sprocs Well

I'm not talking from the outside (naming conventions and such are important, but that's not what I'm getting at here) but rather from a "how they operate" standpoint. The next few sections discuss this.

23.6.1. Keeping Transactions Short

Long transactions cannot only cause deadlock situations but also basic blocking (where someone else's process has to wait for yours because you haven't finished with the locks yet). Any time you have a process that is blocked — even if it will eventually be able to continue after the blocking transaction is complete — you are delaying, and therefore hurting the performance of, that blocked procedure. There is nothing that has a more immediate effect on performance than that a process has to simply stop and wait.

23.6.2. Using the Least Restrictive Transaction Isolation Level Possible

The tighter you hold those locks, the more likely that you're going to wind up blocking another process. You need to be sure that you take the number of locks that you really need to ensure data integrity — but try not to take any more than that.

If you need more information on isolation levels, check out transactions and locks in Chapter 12.

23.6.3. Implementing Multiple Solutions if Necessary

An example here is a search query that accepts multiple parameters but doesn't require all of them. It's quite possible to write your sproc so that it just uses one query, regardless of how many parameters were actually supplied — a "one-size-fits-all" ...