Analyzing statement executionAfter you create indexes, make sure that
is using them. In
addition, you might also want to find out the join order that
is
choosing.Statement executionanalyzingIndex useanalyzingJoin orderanalyzingApplication performanceanalyzingCheckpoints
Use this 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 to 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 afterwards,
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 the derby.language.logQueryPlan
property to true to check whether indexes are being used or not. This property
prints query plans in the derby.log file. See the
" properties" section of
the for details on this property.
See 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
.