Skip navigation.
Downloads | Product Documentation | Support

Data Services Developer's Guide

# Testing Query Functions and Viewing Query Plans

You can use Test View to execute any data service read or relationship function for which data is available.

When you run a query in Test View results appear in an editable window in text or structured XML form. When updates are available for your data, you can immediately update your back-end data. Query results can also be used as complex parameters for other queries.

In creating support for query functions, Liquid Data determines Test View options from your query function's signature. Three types of query function signatures are supported:

- Queries without parameters
- Queries with a simple parameter type
- Queries with a complex parameter type

Query Plan View is also described in this chapter.

The following major topics are covered in this chapter:

- Running Queries Using Test View
- Analyzing Queries Using Plan View

# Running Queries Using Test View

In Test View you can select any of the read or navigation functions defined in your data service from a drop-down list.

Figure 7-1 Test View Options for a Function Accepting a Simple Parameter

If the query accepts complex parameters, the parameter entry dialog automatically adjusts, as shown in Figure 7-2.

Figure 7-2 Function Accepting a Complex Parameter Using Template Option for Input

## Using Test View

To use Test View, follow these steps:

1. Select the Test View tab, then chose a function from the pulldown menu. The menu contains all the read and navigation functions in your current data service.
2. Enter function parameters, if any.
3. Click on Execute to run the query and view the results.
4. If you have back-end data write permission, you can make changes in your data as well. Click on Edit Results and make any necessary changes. Then click Submit to update your data.

You can review instructions generated by your query in the Output window. See Reviewing Query Performance for details.

### Running a Query With No Parameters

In the case of a query such as getAllCustomers(), no parameters are needed (Figure 7-3).

Figure 7-3 Query Without Parameters

When you click Execute the query will run.

Results are returned in text or XML form. Click on the + next to a complex element (in this case, a table representation) to see more detailed results.

#### Editing Results

When you have appropriate update permissions as is commonly the case with "sand box" testing you can directly edit results. For example, you could change a customer ID.

Figure 7-4 Editing query results

When you are satisfied with the changes click Submit.

### Running a Function With Simple Parameters

When your query requires one or multiple simple parameters, Test View display each parameter in its own field, identified by name and type. The query function will be listed with a parameter as in:

>     getCust(CustNum, CustName)

Figure 7-5 Function with Two Input Parameters in Test View

See Running a Query With No Parameters on page 7-4 for details or executing a query and editing and submitting results.

### Running a Query Requiring a Complex Parameter

Enterprise-scale queries often require a complex parameter type as input. For example, an inventory query may require a set of parameters which are based on a web service supplying a list of orders received. It is easier to just pass the entire object than to specify a large set of individual parameters.

When your query requires a complex parameter, the query function will be listed with a parameter as in:

>     getProfileView(arg)

The arg parameter indicates that a complex parameter type is needed.

For such parameters Test View displays a box (Figure 7-2) into which you can:

- Paste the results of the most recently run query (assuming the results are relevant)
- Paste a template of the complex parameter into which you can enter necessary values
- Browse to an XML document to serve as input
- Enter your complex parameter directly

#### Using Prior Results as Input

In any given data service you can use results from a previously run query as input. This is particularly useful when invoking navigation functions.

Note: When pasting prior results it's important to keep in mind that queries returning multiple results (arrays) cannot be input to functions looking for a single object as a parameter. For example, a function that gets orders for a particular customer is likely to return multiple orders. Those results cannot be used for a function that returns information about a particular customer.

The following steps show how results of a singleton query can be repurposed as input for a complex parameter.

1. Assume that you have first run a simple query, selecting information on a particular order. Then you want to get additional information on the customer who placed the order. Results shown below contain elements called for by the function getElecOrderByOrdID(ORDER_ID), located in the RTLServices/ElecOrder data service.

Figure 7-6 Executing a simple parameterized query

2. Your results now contain the required customer ID. Select the getCustomer() relationship function from the dropdown list of available functions.
3. Click on the Paste Result button. Your previous results appear as an editable complex parameter in XML format (Figure 7-7).

Figure 7-7 Using Query Results in a New Query

4. Click Execute. Results of your new query based on the Customer XML type appear (Figure 7-8).

Figure 7-8 Complex Parameterized Query Results

#### Using an Input Parameter's Template to Guide Input

You can also automatically enter a template of the XML type of your data service.

Figure 7-9 Using XML Type Template to Guide Data Input

Template parameters are ideal when you know the key parameters required by your query.

## Limiting Array Results

You can filter query results through Test View to n instances of a single element. For example, the first five customers from an array of 5,000.

### An Example

Figure 7-10 shows a function where the results for ELEC_ORDER/ELEC_LINE_ITEM/ORDER_ID are limited to 3.

Figure 7-10 Limiting Elements in an Array Result

## Starting Client Transaction Option

The Client Transaction Option supports functions that query more than multiple (two or more) relational sources using XA transaction drivers. By default this option is not selected, meaning that the NotSupported EJB transaction method is used. If the option is checked, the Required transaction mode will be used instead.

For general information on the subject see Transactions in EJB Applications BEA WebLogic Server documentation.

## Disregarding a Running Query

An executing query can only be ended through the Liquid Data Administration Console or by ending your server process. However, you can start a new query by changing your selection in Test View. Performance may be affected.

## Reviewing Query Performance

When a query function is invoked through Test View performance information appears in the WebLogic Workshop Output window (View —> Windows —> Output). The most recent query results are reported at the bottom of the Output pane.

Information includes:

- Compilation time
- Execution time
- Retrieval time and number of invocations for each data source
- Query statement

Figure 7-11 Function Query Output Windows Results

# Analyzing Queries Using Plan View

Two types of information are available to help you analyze the design and performance of your query.

- Performance Information appearing in the Results window
- Query Plan

Query Plan View is designed to help you understand how a query is designed.

The returned plan identifies the following query components:

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

Figure 7-12 Query Plan Fragment for RTLApp's Customer Data Service getCustomerByCustID(cust_id) Function

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

- Tree view. This is a collapsible graphical presentation of the query plan.
- XML view. Provides a collapsible XML document view of the query plan.
- Text view. Presents the information as text.

## Analyzing a Sample Query

The following query is from the Liquid Data RTLApp (RTLServices/OrderDetailView/getElecOrderDetailView(order_id, customer_id)

From the function signature you know that the query returns data related to order details after it is passed an order ID and a customer ID.

The query can be described with the following psuedocode:

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

A compressed version of the query plan is shown in Figure 7-13.

Figure 7-13 Query Plan for getElecOrderDetailView()

The let 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, as shown in Figure 7-14.

Figure 7-14 Top Down Schematic of getElecOrderDetailView() Function

## Working With Your Query Plan

Two options are available in the 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.

## Reviewing Query Performance

See Reviewing Query Performance.