Recent site activity

SQL Topology

The following represents the comparison of two explain plans for the same query.
Its tedious and unpleasant to read.


             plan_comparison by you.


The same execution plan comparison is accomplished more simply and pleasantly with "SQL Join Topology" diagrams.


                                             compare_plans1
If we transform the execution plans into join topology diagrams it becomes  easy to see the most important difference. 
The most important difference is the starting table join. 
Joins are made on only 2 tables at a time and the first 2 tables we join is  the most 
important decision for the execution efficiency of the SQL.


We can further expand on these ideas  by adding information about the kinds of joins
  • Merge
  • Hash
  • Nest Loops
and the kinds of table access
  • index
  • full table scan
  • index skip scan
  • full index scan
resulting in a powerful graphic that allows easy identification of the differences and kinds of differences.
                           compare_graphic by you.

             
The idea of "SQL Join Topology" came from "join clouds" which  was first brought to my attention in Dan Tow's book "SQL Tuning."
I think  "SQL Join Topology"  is a marvelous way of showing the most important information in an explain plan.
Join clouds have there limitations when the number of objects in a statement becomes large. 
I'm am researching ways of indication the join topology using other visual metaphors besides join clouds.

I've started using 3 new terms regarding graphical execution plans
     Join diagrams - layout the joins with master tables on top and details on the bottom
     Join clouds -  graphically groupings showing the order of joins in a Join diagram
     Join topology - graphical combination of Join Diagrams and Join Clouds


Balanced Join Diagram

Here is a join diagram example. In order to draw the connections between tables in an SQL query all we need is the from clause shown here:
using the from clause we can lay out nodes fore each table and connection lines to the joining tables. The above diagram might be of some use, but it's limited. On the other hand if we impose order on the diagram we will have a powerful graphic.


Master Detail Join Diagram

Imposing the order of detail tables on top and master details on bottom we can start quickly identifying things in the query.
A master table returns one row when we join into it from a detail.
A detail can return multiple rows when joined into from a master.
The master should have a unique index on the join column(s) so that the optimizer knows the the relationship between the master and detail..
Given this arrangement we  want to join down when possible because this will keep the result set the  same size or smaller. Joining up potentially increases the result set. We want to keep the result set as small as possible for as long as possible.
Beside giving  information about how to join, we see, more importantly, if there are any problems in the schema definition.






For more on Join connectors see http://sites.google.com/site/youvisualize/sql-joins








Comments