8 Understanding Query Plans

This chapter describes how to obtain and use query plans. This chapter contains the following sections:

Section 8.1, "Using Query Plan View"
Section 8.2, "Analyzing a Sample Query"
Section 8.3, "Working with a Query Plan"

8.1 Using Query Plan View

To obtain a query plan for any function in your data service, select the Query Plan tab and select a function, just as you would in Test View. In addition, as a convenience, you can obtain an ad hoc query plan for XQuery or SQL.

The interface for Query Plan View is quite similar to that used for testing your query functions. You select a function or procedure from a drop down list and then click the Show Query Plan button.

A query plan identifies the following query components:

Joins
Outer join
Select statements
Data sources
Custom function calls
Order-bys
Remove duplicates

There are several ways that a query plan can be viewed:

Tree view. A collapsible graphical presentation of the query plan.
Text view. Presents the information as text.

Figure 8-1 Customer Order Items Query Plan

Description of "Figure 8-1 Customer Order Items Query Plan"

8.1.1 Query Plan Information and Warnings

The query plan shows both informational and warning messages. When a section of the plan is flagged with a warning, the plan segment is highlighted in red. If you mouse over the segment, the warning message appears.

Informational messages also can appear with plan segments. Such segments are highlighted in yellow.

Table 8-1 Informational and Warning Messages Associated With Query Plans

Warning Message Type	Informational Message Type
XQuery compiler: Typematch. Typematch issues will be resolved by the compiler (may affect performance)	Audit. Auditing has been set for this particular function (will affect performance).
XQuery compiler: No where clause. There is no predicate associated with the query function (will affect performance).	Cache. Function is cached (may enhance performance).
XQuery compiler: Untyped data. Possible untyped atomic data found in the node constructor.	SQL pushdown generation details.
XQuery compiler: No such element. The element (name provided) is not found in in-scope schemas.	NA
SQL generation: missing key. Underlying table/view does not have a key.	NA
SQL generation: cannot generate subquery. isSubquery property is set to false on the data service. (See the "Function Annotations" section of the Understanding Data Services Annotations section of the XQuery Developer's Guide.	NA
SQL generation: cannot generate SQL for join expression. Unable to translate join condition.	NA
SQL generation: cannot generate SQL for aggregate expression (named). Function does not operate on a sequence.	NA
SQL generation: fn:string( ) function encountered. Use xs:string( ) instead since xs:string( ) can be pushed down to the database for processing.	NA

8.1.2 Printing or Saving Your Query Plan

There are two right-click options associated with query plans:

Prints the plan
Saves the plan

The default file name for the saved file will appear in the form:

<dataServiceName_qp>

If you right-click on the root element of the plan, Plan A right-mouse option on the root element in the plan allows you to print a query plan to a printer or a file. Right-click on any node in the plan and select either the print or print to a file option.

If you print to a file the filename will be of type XML. The name of the file will be the function name followed by the letters _qp, as in: getCustomerView_qp.xml

The file can be saved anywhere in your application.

8.1.3 Loading a Previously Saved Query Plan

You can load a previously saved query plan using the following steps:

Select
```
Load from file...
```
from the plan drop down box.
In the Browse File dialog locate an existing query plan in the current project.
Click Open.

The selected query plan will be appear.

8.2 Analyzing a Sample Query

Assume a query returns data related to order details after it is passed an order ID and a customer ID.

The following is a "pseudocode" description of the query:

for electronic orders matching CustomerID and OrderID
 return order information and ship-to information
  for credit card information matching an AddressID
   return credit information and bill-to address information
    for electronic line item information matching the line item in the order
      return line item information

The statements represent mappings or projections in the data service. This can be useful when trying to trace performance issues.

The join conditions are identified in the plan as a left-outer join driven by a complex parameter. By definition, joins have left and right sides, each of which can contain additional joins. One of the best uses of the query plan is to see how the query logic works up the various data threads to return results.

8.3 Working with a Query Plan

Two options are available in Query Plan:

Expand All. This right-click menu option expands the currently selected element and any children. If applied to the top-most element in the plan, all elements are expanded.
Match highlighting. When you click on a variable name any elements (open or closed) containing a match for that variable are highlighted. This feature helps you trace variables in the query plan.

8.3.1 Identifying Problematic Conditions Through the Query Plan

When you show a query plan for a particular function, you may notice red or yellow highlighting of particular routines. These correspond to warnings or informational messages from the plan interpreter. For example, if a for statement is missing a where clause (potentially leading to slow performance or retrieval of a massive amount of data) a red warning will appear adjacent to the statement. Simply mouse-over the highlighted section of the plan to view the information or warning.