Analyzing statement executionStatement executionanalyzingIndex useanalyzingJoin
orderanalyzingApplication performanceanalyzingCheckpoint After you create indexes, make sure that is
using them. In addition, you might also want to find out the join order is choosing.
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 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, returns
uncached data. If you run the same query immediately afterward, 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.
Check that the appropriate indexes are being used to satisfy the query and
that is choosing
the best join order. You can also set derby.language.logQueryPlan to true
to check whether indexes are being used or not. This property will is print
query plans in the derby.log file. See ,
as well as for more
information.Make any necessary changes and then repeat.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 .