I've inherited a database which contains several procedures which are 1000-1500 lines long, with complex nested sub-selects going up to 7 or 8 levels deep in places. I desperately need to refactor them for my own sanity, but how can I begin to do this with any level of confidence that they still work the same?

I would write unit tests if this were .Net - do you recommend a similar approach?

  • What database platform is this? – Leigh Riffel Feb 9 '11 at 16:02
  • @Leigh: I was going for more general advice, but it happens to be SQL Server 2005. – goric Feb 9 '11 at 17:57
  • How do you mean "support for them"? You can write unit tests in any language. Here is a tool for T-SQL for example. – Gaius Feb 10 '11 at 9:58
  • @Gaius: Thanks, I wasn't aware of tools for unit testing T-SQL - this is part of the reason I asked the question. I edited it to take out that wording. – goric Feb 10 '11 at 15:52

Yes, create unit tests. This is really the only way to ensure that a modified version meets the same requirements as the original.

To refactor the procedures look for any repeating patterns of code that could be extracted into separate procedures or nested procedures. If the procedures are still too long break parts of it into separate procedures that each complete one task. As you break off new procedures you should add unit tests for it.

Seven or eight levels of sub-selects does sound excessive, but you may find that some or even most of these are necessary to produce the data required. I would focus on the procedure initially and then tackle the SQL.

To do unit testing in Oracle, the foremost commercial product for testing is Quest's Code Tester. Oracle has Unit Testing built into it's free SQL Developer product. The question Unit testing for PL/SQL on StackOverflow has some other options or you can just write your own tests.

| improve this answer | |
  • 1
    Thanks. Does anyone know of similar tools for SQL Server? – goric Feb 9 '11 at 18:10
  • A brief search seems to indicate that the state of procedure testing is as bad for Microsoft as it is for Oracle. Here is the StackOverflow answer to this question. stackoverflow.com/questions/2765212/… – Leigh Riffel Feb 10 '11 at 20:46

maybe you could find good advice in here:

In the meantime, try to identify nasty-looking queries, like the ones that use where instead of joins.

| improve this answer | |

As for my two cents here, I generally run it through a reformatter if I can, to get things to a traceable level, because anything that's 1500 lines unless already reformattered is going to be a little rough to trace by hand.

Then I try and put deeply nested selects into temp tables to reduce the nesting.

Then I try and figure out if it can be split into contiguous chunks of code, that can be extracted to run by themselves (think like making smaller functions out of a single long method of C#) and encapsulated to prevent too much information being presented in one long stretch.

Those are my basics that I toss at the problem before getting too far off topic

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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