Archive

Posts Tagged ‘Performance’

Creating a simple cache in Java using a LinkedHashMap and an Anonymous Inner Class

June 28th, 2009
The processing costs for selecting a value from a database-table are fairly high compared to the costs having the value already in memory. So it seems preferrable to use some smart caching-mechanism that keeps often used values in your application instead of retrieving these values from resources somewhere ‘outside’.

Most frameworks have at least one cache implementation onboard, but there also exist several other implementations of caches like e.g. EHCache. Even ordinary HashMaps/Hashtables can serve as caches also.

A critial factor when using caches in Java is the size of the cache: when your cache grows too big, the Java Garbage Collector has to cleanup more often (which consumes time) or your application even crashes with a java.lang.OutOfMemoryError.

One way to control the memory-consumption of caches is to use SoftReferences in HashMaps/Hashtables, another one is to throw away old or unused content by implementing a caching-strategy like e.g. LRU.

A simple LRU-cache already ships within the components of the Java Standard Library: the LinkedHashMap. All you have to do is to tell your application whether the eldest entry in the map should be retained or removed after a new entry is inserted. Additionally a special constructor has to be used that defines the orderingMode for the map: ‘true’ for access-order (LRU), ‘false’ for insertion-order.

Suppose we want to cache a mapping of String-Names to Integer-Ids with a maximum size of 100 entries.
How that can be done is shown by the example below with the use of an Anonymous Inner Class that overrides the removeEldestEntry-method of the LinkedHashMap.
 Java |  copy code |? 
01
package com.java_blog;
02
 
03
import java.util.LinkedHashMap;
04
import java.util.Map;
05
 
06
 public class LinkedHashMapExample { 
07
 
08
      private final static int CACHE_MAX_SIZE = 100; 
09
      private LinkedHashMap<String, Integer> cache;
10
 
11
     @SuppressWarnings("serial")
12
      public LinkedHashMapExample() { 
13
 
14
            this.cache = new LinkedHashMap<String, Integer>(CACHE_MAX_SIZE, 0.75f, true) { 
15
                  protected boolean removeEldestEntry(
16
                             Map.Entry<String, Integer> eldest) {
17
                        // Remove the eldest entry if the size of the cache exceeds the
18
                        // maximum size
19
                        return size() > CACHE_MAX_SIZE;
20
                  }
21
            };
22
      } 
23
 
24
      public Integer getIdForName(String name) {
25
             Integer id = cache.get(name);
26
 
27
             if (id != null)
28
                  return id;
29
 
30
            else {
31
                  id = getIdForNameFromExternal(name);
32
                   // TODO Tino, 24.06.2009: what to do if no id could be found for the
33
                  // provided name in external resource?
34
                  cache.put(name, id);
35
                  return id;
36
            }
37
      }
38
 
39
      private Integer getIdForNameFromExternal(String name) {
40
             // TODO Tino, 24.06.2009: replace dummy-code
41
            return 1;
42
      }
43
}

Happy Coding,

Tino


Share/Save/Bookmark

tino Java ,

Prepared Statements with JDBC

March 9th, 2009
When your Java-application is talking to a database, there are several do’s and don’ts, patterns and anti-patterns you should be aware of. The probably most important ones are the proper use of indices (which is a database-structure-thing), the pooling of connections to the database (which is a Java-thing) and the reasonable use of Prepared Statements (which is also be done in Java). A blog-post concerning the first two issues might come sometimes in the future, but for now I will focus on Prepared Statements in Java using JDBC.

Because I am a fan of tutorials I’ll start with some practical examples before I’ll dive deeper into the pros and cons of Prepared Statements.

Let’s assume you have a table like this:

CREATE TABLE `userTable` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Your application’s task: select the name for a specific id.

Listing 1 shows the code with the use of a regular Statement.

 Java |  copy code |? 
01
import java.sql.Connection;
02
import java.sql.ResultSet;
03
import java.sql.SQLException;
04
import java.sql.Statement;
05
 
06
public class StatementSelect {
07
 
08
      private Connection      connection;
09
      private Statement       statement;
10
      private ResultSet        resultSet;
11
 
12
      public StatementSelect(Connection connection) {
13
            this.connection = connection;
14
      }
15
 
16
      /**
17
       * Selects the name for a specific id from the userTable in the database
18
       *
19
       * @param id
20
       * @return
21
       * @throws SQLException
22
       */
23
      public String select(int id) throws SQLException {
24
            final String nameColumn = "name";
25
            String ret = "";
26
 
27
            String query = String.format("SELECT %s FROM userTable WHERE id = %s",
28
                        nameColumn, id); 
29
 
30
            if (statement == null || statement.isClosed())
31
                 statement = connection.createStatement();
32
 
33
           try {
34
                  resultSet = statement.executeQuery(query);
35
                  /*
36
                   * Let's assume 'id' is a primary key --> There is at most one entry
37
                   * for a specific id.
38
                   */
39
                  if (resultSet.next())
40
                        ret = resultSet.getString(nameColumn);
41
 
42
           } finally {
43
                  try {
44
                        if (resultSet != null)
45
                             resultSet.close();
46
                  } catch (SQLException e) {
47
                        e.printStackTrace();
48
                 }
49
            }
50
           return ret;
51
      }
52
 
53
     public void cleanUp() {
54
            try {
55
                  if (statement != null)
56
                        statement.close();
57
           } catch (SQLException e) {
58
                  e.printStackTrace();
59
            }
60
      }
61
}


