Q: Why is my SELECT so slow?
Answer: Graphical Plan!
Q: Why is my SELECT sometimes slow and the rest of the time it's fast?
Answer: Compare Plans!
The new Compare Plans tool in SQL Anywhere 17 lets you compare two graphical plans to see all the differences side-by-side... not just structural differences between diagrams, but individual differences between performance statistics like the number of disk reads for a single table.
Tip: You don't have to understand every detail of the graphical plans if you can find inspiration in the differences.
Here's a demonstration using the Tutorial: Comparing Plans in Interactive SQL as a starting point:
1. Start the SQL Anywhere 17 demo database.
Tip: You can compare SQL Anywhere 16 plans but you have to launch the Compare Plans tool via ISQL 17.
2. Flush the cache in ISQL.
CALL sa_flush_cache();
3. Copy and paste this SELECT into ISQL but do not execute it (so the cache will still be empty when the next step captures the plan).
SELECT DISTINCT EmployeeID, GivenName, Surname
FROM GROUPO.SalesOrders WITH (INDEX (SalesOrdersKey)), GROUPO.Employees, GROUPO.SalesOrderItems
WHERE SalesRepresentative = EmployeeID and
SalesOrders.ID = SalesOrderItems.ID and
SalesOrderItems.ProductID = (SELECT ProductID
FROM GROUPO.Products, GROUPO.MarketingInformation
WHERE Name = 'Tee Shirt' AND
Color = 'White' AND
Size = 'Small' AND
MarketingInformation.Description LIKE '%made of recycled water bottles%');
4. Open the Tools - Plan Viewer and capture the plan as FirstPlan17.saplan.
ISQL - Tools - Plan Viewer
Pick Statistics level: Detailed and node statistics
Cursor type: Asensitive
Update status: Read-only
Click on Get Plan
Wait for the plan to appear
Click Save As...
Specify FirstPlan17.saplan
Click Save
5. Switch back to ISQL, then repeat Step 4 to save SecondPlan17.saplan.
6. Switch back to ISQL and open Tools - Compare Plans.
ISQL - Tools - Compare Plans Click on Plan 1: - Browse... Pick FirstPlan17.saplan Click Open Click on Plan 2: - Browse... Pick SecondPlan17.saplan Click Open Click Compare Plans
Here are the side-by-side plans; the diagrams look the same but the statistics are different:
- the red numbers show zero disk reads for the second execution because all the rows were in the cache, and
- the yellow numbers are disk statistics that were only measured for the first plan:
Tip: You can compare plans for two different SELECT statements, but make sure you're not doing that by accident. The tool doesn't show differences between SELECT statements, just the resulting plans.
Here's a map for describing the Compare Plans display:
Plan 1: [filespec1] [Browse...] Plan 2: [filespec2] [Browse...] [<-] [Compare Plans] [->] -------------------------------------------------------------------------------- Comparison Overview Name Name id1 node1 - id1 node1 id2 node2 - id2 node2 -------------------------------------------------------------------------------- 1. filename1 | 2. filename2 Subquery: [SubQ dropdown] | Subquery: [SubQ dropdown] -------------------------------------------------------------------------------- | | Diagram | Diagram | | -------------------------------------------------------------------------------- [Details] [Advanced Details] | [Details] [Advanced Details] ------------------------------| --------------------------- | | Statistics | Statistics | | -------------------------------------------------------------------------------- [Match Operators][Unmatch Operators] [Match Queries][Unmatch Queries]
Plan 1: [filespec1] is for picking the plan to show on the left side, Plan 2: [filespec2] is for the right-hand plan, and [Compare Plans] does the work.
Tip: When you restart the Compare Plans tool after using it before, [filespec1] and [filespec2] will be the same. Be careful what you pick, the tool doesn't care if you compare the same plan with itself.
Tip: When you change [filespec1] or [filespec2], even if you do it via the [Browse...] buttons, don't forget to click [Compare Plans] again.
In other words, don't rely on [filespec1] and [filespec2] for confirmation of exactly what you are looking at. If you forgot to click [Compare Plans], those aren't the plans being displayed.
The [<-] and [->] buttons scroll up and down through Comparison Overview section, highlighting different Name rows and corresponding boxes in the Diagram sections.
Each Name in the Comparison Overview section consists of a generated identifier 1, 2, 3 and a non-unique node title (SELECT, Work, DistH, etc.).
Tip: The Comparison Overview doesn't show the whole query, just the part you selected in the Subquery: [dropdown].
Big complicated SELECT statements can have many entries in the Subquery list. Some of those subqueries can be far more expensive than others, and they can be way down deep in the list.
The two Subquery: [dropdown] lists are synchronized; change one and the other changes, and so do both the Diagram sections.
Each box in the Diagram sections shows one table or other component in the SQL Anywhere query execution plan; for the full list see Execution Plan Components.
Tip: Table names inside rectangles mean table scans, trapezoids mean index scans. Table scans aren't always bad; if SQL Anywhere picks a table scan when an index exists, it's usually because the table scan is faster.
Tip: If a Diagram section shows up empty, click on the second node and then back on the top node.
The Statistics sections, with their Details and Advanced Details tabs, are where all the interesting comparisons are found.
Here's how to find them:
- Step through all the diagrams in the Subquery: [dropdown] list.
- Concentrate on the Actual numbers, not the Estimates.
- Concentrate on the Subtree Statistics sections, not the Node Statistics.
- Start by looking for large RunTime numbers, not PecentTotalCost.
One Last Thing...
If you don't like surprises, don't click on the [Match] and [Unmatch] buttons at the bottom of the screen.
If you want to learn, see Lesson 3: Manually Match and Unmatch Operators and Queries.If you don't care, there's still a reason to click on these buttons, it's to answer this question...
Q: How do I see the original SELECT statements for the two plans?
Answer...
- Click on Subquery: Main Query.
- Click on the topmost SELECT box in the diagrams.
- Click on [Unmatch Operators].
Voila! The original SELECT statements now appear in the Details tabs.
Tip: To reset everything, click on [Compare Plans} again... that gets rid of the effects of clicking on [Match] and [Unmatch].
