Increase perfomance for insert operations to database

Hi all!

In this post, I would like to tell about the way to optimize INSERT operation to database.


In our project we use postgresql 8.3. I have big problems with speed on some operations. When I profiled the application, I find out that the biggest amount of time spent for JDBC operations. We getting data from external systems, and often we have more than 300000 inserts to table. For work with database we use JDBCTemplate from Spring framework.

All that you should do: realize class that implements BatchPreparedStatementSetter. I create universal abstract class that implements this interface.

abstract public class AbstractPreparedStatementSetter implements PreparedStatementSetter, BatchPreparedStatementSetter {
 private List objects;

 protected AbstractPreparedStatementSetter(List objects) {
  this.objects = objects;
 }

 public void setValues(PreparedStatement preparedStatement) throws SQLException {
  fillStatement(objects.get(0), preparedStatement);
 }

 @Override
 public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
  fillStatement(objects.get(i), preparedStatement);
 }

 @Override
 public int getBatchSize() {
  return objects.size();
 }

 abstract public void fillStatement(T object, PreparedStatement statement) throws SQLException;
}

Concrete implementation example:

public class RegistrationEntrySetter extends AbstractPreparedStatementSetter {
 public RegistrationEntrySetter(List objects) {
  super(objects);
 }

 @Override
 public void fillStatement(RegistrationEntry entry, PreparedStatement statement) throws SQLException {
  long time = entry.getActionDate() == null ? System.currentTimeMillis() : entry.getActionDate().getTime();
  statement.setString(1, entry.getLogin());
  statement.setTimestamp(2, new Timestamp(time));
  statement.setShort(3, entry.getActionResult());
  statement.setString(4, entry.getDeviceName());
  String comment = entry.getComment();
  if(comment.length() > 8190)
   comment = comment.substring(0, 8190);
  statement.setString(5, comment);
  statement.setShort(6, entry.getAcessLevel().getLevel());
  statement.setString(7, entry.getObjectType());
  statement.setString(8, entry.getObjectCategory());
  statement.setString(9, entry.getObjectId());
  statement.setInt(10, entry.getEventType().getId());
  statement.setString(11, entry.getActionName());
  statement.setString(12, entry.getUserName());
  statement.setShort(13, entry.isNotification() ? (short)1 : (short)0);
  statement.setString(14, entry.getRemoteAddress());
  statement.setString(15, PasswordEncoder.encode(entry.toString()));
 }
}

In spring xml context, I describe all implementations for statements setters for my classes.

    <bean id="preparedStatementsSetter" class="java.util.HashMap">
        <constructor-arg>
            <map>
                <entry key="com.blogspot.RegistrationEntry">
                    <value>com.otr.security.server.audit.sql.RegistrationEntrySetter</value>
                </entry>
                .....
                .....
            </map>
        </constructor-arg>
    </bean>

And in my dao I can get setter for concrete class for list of objects.
In this way we have one advantage, all setters are singleton and we have reusable prepared statements.

The sample dao may look like this:

public class AbstractDAO {
 //spring fields
 private SimpleJdbcTemplate template;
 private Map<Class, Class<? extends AbstractPreparedStatementSetter>> statementSetters;
 //spring fields

 public int[] batchUpdate(String sql, List objects) {
  if (!objects.isEmpty()) {
   try {
    AbstractPreparedStatementSetter setter = getSetter(objects, objects.get(0).getClass());
    if (setter != null) {
     return template.getJdbcOperations().batchUpdate(sql, setter);
    }
   } catch (Exception e) {
    log.error("Error while construct sql setter", e);
   }
  }

  return new int[]{};
 }

 private AbstractPreparedStatementSetter getSetter(List objects, Class objectClazz)
   throws InvocationTargetException, IllegalAccessException, InstantiationException, NoSuchMethodException {
  Class<? extends AbstractPreparedStatementSetter> setterClazz = statementSetters.get(objectClazz);

  if (setterClazz != null) {
   Constructor<? extends AbstractPreparedStatementSetter> c = setterClazz.getConstructor(List.class);
   return c.newInstance(objects);
  } else {
   log.warn("======================NO SQL SETTER FOR CLASS " + objectClazz.getName() + "======================");
   return null;
  }
 }
}

Connect to mysql 5 with Tomcat 6

When I try to solve this problem I read two docs: help in Netbeans 6(http://www.netbeans.org/kb/60/web/mysql-webapp.html#settingUpConnPool) and jndi help from tomcat.apache.org(http://tomcat.apache.org/tomcat-6.0-doc/jndi-datasource-examples-howto.html). But when I lookup InitialContext, application throws NamingException. I spent over four hours to solve this problem. As I've understood, when I create resource in context.xml which placed in web application, Tomcat dont put it in global resources and when I connect to this resource, I should write prefix java:comp/env/ before actual resource name. I replace this line:

Datasource ds = (Datasource)ic.lookup("jdbs/sambledb");

with this:

Datasource ds = (Datasource)ic.lookup("java:comp/env/jdbs/sambledb");

YES! Lookup result was successfull.

I use oficial recommended jconnector version 5.1 from mysql.com. I copy jar with connector in TOMCAT_HOME/lib/, but I get ClassNotFoundException.... after long four or five hours I hate Window Vista, Tomcat, Netbeans, Java, Computers and...my life :) . I cannt direct solve this problem. I have no ideas how services in Windows works, but when I uninstall Tomcat which I downloaded from link with name Windows Service Installer(http://www.sai.msu.su/apache/tomcat/tomcat-6/v6.0.18/bin/apache-tomcat-6.0.18.exe) and install simple zip version (http://www.sai.msu.su/apache/tomcat/tomcat-6/v6.0.18/bin/apache-tomcat-6.0.18.zip) all works ok(YEAH!) :)

Some tips:

-Don't use empty password in mysql to connect.

-In different cases Tomcat throws exception sounds like this - driver "com.mysql.jdbc.Driver" not found. You can reduce number of meanings if insert in your code this line - Class.forName("com.mysql.jdbc.Driver").

-DONT USE SERVICE INSTALLER VERSION OF TOMCAT!!!(I like init.d:))

Finally I have something like this:

web.xml:

<resource-ref>
<description>jdbc:mysql://localhost:3306/mynewdatabase</description>
  <res-ref-name>jdbc/mynewdatabase/<res-ref-name>
  <res-type>javax.sql.DataSource</res-type>
  <res-auth>Container</res-auth>
</resource-ref>

context.xml:

<Resource name="jdbc/mynewdatabase" auth="Container" type="javax.sql.DataSource"
maxActive="100" maxIdle="30" maxWait="10000"
username="javauser" password="qwerty" driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://127.0.0.1:3306/mynewdatabase"/></resource>

sample method to test connection:

...
public String getEmployers() {
  try {
    InitialContext ic = new InitialContext();
    DataSource ds = (DataSource)   ic.lookup("java:comp/env/jdbc/mynewdatabase");
    Connection con = ds.getConnection(); //NO User and Password, we set it in Resource
    Statement st = con.createStatement();

    ResultSet rs = st.executeQuery("SELECT * FROM employers");

    String ret = "Employers ";
    while (rs.next()) {
      ret += rs.getString("name") + ",";
    }
    return ret;
  } catch (SQLException ex) {
    return ex.getMessage();
  } catch (NamingException ex) {
    return ex.getMessage();
  }
}
...

If you have troubles, write its in comments and I try to help you.