Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
1  
It would help if you clarify the question by giving the specific qualities that make them "unreadable". It sounds like you are concerned with more than just overall length. –  Jonathan Van Matre Jan 13 at 13:11
    
@JonathanVanMatre Done. –  Jacob Spire Jan 13 at 13:28
    
Novick's article was written in 2005. Not being an SQL Server guy I can only assume that there have been positive changes since then in performance for nested subroutines –  kevinsky Jan 13 at 14:07
1  
You might also want to add which DBMS you are using (Oracle, Postgres, ...). –  a_horse_with_no_name Jan 13 at 15:32
add comment

3 Answers 3

up vote 2 down vote accepted

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.

enter image description here

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.

enter image description here

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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.