< Previous | Next >

Lesson 5: Measuring the performance of the SQL statements in the Inventory_LevelsData interface

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:

  1. Create and run a pureQuery run configuration:
    1. In the Package Explorer, right-click the project folder and select Run As > Run Configurations.
    2. In the Run Configurations window that opens, double-click pureQuery in the tree on the left to create a new run configuration.
    3. 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.
    4. 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.
    5. Click the Apply button.
    6. 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
      Entering the GSDB connection information
    7. Click Apply and then click Run.
    By default, Optim™ Development Studio opens the Console view to display the results of the SQL statements.
  2. 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: The EXPLAIN or Show Performance 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 and select pureQuery > Refresh Project in SQL Outline before the performance data appears.

  3. In the toolbar for the SQL Outline view, select Workbench SQL performance from the Performance Data Type list.
  4. 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 Show tree or table view button).

    The hierarchy in the Database page of the view switches to a table.

  5. 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.
  6. 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:
    1. Click the Save Current Performance Data button.
      The location of the Save Current Performance button
    2. In the Save Performance Data window, name the data set.
      The Save Performance Data window
      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.
< Previous | Next >

Feedback