PreparedStatement批处理表现不如原来的声明

Question

我正在将数据访问代码写入批处理操作，以提高我的应用程序的性能。

我现在正在执行删除操作，但这很容易适用于插入和更新。

我有5个表来处理。这是两种方法我试过（辅助功能是在底部）：

public void deleteFooList(final int[] fooIds) 
     throws SQLException { 

    Connection connection = dataSource.getConnection(); 

    try { 
     connection.setAutoCommit(false); 
     PreparedStatement childStatement = connection.prepareStatement(
       "DELETE FROM child WHERE fooId = ?"); 
     PreparedStatement otherStatement = connection.prepareStatement(
       "DELETE FROM otherfoo WHERE fooId = ?"); 
     PreparedStatement userStatement = connection.prepareStatement(
       "DELETE FROM userfoo WHERE fooId = ?"); 
     PreparedStatement modStatement = connection.prepareStatement(
       "DELETE FROM modification WHERE fooId = ?"); 
     PreparedStatement fooStatement = connection.prepareStatement(
       "DELETE FROM foo WHERE id = ?"); 
     for (int fooId : fooIds) { 
      childStatement.setInt(1, fooId); 
      otherStatement.setInt(1, fooId); 
      userStatement.setInt(1, fooId); 
      modStatement.setInt(1, fooId); 
      fooStatement.setInt(1, fooId); 
      childStatement.addBatch(); 
      otherStatement.addBatch(); 
      userStatement.addBatch(); 
      modStatement.addBatch(); 
      fooStatement.addBatch(); 
     } 
     executeBatchAndCheckResult(childStatement, fooIds.length); 
     executeBatchAndCheckResult(otherStatement, fooIds.length); 
     executeBatchAndCheckResult(userStatement, fooIds.length); 
     executeBatchAndCheckResult(modStatement, fooIds.length); 
     executeBatchAndCheckResult(fooStatement, fooIds.length); 

     connection.commit(); 
    } catch (SQLException e) { 
     connection.rollback(); 
     throw e; 
    } finally { 
     connection.close(); 
    } 
} 

而且我也试过这样：

public void deleteFooList2(final int[] fooIds) 
    throws SQLException { 
    StringBuilder deleteChildSql = new StringBuilder(
      "DELETE FROM child WHERE fooId IN ("); 
    StringBuilder deleteOtherSql = new StringBuilder(
      "DELETE FROM otherfoo WHERE fooId IN ("); 
    StringBuilder deleteUserSql = new StringBuilder(
      "DELETE FROM userfoo WHERE fooId IN ("); 
    StringBuilder deleteModSql = new StringBuilder(
      "DELETE FROM modification WHERE fooId IN ("); 
    StringBuilder deleteFooSql = new StringBuilder(
      "DELETE FROM foo WHERE id IN ("); 
    Connection connection = childSource.getConnection(); 

    try { 
     connection.setAutoCommit(false); 
     Statement statement = connection.createStatement(); 
     for (int x = 0; x < fooIds.length; x++) { 
      if (x > 0) { 
       deleteChildSql.append(","); 
       deleteOtherSql.append(","); 
       deleteUserSql.append(","); 
       deleteModSql.append(","); 
       deleteFooSql.append(","); 
      } 
      deleteChildSql.append(fooIds[x]); 
      deleteOtherSql.append(fooIds[x]); 
      deleteUserSql.append(fooIds[x]); 
      deleteModSql.append(fooIds[x]); 
      deleteFooSql.append(fooIds[x]); 
     } 
     deleteChildSql.append(")"); 
     deleteOtherSql.append(")"); 
     deleteUserSql.append(")"); 
     deleteModSql.append(")"); 
     deleteFooSql.append(")"); 
     statement.addBatch(deleteChildSql.toString()); 
     statement.addBatch(deleteOtherSql.toString()); 
     statement.addBatch(deleteUserSql.toString()); 
     statement.addBatch(deleteModSql.toString()); 
     statement.addBatch(deleteFooSql.toString()); 
     executeBatchAndCheckResult(statement, fooIds.length); 
     connection.commit(); 
    } catch (SQLException e) { 
     connection.rollback(); 
     throw e; 
    } finally { 
     connection.close(); 
    } 

使用这个辅助功能，为了保持完整性，而不是相关性：

private void executeBatchAndCheckResult(Statement statement, 
             int count) 
     throws SQLException { 
    int[] results = statement.executeBatch(); 
    if (results == null) { 
     throw new SQLException(
       "Batch update failed to return results!"); 
    } 
    int total = 0; 
    for (int result : results) { 
     total += result; 
     if (result == Statement.EXECUTE_FAILED) { 
      String sql = statement.toString(); 
      throw new SQLException(String.format(
        "Error executing batch: %s", sql)); 
     } 
    } 
    log.info(String.format("Ran batch, statement count %d, row count %d", 
      results.length, total)); 
    if (results.length != count) { 
     throw new SQLException(String.format(
       "Batch update failed to execute correct count! " + 
         "(%d instead of %d)", results.length, count)); 
    } 
} 

我很惊讶地发现，原始Statement表现比5 PreparedStatements更快。实际上它是看不见的。 Statement的速度与其自身的5 PreparedStatements一样快。我是否错误地执行了PreparedStatement批次？

这是从Statement快速SQL：

DELETE FROM foo WHERE id IN (52000,52001,52002,52003,52004,52005,52006,52007,52008,52009,52010) 
DELETE FROM modification WHERE fooId IN (52000,52001,52002,52003,52004,52005,52006,52007,52008,52009,52010) 
DELETE FROM userfoo WHERE fooId IN (52000,52001,52002,52003,52004,52005,52006,52007,52008,52009,52010) 
DELETE FROM otherfoo WHERE fooId IN (52000,52001,52002,52003,52004,52005,52006,52007,52008,52009,52010) 
DELETE FROM childfoo WHERE fooId IN (52000,52001,52002,52003,52004,52005,52006,52007,52008,52009,52010) 

，这是从PreparedStatements慢SQL：

DELETE FROM foo WHERE id = 52010 
DELETE FROM modification WHERE fooId = 52010 
DELETE FROM userfoo WHERE fooId = 52010 
DELETE FROM otherfoo WHERE fooId = 52010  
DELETE FROM childfoo WHERE fooId = 52010 

这是P6SPY所以它不是到底发生了什么。来自PreparedStatements的日志记录仅显示添加到批次中的最后一项，而不是全部DELETE。

我有关于MySQL JDBC参数rewriteBatchedStatements=true，所以我假设了MySQL连接器/ J被重写批类似像

DELETE FROM foo WHERE id = 52010 OR id = 52009 OR id = 52008 OR id = 52007 OR id = 52006 OR id = 52005 etc 

东西也许它不是？我还能做什么错？

Answer 1

我有关于MySQL JDBC参数rewriteBatchedStatements=true，所以我假设了MySQL连接器/ J被改写[删除]批量

不，事实并非如此。 rewriteBatchedStatements=true仅适用于INSERT INTO ... VALUES ...批次。一批DELETE ... WHERE fooId = ?语句仍将单独发送每个DELETE语句。 （通过测试和检查通用日志来确认）。这就是为什么您会看到PreparedStatement批处理和您的Statement（已经过手动优化以在单次往返中删除多行）之间的性能差异。