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