iBatis SQL Maps

Nitin.Dubey | 14 Jun 2010 07:10

SQLExecutor.addBatch() modified

By default for batch inserts ibatis caches the prepared statements in list and always re-uses the last statement for table inserts. This model works for batch inserts for a single table. However, we have a requirement where we need to do batch inserts into several tables in a single transaction. Since ibatis is designed for batch inserts to a single table, using multi table insert gives us pathetic performance, as ibatis creates a new PreparedStatement for every query (occuping database cursor as well).

I looked at the source code and modified SqlExecutor==>addBatch() method. This method uses a Map instead of a list. Corresponding changes are done to populate the map and retrieve from map. Following is the code.

private Map<String,PreparedStatement> statementMap = new HashMap<String,PreparedStatement>();

    public void addBatch(StatementScope statementScope, Connection conn, String sql, Object[] parameters) throws SQLException {
      PreparedStatement ps = null;
      //if (currentSql != null && currentSql.equals(sql)) {
      if (currentSql != null && statementMap.containsKey(currentSql)) {
        //int last = statementList.size() - 1;
        ps = (PreparedStatement) statementMap.get(currentSql);
      } else {
        ps = prepareStatement(statementScope.getSession(), conn, sql);
        setStatementTimeout(statementScope.getStatement(), ps);
        currentSql = sql;
        statementList.add(ps);
        statementMap.put(currentSql,ps);
        batchResultList.add(new BatchResult(statementScope.getStatement().getId(), sql));
      }
      statementScope.getParameterMap().setParameters(statementScope, ps, parameters);
      ps.addBatch();
      size++;
    }

    public int executeBatch() throws SQLException {
      int totalRowCount = 0;
      Collection<PreparedStatement> psColl = statementMap.values();
      //for (int i = 0, n = statementList.size(); i < n; i++) {
      Iterator<PreparedStatement> it = psColl.iterator();
      //for (int i = 0, n = psColl.size(); i < n; i++) {
      while(it.hasNext()){
        //PreparedStatement ps = (PreparedStatement) statementList.get(i);
       PreparedStatement ps = it.next();
        int[] rowCounts = ps.executeBatch();
        for (int j = 0; j < rowCounts.length; j++) {
          if (rowCounts[j] == Statement.SUCCESS_NO_INFO) {
            // do nothing
          } else if (rowCounts[j] == Statement.EXECUTE_FAILED) {
            throw new SQLException("The batched statement at index " + j + " failed to execute.");
          } else {
            totalRowCount += rowCounts[j];
          }
        }
      }
      return totalRowCount;
    }

Que: After making these changes everything works for us and the performance is very good as it starts using cached PreparedStatements. Does it look like a proper implementation? Will it have any other impact that I may not have seen yet?

-- Nitin

headers

Nathan Maves | 14 Jun 2010 17:10

Re: SQLExecutor.addBatch() modified

This is all great stuff but if you could please great a ticket//issue
for this and attach the fix to it.  This way we can track it.

Submission form - http://code.google.com/p/mybatis/issues/entry

Nathan

