Tracking performance

The first step in tracking performance is to determine which indexes (keys) are used for a query. A query’s performance will often vary greatly depending on the keys used to optimize it. If you’ve created well-chosen keys as described in Optimizing with keys, and you’re still experiencing performance problems, the next step is to use Synergy DBMS logging to see what calls the xfODBC driver is making to the Synergy database.

Determining which indexes are used

To find out which indexes are used for a query, use SET OPTION PLAN in conjunction with SET OPTION LOGFILE. This generates a log file that includes a “Pushed key#” line that lists the index used to optimize the query, and it includes a table that lists index information. You can also use information from this log file to get key of reference (KRF) information. See Notes on PLAN.

If you have an optimization issue that you plan to log with Synergy/DE Developer Support, use SET OPTION PLAN and SET OPTION LOGFILE.

Using Synergy DBMS logging

Synergy DBMS logging enables you to log the calls made from the xfODBC driver to an ISAM database. By recording the number of reads to an ISAM file, this log can help you determine if an SQL statement is processed optimally. For example, assume you have a database with the following:

Two tables: order with 8 rows and plants with 121 rows
A valid plant table entry for each order
An index defined for plants.in_itemid

To list all the order numbers and their associated plant names, you could create the following query:

SELECT orders.or_number, plants.in_name
    FROM orders, plants 
    WHERE orders.or_item = plants.in_itemid

You could then use Synergy DBMS logging to find out how many reads result from the query. To do this,

Turn on Synergy DBMS logging. (See Synergy DBMS logging for information.)

Run the query.

Open the resulting log file, find the open() statement for the orders table, and note the file handle identifier. In the following, for example, the file handle identifier is 218580a8:

open(21580a8, I:I, 'XFDBTUT:orders')

Use the file handle identifier to count the number of reads() for the table—e.g.,

reads(21580a8, '', rfa= 00, 100)

Because of the order of the tables in the FROM clause in the above query, there are nine reads() for orders (eight successful and one unsuccessful). xfODBC reads each row in the orders table and then attempts to read each plants.in_itemid that matches orders.or_item. With the plants.in_itemid index, xfODBC is able to position to the first occurrence of plants.in_itemid, so that each orders table reads() reads the plants table only once. If the plants.in_itemid index is not unique (if duplicates are allowed), there will be two or more plants reads() entries for each orders table reads().