Listing 2 makes use of a Prepared Statement.

 Java |  copy code |? 
01
import java.sql.Connection;
02
import java.sql.PreparedStatement;
03
import java.sql.ResultSet;
04
import java.sql.SQLException;
05
 
06
public class PreparedStatementSelect {
07
 
08
      private Connection           connection;
09
      private PreparedStatement    preparedStatement;
10
      private ResultSet            resultSet;
11
 
12
      public PreparedStatementSelect(Connection connection) {
13
            this.connection = connection;
14
      }
15
 
16
      /**
17
       * Selects the name for a specific id from the userTable in the database
18
       *
19
       * @param id
20
       * @return
21
       * @throws SQLException
22
       */
23
      public String select(int id) throws SQLException {
24
            final String nameColumn = "name";
25
            String ret = "";
26
 
27
            String query = String.format("SELECT %s FROM userTable WHERE id = ?",
28
                        nameColumn);
29
 
30
            if (preparedStatement == null || preparedStatement.isClosed())
31
                  preparedStatement = connection.prepareStatement(query);
32
 
33
            preparedStatement.setInt(1, id);
34
 
35
            try {
36
                  resultSet = preparedStatement.executeQuery();
37
 
38
                  /*
39
                   * Let's assume 'id' is a primary key --> There is at most one entry
40
                   * for a specific id.
41
                   */
42
                  if (resultSet.next())
43
                        ret = resultSet.getString(nameColumn);
44
 
45
            } finally {
46
                  try {
47
                        if (resultSet != null)
48
                             resultSet.close();
49
                  } catch (SQLException e) {
50
                        e.printStackTrace();
51
                  }
52
            }
53
 
54
            return ret;
55
      }
56
 
57
      public void cleanUp() {
58
            try {
59
                  if (preparedStatement != null)
60
                        preparedStatement.close();
61
            } catch (SQLException e) {
62
                  e.printStackTrace();
63
            }
64
      }
65
}



Did you spot the differences?

  • Line 27: the queries are not identical: instead of directly specifying for which id the name should be selected in listing 1, a ‘?’-placeholder is used in listing 2 when preparing the Prepared Statement.

  • Line 31: listing 1 creates a regular Statement-object; a Prepared Statement is created in listing 2.

  • Line 33: to tell your database for which id you’d like to have the name, an additional step is needed in listing 2: the ‘?’ that was used to prepare the Prepared Statement is replaced by the correct id.

  • Line 34/36: in listing 1 the query is used as parameter in the executeQuery()-call. Listing 2 doesn’t use a parameter.


So now let’s talk about the two major advantages that come with the use of Prepared Statements:

  • If the above query is executed multiple times, the query in the regular statement has to be compiled by the database every time before execution. With Prepared Statements it has to be compiled only once (but this compilation needs an extra roundtrip to the database). This once-vs-multiple-compilation may save you time, depending on the structure of your requests. As a rule of thumb: The more often you execute a query with the same structure, the more likely it is that you gain a performance boost through Prepared Statements.


  • In regular queries every String in the where-condition has to be escaped by surrounding it with ”. This job is done for you by Prepared Statements automatically.

    The consequences? You don’t have to worry about escaping and you are less vulnerable to SQL-injections.


But there are some points you should have in mind, when going for Prepared Statements:

  • Prepared Statements are built per connection. This means, if you have multiple connections to your database and the same query should be executed a couple of times, you should make sure, that the Prepared Statement for that query is always using the same connection. Otherwise you have to recreate the Prepared Statement for every connection, which produces overhead.

  • The intention of Prepared Statements is reusability, so make your Prepared Statements reusable (like I did in listing 2)! A lot of overhead would be produced, if you recreate the Prepared Statement every time when the query is executed.


  • If you’re executing a query only once, maybe regular Statements will better fit your needs: keep in mind that the preparation of Prepared Statements needs an extra database-call. But don’t forget to validate your input first to prevent SQL-injection!


  • If you’re using MySQL < 5.1.17, the query cache is not used by Prepared Statements. So if you have few modifications on your datasets and you execute the same query over and over, have a close look if the use of Prepared Statements really increases performance.


  • If you think about parametrizing the tablename (e.g. ‘SELECT name FROM ?’): forget it! That won’t work!


So after this short trip through Prepared Statements I hope some of the advantages and disadvantes have become obvious.

If you find any mistakes in my post, or if you are of a different opinion, feel free to leave a comment or use the contact-site.


Happy coding,

Tino.

Share/Save/Bookmark

tino JDBC, Java , , ,