Caching Database Queries in Java

Overview

Caching database queries can reduce and even remove the performance degradation caused by slow database access.

Introduction

Database queries maybe a main source of performance problems in a Java application. To process the queries, a database server may have to do work that takes significant amount of time. Executing and getting results of such queries can take seconds and even minutes. For many Java applications such delays are unacceptable.

Two steps are involved in addressing the performance problem caused by the heavy database queries. These steps are:

Optimizing database queries.
Caching database queries.

Optimizing Database Queries

Optimizing database queries is the first step in addressing the performance problem.

Creating Indexes for Where and Order By Fields

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 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.

Analyzing Query Execution Plan

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:

Execute the query in question using a database console tool.
Request the tool to provide a query execution plan.
Find places where the database reports full scans.
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 large magnitude.

Query Caching Algorithm

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:

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

Query Caching Example

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 QueryResult 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 responsible 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.Cacheonix;



public final class CachingDatabaseQueryExecutor {



   // Set up the database driver

   private static final Driver driver;





   static {



      try {



         final Class driverClass = Class.forName("my.database.drive.name");

         driver = (Driver) driverClass.newInstance();

      } catch (Exception e) {



         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 = Cacheonix.getInstance().getCache("query.cache");

      final QueryKey queryKey = new QueryKey(queryText, queryParameters);

      QueryResult queryResult = (QueryResult) queryCache.get(queryKey);

      if (queryResult == null) {

         

         // Not in cache, get the result from the database

         Connection conn = null;

         PreparedStatement ps = null;

         ResultSet rs = null;



         try {

            conn = driver.connect("my/connection/URL", new Properties());

            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 retrieve the result

            final List rows = new ArrayList();

            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);

               }

               rows.add(row);

            }



            // Create query result

            queryResult = new QueryResult(columnCount, rows);



            // Put the result to cache

            queryCache.put(queryKey, queryResult);

            

         } finally {

            

            // Close result set

            if (rs != null) {

               

               try {

                  rs.close();

               } catch (SQLException ignored) {

               }

            }



            // Close prepared statement

            if (ps != null) {

               

               try {

                  ps.close();

               } catch (SQLException ignored) {

               }

            }



            // Close connection

            if (conn != null) {

               

               try {

                  conn.close();

               } catch (SQLException ignored) {

               }

            }

         }



      }



      return queryResult;

   }

Conclusion

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

Adding Java Cache Cacheonix to Your Application

Cacheonix is an Open Source Java project that offers a fast local cache and a strictly-consistent distrbuted cache. To add Cacheonix to your Maven project, add the following to the dependencies section of your pom.xml:

<dependency>
   <groupId>org.cacheonix</groupId>
   <artifactId>cacheonix-core</artifactId>
   <version>2.3.1</version>
<dependency>

Download Open Source cache Cacheonix