SQL tuning can be a challenge to even the most experienced database professional. So, how can you give yourself a leg up in optimizing a difficult query? As you will discover in this best practice, visualizing the query can help you quickly understand the query, as well as define its optimal execution path. A best practice for visualizing the query is to draw the query out as a Visual SQL Tuning (VST) diagram. A VST diagram consists of the following elements:
Historically, SQL tuning has been limited to the following two approaches:
For example, a trace output might look something like this:e
Not exactly user-friendly. I think I might need a couple of double espressos before I plunge into this one. How about an explain plan? An explain plan is better, but still lacking insightful information
If I was greeted with this first thing in the morning, I'd definitely get a coffee before attempting to operate on it. The explain plan is just a set of directions that the database takes to retrieve the data from the database in order to satisfy an SQL query. An analogy would be if we imagine the SQL query as a google maps request for directions to get from my home in the suburbs to my office downtown. The explain plan is like the turn-by-turn route list. But for any of us who have gotten lost well know, it’s also nice to be able to see the map as well as the turn-by-turn directions. If google maps only gave me the directions without the map I'd be pretty upset, probably lost and totally anxious about how long it's going to take me to get to my destination. Are these a good set of directions? (from where I live to where I work, in San Francisco):  It might help if I had a map:  On the map, the directions look pretty sensible, but what if there was a traffic jam on some streets and not others? How about a map that included traffic jams (i.e. bottlenecks)? Whether the turn-by-turn directions are good or bad requires a map (i.e. visual information). So how do we get a map of for an SQL query? Let's start with the terrain. We can describe the terrain by laying out the tables in the query graphically and drawing connectors for every join, or routes through the diagram. A diagram could look like this:  Now we have a map of the terrain. Seeing the terrain allows us to pick out an optimal explain plan. To pick an optimal explain plan, find tables that have the most selective filter and start the execution there. In the above case there is only one table with a filter. This filter is denoted by the F icon on the table BROKER. Start at BROKER and then look for joins that keep the running row set size to a minimum. The running row set size is the number of rows we carry from the previous node to the next node in the VST. We have to visit all the nodes, but the fewer rows we have to carry with us from one node to the next, the faster we’re going to arrive at our destination. Whether you draw out the VST by hand or use a tool, a best practice is to draw the child table above the parent table. This layout encourages us to navigate the VST join down to keep our running row set the same size vs. joining up which will generally increase the row set size:  Thus, the idea is to start at the most selective filter and join down before joining up. In our example there is only one filter on BROKER, so we start there, we join down to OFFICE_LOCATION, then finish off with CLIENT_TRANSACTION, and end at CLIENT. But what if we have more than one filter and we have to choose? In that case we have to calculate the filter percentage. The filter percentage is simply the number of filter rows divided by total rows in the table. Filter rows are the number of rows returned after applying the filter criteria on that table from the query. In the diagram below, BROKER is the most selective filter at .05% so we start at BROKER and follow the same path as we just discussed:  But what if we don’t have access to any information identifying the parent-child relationships (such as PK/FK constraints, unique indexes or unique constraints)? In those cases, we may be looking at many-to-many relationships. With many-to-many relationships, it's unclear how to navigate the VST. We no longer know whether to join up or down. To solve this impasse, we can add the two table join row sizes to help us navigate. Below, we’ve drawn the two table join sizes on the join lines:  To navigate the VST above, we again start at BROKER with the most selective filter, then look for the smallest two table join size which is OFFICE_LOCATION with a join size of 3. Then we join to CLIENT_TRANSACTION and finally CLIENT. As you can see, VST diagrams act like the map for the terrain that an SQL query has to navigate. When a simple map is not enough, we can add statistics such as filter percentages and two table join sizes to complete the map. VST diagrams can be drawn on paper, with tools such as Visio or with database products that draw them automatically using the SQL query, schema definitions and table statistics. For more perspective on the visual approach to tuning SQL and drawing VST diagrams see SQL Tuning by Dan Tow Refactoring SQL Applications by Stephan Faroult , chapter 5 "Designing Efficient SQL: A Visual Approach" by Jonathan Lewis |
Mr Blue Sky
4 days ago
No comments:
Post a Comment