Optimizing Database Queries Java

Optimizing Database Queries

Optimizing database queries is the first step in addressing the performance problem. It is possible to reduce the query execution time by applying the following simple optimization technique: create an index for each combination of fields involved in the "where" and "order by" clause .

Example:

Consider an e-commerce management system the part of that contains invoicing:

create table PRODUCT (
  ID integer not null,
  NAME varchar(254) not null
)


create table INVOICE (
  ID integer not null,
  NUMBER integer not null,
  PRODUCT_ID integer not null
  constraint INVOICE _FK1 foreign key (PRODUCT_ID) references PRODUCT(ID)
)

The following query finds all invoices with the given product and orders them by the invoice number.

select distinct(INVOICE.*) from INVOICE, PRODUCT
       where PRODUCT.NAME = ? and INVOICE.PRODUCT_ID = PRODUCT.ID
       order by INVOICE.NUMBER

The following indexes may be created up front. Suffix PK stands for primary key, suffix AK stands for unique alternative key, suffix IX stands for non-unique index :

create unique index PRODUCT_PK1 on PRODUCT(ID);
create unique index PRODUCT_AK1 on PRODUCT(NAME);
create index INVOICE_IX1 on INVOICE(PRODUCT_ID);
create index INVOICE_IX2 on INVOICE(NUMBER);

This technique alone will guarantee an improvement of the query performance.

If the query is still slow, the following advanced optimization technique may be applied: create an index for each combination of fields involved in a full scan as determined by examining a query execution plan .

Example:

1. Execute the query in question using a database console tool.
2. Request the tool to provide a query execution plan
3. Find places where the database reports full scans
4. Add an index or indexes on fields involved in the full scans.

Caching Database Queries

Even fully optimized queries may take unacceptably long time. Caching the results of data queries may provide a significant improvement of the application performance, often in orders of magnitude.

A cache is an area of local memory that holds a copy of frequently accessed data that is otherwise expensive to get or compute. Cached data is identified by a key. The algorithm is simple:

1. Get a cached query result
2. If not found, execute a query and put it into the cache
3. Return the result of the query to the requestor

For database queries, the key is a text of the query and a set of parameters passed to a prepared statement:

import java.io.Serializable;
import java.util.Collections;
import java.util.List;

public final class QueryKey implements Serializable {

   private final String queryText;
   private final List queryParameters;

   public QueryKey(final String queryText, final List queryParameters) {
      this.queryText = queryText;
      this.queryParameters = queryParameters;
   }

   public String getQueryText() {
      return queryText;
   }

   public List getQueryParameters() {
      return Collections.unmodifiableList(queryParameters);
   }

   public boolean equals(final Object value) {
      if (this == value) return true;
      if (value == null || getClass() != value.getClass()) return false;
      final QueryKey query = (QueryKey)value;
      if (!queryParameters.equals(query.queryParameters)) return false;
      if (!queryText.equals(query.queryText)) return false;
      return true;
   }


   public int hashCode() {
      int result;
      result = queryText.hashCode();
      result = 29 * result + queryParameters.hashCode();
      return result;
   }
}

The Query result object holds the results of executing the query:

import java.io.Serializable;
import java.util.List;

public final class QueryResult implements Serializable {

   private final int columnCount;
   private final List rows;


   public QueryResult(final int columnCount, final List rows) {
      this.columnCount = columnCount;
      this.rows = rows;
   }

   public int getColumnCount() {
      return columnCount;
   }

   public List getRows() {
      return rows;
   }
}

The following complete Java code shows a class responcible for caching the database queries for Java:

import java.io.IOException;
import java.sql.Connection;
import java.sql.Driver;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import cacheonix.cache.Cache;
import cacheonix.cache.CacheManager;

public final class CachingDatabaseQueryExecutor {

  // Set up the database driver
  static {
     try {
       final Class driverClass = Class.forName("my.database.drive.name");
       final Driver driver = (Driver)driverClass.newInstance();
     } catch (Exception) {
            throw new IllegalStateException(e.toString());
         }
   }
   
   
   public QueryResult execute(final String queryText, final List queryParameters)
     throws IOException, ClassNotFoundException, IllegalAccessException, 
         InstantiationException, SQLException {

      // Get result from cache
      final Cache queryCache = CacheManager.getInstance().getCache("query.cache");
      final QueryKey queryKey = new QueryKey(queryText, queryParameters);
      QueryResult queryResult = (QueryResult)queryCache.get(queryKey);
      if (queryResult == null) {
         // Get the result from the database
         final Connection conn = driver.connect("my/connection/URL", 
                                         new Properties());
         final PreparedStatement ps = conn.prepareStatement(queryText);

         // Set queryParameters
         for (int i = 1; i <= queryParameters.size(); i++) {
            final Object parameter = queryParameters.get(i - 1);
            ps.setObject(i, parameter);
         }

         // Execute the statement and retrive the result
         final List rows = new ArrayList(3);
         final ResultSet rs = ps.executeQuery();
         final int columnCount = rs.getMetaData().getColumnCount();
         while (rs.next()) {
            final Object[] row = new Object[columnCount];
            for (int columnIndex = 1; columnIndex <= columnCount; columnIndex++) {
               row[columnIndex - 1] = rs.getObject(columnIndex);
            }
         }

         // Create query result
         queryResult = new QueryResult(columnCount, rows);

         // Put the result to cache
         queryCache.put(queryKey, queryResult);

      }

      return queryResult;
   }
}

Conclusion

Applying the query optimization techniques and caching the database queries can provide a significant performance improvement for a Java application.