Have you ever tried to analyze a complicated sql, but there were so many clauses or inline views, that you hardly knew where to start? Well, I've been saddled with that type of code many times. Fortunately, there is a better alternative. It's called "Query Subfactoring," and it's a great way to make code more manageable.

The "With" Syntax

Some folks call this the "With" syntax. Using this feature makes your code much easier to follow, and much easier to debug--both functionality-wise and performance-wise. Use of a lot of inline-views can make the code nearly impossible to understand.

Here's what it looks like--note that each "view" is enclosed within parentheses:

With View1 as (Select * from Table1 Where Col1 = 'ABC'),

View2 as (Select * from Table2 Where Col1 = 'DEF')

Select Count(*) from View1, View2 Where View1.Col1 = View2.Col1;

In the example above, I create just two "views," but you can do as many as you want. Using this approach, you will find it much easier to tune complicate sql. When this format, you can simply tune each part, piece by piece. It's as though you were creating separate tables.

So, for the above example, I would get the "View1" running smoothly, then "View2." When all the preliminary parts are running well, you can then concentrate on tuning the main body. Simple!