You can use the SQL Outline view to
find out how well your SQL statements perform and quickly find the
statements that need tuning.
To measure the performance of the SQL statements in your Java project:
- Create and run a pureQuery run configuration:
- In the Package Explorer, right-click
the project folder and select .
- In the Run Configurations window
that opens, double-click Data Access Development in
the tree on the left to create a new run configuration.
- In the Name field, type a name
for the configuration. Then, click the Search button
under Main class.
Note: You need
to use pureQuery run configurations only when you want to collect
performance data for the SQL statements in an application. Most of
the time, you can use Java Application
run configurations, as you did when you ran the SampleUtil application.
The Select Main Type window opens.
- Select the class that contains the main() method for
your application. In this tutorial, you will run the test
application InventoryLevelsDataTest. This is a simple, lightweight
application that runs the SELECT statement in the InventoryLevelsData
interface that returns all of the rows in the INVENTORY_LEVELS table.
The application then initializes a bean by using the values from the
first row of the table. The application passes the bean to the methods
that use beans as inputs.
- Click the Apply button.
- In the Program arguments field
on the Arguments page, type the JDBC connection URL, user ID, and
password that are required to connect to the GSDB database. The
following image shows a sample connection to the GSDB database.
Figure 1. An example URL, user ID, and password for a connection to the
GSDB database
- Click Apply and then click Run.
By default, IBM® Data
Studio opens the Console view to display the
results of the SQL statements.
- View the performance data for the SQL statements that ran.
Go to the SQL Outline view. Click the
down arrow on the Show Performance or EXPLAIN Data button:
and select Performance
Data.
Columns that display performance data, with
values in milliseconds, appear to the right of the SQL statements.
You might have to right-click the project folder in the Package Explorer
and select before
the performance data appears.
- In the toolbar for the SQL Outline view, select Workbench
SQL performance from the Performance Data Type list.
- Find the underperforming statements. It is
easy to find the slowest SQL statement when you have a small number
of them. However, if you have a larger number of statements, you can
more easily find the underperforming statements with the table view.
Click the Show tree view or table view button
(
).The hierarchy in
the Database page of the view switches to a table.
- Click the head of the Total Client Time column
twice to see this view of the data with the slowest statements at
the top of the table.
Tip: If you want to edit,
tune, or send the statement to someone else to look at, you can right-click
on any statement. You still have all of the options that you learned
about in the previous lesson.
- Save the performance data. You should save
the performance data before you edit or tune one or more statements.
You can then compare the performance of the old version of those statements
with the new version. To save a set of performance data:
- Click the Save Current Performance Data button.
- In the Save Performance Data window,
name the data set. pureQuery saves the data set, appending a timestamp to
the name that you specified.
In the next lesson, you will compare this set of performance
data with another one.