On Sun, Jun 13, 2010 at 11:10 PM,  <Nitin.Dubey <at> sungard.com> wrote:
> By default for batch inserts ibatis caches the prepared statements in list
> and always re-uses the last statement for table inserts.  This model works
> for batch inserts for a single table.  However, we have a requirement where
> we need to do batch inserts into several tables in a single transaction.
> Since ibatis is designed for batch inserts to a single table, using multi
> table insert gives us pathetic performance, as ibatis creates a new
> PreparedStatement for every query (occuping database cursor as well).
>
> I looked at the source code and modified SqlExecutor==>addBatch() method.
> This method uses a Map instead of a list.  Corresponding changes are done to
> populate the map and retrieve from map.  Following is the code.
>
>     private Map<String,PreparedStatement> statementMap = new
> HashMap<String,PreparedStatement>();
>
>     public void addBatch(StatementScope statementScope, Connection conn,
> String sql, Object[] parameters) throws SQLException {
>       PreparedStatement ps = null;
>       //if (currentSql != null && currentSql.equals(sql)) {
>       if (currentSql != null && statementMap.containsKey(currentSql)) {
>         //int last = statementList.size() - 1;
>         ps = (PreparedStatement) statementMap.get(currentSql);
>       } else {
>         ps = prepareStatement(statementScope.getSession(), conn, sql);
>         setStatementTimeout(statementScope.getStatement(), ps);
>         currentSql = sql;
>         statementList.add(ps);
>         statementMap.put(currentSql,ps);
>         batchResultList.add(new
> BatchResult(statementScope.getStatement().getId(), sql));
>       }
>       statementScope.getParameterMap().setParameters(statementScope, ps,
> parameters);
>       ps.addBatch();
>       size++;
>     }
>
>     public int executeBatch() throws SQLException {
>       int totalRowCount = 0;
>       Collection<PreparedStatement> psColl = statementMap.values();
>       //for (int i = 0, n = statementList.size(); i < n; i++) {
>       Iterator<PreparedStatement> it = psColl.iterator();
>       //for (int i = 0, n = psColl.size(); i < n; i++) {
>       while(it.hasNext()){
>         //PreparedStatement ps = (PreparedStatement) statementList.get(i);
>        PreparedStatement ps = it.next();
>         int[] rowCounts = ps.executeBatch();
>         for (int j = 0; j < rowCounts.length; j++) {
>           if (rowCounts[j] == Statement.SUCCESS_NO_INFO) {
>             // do nothing
>           } else if (rowCounts[j] == Statement.EXECUTE_FAILED) {
>             throw new SQLException("The batched statement at index " + j + "
> failed to execute.");
>           } else {
>             totalRowCount += rowCounts[j];
>           }
>         }
>       }
>       return totalRowCount;
>     }
>
> Que: After making these changes everything works for us and the performance
> is very good as it starts using cached PreparedStatements.  Does it look
> like a proper implementation?  Will it have any other impact that I may not
> have seen yet?
>
> -- Nitin

headers

Nathan Maves | 14 Jun 2010 17:11

Re: SQLExecutor.addBatch() modified

On a side note...

The ibatis project has moved and been renamed.

It is no longer being maintained as an Apache project, but has moved
(along with the development team) here:

http://www.mybatis.org/

Please join us at the new location by joining the mailing list here:

http://groups.google.com/group/mybatis-user

On Mon, Jun 14, 2010 at 9:10 AM, Nathan Maves <nathan.maves <at> gmail.com> wrote:
> This is all great stuff but if you could please great a ticket//issue
> for this and attach the fix to it.  This way we can track it.
>
> Submission form - http://code.google.com/p/mybatis/issues/entry
>
> Nathan
>
> On Sun, Jun 13, 2010 at 11:10 PM,  <Nitin.Dubey <at> sungard.com> wrote:
>> By default for batch inserts ibatis caches the prepared statements in list
>> and always re-uses the last statement for table inserts.  This model works
>> for batch inserts for a single table.  However, we have a requirement where
>> we need to do batch inserts into several tables in a single transaction.
>> Since ibatis is designed for batch inserts to a single table, using multi
>> table insert gives us pathetic performance, as ibatis creates a new
>> PreparedStatement for every query (occuping database cursor as well).
>>
>> I looked at the source code and modified SqlExecutor==>addBatch() method.
>> This method uses a Map instead of a list.  Corresponding changes are done to
>> populate the map and retrieve from map.  Following is the code.
>>
>>     private Map<String,PreparedStatement> statementMap = new
>> HashMap<String,PreparedStatement>();
>>
>>     public void addBatch(StatementScope statementScope, Connection conn,
>> String sql, Object[] parameters) throws SQLException {
>>       PreparedStatement ps = null;
>>       //if (currentSql != null && currentSql.equals(sql)) {
>>       if (currentSql != null && statementMap.containsKey(currentSql)) {
>>         //int last = statementList.size() - 1;
>>         ps = (PreparedStatement) statementMap.get(currentSql);
>>       } else {
>>         ps = prepareStatement(statementScope.getSession(), conn, sql);
>>         setStatementTimeout(statementScope.getStatement(), ps);
>>         currentSql = sql;
>>         statementList.add(ps);
>>         statementMap.put(currentSql,ps);
>>         batchResultList.add(new
>> BatchResult(statementScope.getStatement().getId(), sql));
>>       }
>>       statementScope.getParameterMap().setParameters(statementScope, ps,
>> parameters);
>>       ps.addBatch();
>>       size++;
>>     }
>>
>>     public int executeBatch() throws SQLException {
>>       int totalRowCount = 0;
>>       Collection<PreparedStatement> psColl = statementMap.values();
>>       //for (int i = 0, n = statementList.size(); i < n; i++) {
>>       Iterator<PreparedStatement> it = psColl.iterator();
>>       //for (int i = 0, n = psColl.size(); i < n; i++) {
>>       while(it.hasNext()){
>>         //PreparedStatement ps = (PreparedStatement) statementList.get(i);
>>        PreparedStatement ps = it.next();
>>         int[] rowCounts = ps.executeBatch();
>>         for (int j = 0; j < rowCounts.length; j++) {
>>           if (rowCounts[j] == Statement.SUCCESS_NO_INFO) {
>>             // do nothing
>>           } else if (rowCounts[j] == Statement.EXECUTE_FAILED) {
>>             throw new SQLException("The batched statement at index " + j + "
>> failed to execute.");
>>           } else {
>>             totalRowCount += rowCounts[j];
>>           }
>>         }
>>       }
>>       return totalRowCount;
>>     }
>>
>> Que: After making these changes everything works for us and the performance
>> is very good as it starts using cached PreparedStatements.  Does it look
>> like a proper implementation?  Will it have any other impact that I may not
>> have seen yet?
>>
>> -- Nitin
>