Tuning Databases and Applications
Page 3 of 5

Analyzing Statement Execution

Once you create indexes, make sure that Cloudscape is using them. In addition, you may also want to find out the join order Cloudscape is choosing and force a better order if necessary.

Here is a general plan of attack for analyzing your application's SQL statements:

  1. Collect your application's most frequently used SQL statements and transactions into a single test.
  2. Create a benchmark test suite against which to run the sample queries. The first thing the test suite should do is checkpoint data (force Cloudscape to flush data to disk). You can do that with the following SQL-J statement:

    CALL Factory.getDatabaseOfConnection().checkpoint()

  3. Use performance timings to identify poorly performing queries. Try to distinguish between cached and uncached data. Focus on measuring operations on uncached data (data not already in memory). For example, the first time you run a query, Cloudscape returns uncached data. If you run the same query immediately afterward, Cloudscape is probably returning cached data. The performance of these two otherwise identical statements varies significantly and skews results.
  4. Use RunTimeStatistics to identify tables that are scanned excessively. Are the appropriate indexes being used to satisfy the query? In some rare cases, you may need to force the index on the table. Is Cloudscape choosing the best join order? Force the best order, if appropriate. See Working with RunTimeStatistics for instructions.
  5. Make a change, then retest. (It's an iterative process.)
  6. If changing data access does not create significant improvements, consider other database design changes, such as denormalizing data to reduce the number of joins required. Then review the tips in Application and Database Design Issues.