How to make stored procedures more readable without harming performance

Question

I come from OOP background, and I need to write and maintain huge stored procedures.

Naturally, the sprocs become quite unreadable very quickly, no matter how much I try formatting, giving readable names and aliases, and so on.

Andrew Novic brings up the option of breaking the procedure into subroutines (rule #1 they teach you in school), but points out that this leads to poor performance.

In my case, performance overrules all other considerations, so it looks like I'm stuck with run-on stored procedures. (ORM is out of the question.)

So... is there any way to make my stored procedures more readable,
or is "damage minimilization" (i.e. nice formatting) my only option?

---

UPDATE - Clarification:

I deliberately left out details, because I reasoned answers would be useful for others as well, and not just my specific case.
However, I'm adding the details to give an idea of what I'm facing.

Here's what makes the SPs unreadable:

• Length (hundreds of lines of code each)
• Dynamic SQL
• Control flow
• Nested queries (sometimes two or even three levels)
• Combinations of all of the above :(

---

UPDATE 2:

I'm using Sql Server 2k5/2k8

Answer 1

As you said, start with easy to read consistent formatting. Here is a general idea of how I like to do it, although it's by no reason the only good way to format.

DECLARE @Test int

IF @Test = 1
BEGIN
    -- Indent within blocks
    PRINT @Test
    SELECT Field1, Field2, -- If the list get's long 
            Field3, Field4 -- split into multiple lines
    FROM Table1
    -- Line up JOIN statement and FROM statement
    JOIN Table2
        -- Indent ON condition with one condition per line
        ON Table1.Field1 = Table2.Field2
        AND Table1.Field2 = Table2.Field2
    WHERE Field1 = @Test
      AND Field2 = 'ABC'
END

On top of that you will notice that I've got comments all over the place. Including in the middle of the SELECT statement and at the end of some lines. Over commenting can make it more difficult to read also so you need to find a happy balance. Another commenting trick is to include "block comments" like this:

/************************************************
********* Title for section 1 *******************
************************************************/

These help you divide up your code into obvious sections. Say "Variable Declarations and Initialization", "Loading Temporary Tables", "Generating Dynamic SQL", etc.

For subqueries I tend to find CTEs and even nested CTEs easier to read although in some cases a mix is best.

SELECT *
FROM (
    SELECT *
    FROM (
            SELECT *
            FROM Table1
        ) Level2
    ) Level1

vs

WITH 
    Level2 AS (
        SELECT *
        FROM Table1 ),
    Level1 AS (
        SELECT *
        FROM Level2)
SELECT *
FROM Level1

And a little SSMS trick while you are viewing your SPs. Pay attention to those little - signs next to the code.

Click on one of them and it will collapse the block below it. Doing this you can get a similar effect to separating things out as stored procedure calls.

Of course your mileage will vary with any of these and in some cases a monster SP is just a monster.

Answer 2

You shouldn't overlook the value of good commenting...

But I wouldn't consider the performance impact of nesting stored procedures too badly. If pushed to the extreme, then perhaps the small overhead of having a stored procedure call another stored procedure could be noticeable, but in general I doubt you'd see a problem.

Answer 3

Unfortunately T-SQL and good coding practices are divorced and no longer talking to each other. Rules like DRY, single definition rule etc are result often in poor runtime performance and, with databases, runtime performance trumps code health every time because the runtime performance is often size-of-data. There are various advice out there on how to tackle this problem. Books like Defensive Database Programming dedicate much to T-SQL coding style and touch much of the issues you are hinting at.

Often a fairly big percent of your code base can be tackled by using code generation techniques. XML + XSLT can generate T-SQL and one XSLT template can generate boiler plate stored procedure for hundreds and thousands of objects, enforcing consistency and propagating changes to every object. It is DRY, but trough code generation. Some prefer perl for the task, or python, or ruby. It matters little what tool you use.

Still some code will be outlier, and usually is the interesting code. Peer code review and a healthy check-in culture can keep the beast in check, but it requires the rarest of the rare resources: developer discipline. If you can promote such culture, by all means do it.

Some vendors promote tools that try to do pre-deployment validation or even post-deployment, and even enforcement, but is only so far that a lexer/parser/checkstyle can go. Code should be easily readable by humans and machines have a hard time figuring our what humans understand and what not...

I'm sorry I don't have silver bullet for you. The adversity of T-SQL toward programming idioms of the day (heck, even '90s programming idioms would be good!) is a painful state of the affairs, but it is how things are.