If you have identified some SQL statements which are slow or unoptimized you can use the SQL Execution Plan tool in Advantage Data Architect (ARC) to obtain more information about it. Identifying these queries can sometimes be problematic but you can use SQL Logging to help identify many of them. You can also count on your QA department, you have one right, to help find problem queries.
Once you have located those pesky problem queries the fun part begins. Engineers love to solve problems and some queries can be doozies. As entertaining as these can be it sure is nice to have some help with figuring out why the query may be slow. Although Advantage Technical Support is filled with highly qualified people may I suggest using ARC first.
You can get the execution plan for any query by putting the query in the Native SQL Window and clicking on the Show Plan button (highlighted below). This will retrieve the execution plan from the server which will be displayed graphically in ARC. You can also use the SHOW PLAN keywords before your SQL statement to retrieve the execution plan.
The graphical representation of the execution plan will show all of the intermediate steps that the SQL Engine will use to process the query. Keep in mind that the query is not actually executed when retrieving the plan. When the query is executed some additional optimizations may be applied to improve query performance. Any items which are not optimized will be highlighted with a red dot. You can see two of these on the screenshot below.
By hovering over an object (i.e. TABLE SCAN) additional information about the execution step will be displayed in a popup. The popup (example on right) displays; the operation with a description, number of executions, arguments and any warnings. In this case there is a warning on the Join condition not being optimized. The data shown in this popup is also displayed in the Data window which is normally docked at the bottom of the SQL Utility window. Detailed information about each execution step is contained in the data table.
The SQL Execution plan can give you valuable information about your query and help to identify potential problems. It is a quick way to evaluate your query and determine the need for indexes.
No comments:
Post a Comment