Refactoring SQL

Question

Are there any formal techniques for refactoring SQL similar to this list here that is for code?

I am currently working on a massive query for a particular report and I'm sure there's plenty of scope for refactoring here which I'm just stumbling through myself bit by bit.

Answer 1

Not that I've ever found. I've mostly done SQL Server work and the standard techniques are:

• Parameterise hard-coded values that might change (so the query can be cached)
• Review the execution plan, check where the big monsters are and try changing them
• Index tuning wizard (but beware you don't cause chaos elsewhere from any changes you make for this)

If you're still stuck, many reports don't depend on 100% live data - try precalculating portions of the data (or the whole lot) on a schedule such as overnight.

Answer 2

I have never seen an exhaustive list like the sample you provided.

The most effective way to refactor sql that I have seen is to use the with statement. It allows you to break the sql up into manageable parts, which frequently can be tested independently. In addition it can enable the reuse of query results, sometimes by the use of a system temporary table. It is will worth the effort to examine.

Here is a more entertaining example.

Answer 3

Not about techniques as much, but this question might help you find SQL refactoring tools:

Is there a tool for refactoring SQL, a bit like a ReSharper for SQL

Answer 4

There is a book on the subject: "Refactoring Databases". I haven't read it, but it got 4.5/5 stars on Amazon and is co-authored by Scott Ambler, which are both good signs.