Always Optimize SQL Before Other Optimizations
The natural tendency in JDBC programming
is to tweak your Java code. The truth is you will get the most bang
for your buck in SQL optimizations. Small things such as indexing the
columns in your WHERE clauses will do more for
your application performance than anything you can do in Java. A good
way to find out what is happening with your SQL code is to use your
database engine’s command-line utility and run the
SQL through the EXPLAIN SELECT
command.
The information the EXPLAIN
SELECT command provides is database-dependent.
Whatever your database, it should tell you some basic things about
how it is trying to execute your query. Is the query utilizing
indexes fully, or is it doing multiple table scans for what should be
a simple query?