Analyzing Statement Execution
Once you create indexes, make sure that Derby is using them. In
addition, you might also want to find out the join order Derby is
choosing and force a better order if necessary.
Here is a general plan of attack for analyzing your application's SQL
statements:
- Collect your application's most frequently used SQL statements and
transactions into a single test.
-
Create a benchmark test suite against which to run the sample queries.
The first thing the test suite should do is checkpoint data (force Derby
to flush data to disk). You can do that with the following JDBC
code:
CallableStatement cs = conn.prepareCall
("CALL SYSCS_UTIL.SYSCS_CHECKPOINT_DATABASE()");
cs.execute();
cs.close();
- 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, Derby returns uncached data. If
you run the same query immediately afterward, Derby is probably returning
cached data. The performance of these two otherwise identical
statements varies significantly and skews results.
- Use RunTimeStatistics to identify tables that are scanned
excessively. Are the appropriate indexes being used to satisfy the
query? Is Derby choosing the best join order? Force the best order, if
appropriate. See Working with RunTimeStatistics for instructions.
- Make a change, then retest.
- 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.
[ Previous Page
Next Page
Table of Contents
Index ]