高效使用JDBC执行批量插入的方法

12 浏览
0 Comments

高效使用JDBC执行批量插入的方法

我的应用程序需要进行大量的INSERT操作。它是一个Java应用程序，我使用纯JDBC执行查询。数据库是Oracle。虽然我已经启用了批处理，但是它仍然以单独的INSERT序列执行查询，这样可以节省网络延迟：

insert into some_table (col1, col2) values (val1, val2)
insert into some_table (col1, col2) values (val3, val4)
insert into some_table (col1, col2) values (val5, val6)

我想知道以下形式的INSERT是否更高效：

insert into some_table (col1, col2) values (val1, val2), (val3, val4), (val5, val6)

即将多个INSERT合并为一个操作。

还有其他使批量INSERT更快的提示吗？

0

3 答案

0 Comments

高效使用JDBC进行批量插入的方法

在使用JDBC进行批量插入时，可以采用以下解决方案：

首先，通过使用prepareStatement方法来准备SQL语句，该方法只会解析一次SQL语句，提高了执行效率。

其次，使用ps.setString方法来设置待插入的值，然后使用ps.addBatch方法将该操作加入批处理中。

在这个特定的例子中，ps.clearParameters方法是不必要的，可以省略。

最后，使用ps.executeBatch方法来执行批处理操作，并将执行结果存储在results数组中。

需要注意的是，根据JDBC驱动程序的实现方式，批处理操作可能会导致每个批次一次网络往返，也可能会导致每个语句一次网络往返。

为了在MySQL中使用该方法，还需要在URL中添加以下参数：&useServerPrepStmts=false&rewriteBatchedStatements=true。

需要注意的是，clearParameters方法在这个例子中是无用的，只有在特定情况下才需要使用。

需要注意的是，MariaDB驱动程序3+版本已经移除了rewriteBatchedStatements选项。

0
0 Comments

使用JDBC进行批量插入的高效方法

在使用JDBC进行批量插入时，可以使用Statement对象的addBatch()方法将多个插入语句添加到批处理中，并使用executeBatch()方法一次性执行这些语句。下面是一个示例代码：

Statement stmt = con.createStatement();
stmt.addBatch("INSERT INTO employees VALUES (1000, 'Joe Jones')");
stmt.addBatch("INSERT INTO departments VALUES (260, 'Shoe')");
stmt.addBatch("INSERT INTO emp_dept VALUES (1000, 260)");
int[] updateCounts = stmt.executeBatch();

虽然这种方法最终的结果是一样的，但是它会解析多个语句，对于大量数据来说效率比较低，实际上并不比逐个执行每个语句更高效。在重复查询时，请尽量使用PreparedStatement，因为它们的性能更好。

有人问是否有测试PreparedStatement和非PreparedStatement的性能对比数据，一个Stack Overflow的回答提供了详细的统计数据，可以参考这个链接：stackoverflow.com/a/42756134/372055

在动态插入数据时，如果不想在循环中解析每个参数的数据类型，使用PreparedStatement正是解决这个问题的关键。

使用JDBC进行批量插入时，可以使用Statement的addBatch()方法将多个插入语句添加到批处理中，提高插入效率。同时，尽量使用PreparedStatement来执行重复查询，以获得更好的性能。

0
0 Comments

在使用JDBC高效插入Oracle的问题中，我目前正在尝试与DB2（IBM主机上）一起使用，概念上插入方式类似，所以我认为比较一下逐条插入和批量插入的效果可能会有所帮助。

首先是逐条插入的性能指标。

public void writeWithCompileQuery(int records) {
    PreparedStatement statement;
    try {
        Connection connection = getDatabaseConnection();
        connection.setAutoCommit(true);
        String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +
                " VALUES" + "(?, ?, ?, ?, ?)";
        statement = connection.prepareStatement(compiledQuery);
        long start = System.currentTimeMillis();
        for(int index = 1; index < records; index++) {
            statement.setInt(1, index);
            statement.setString(2, "emp number-"+index);
            statement.setInt(3, index);
            statement.setInt(4, index);
            statement.setString(5, "username");
            long startInternal = System.currentTimeMillis();
            statement.executeUpdate();
            System.out.println("each transaction time taken = " + (System.currentTimeMillis() - startInternal) + " ms");
        }
        long end = System.currentTimeMillis();
        System.out.println("total time taken = " + (end - start) + " ms");
        System.out.println("avg total time taken = " + (end - start)/ records + " ms");
        statement.close();
        connection.close();
    } catch (SQLException ex) {
        System.err.println("SQLException information");
        while (ex != null) {
            System.err.println("Error msg: " + ex.getMessage());
            ex = ex.getNextException();
        }
    }
}

使用逐条插入的方式，100次事务所需的时间如下：

each transaction time taken = 123 ms
each transaction time taken = 53 ms
each transaction time taken = 48 ms
each transaction time taken = 48 ms
each transaction time taken = 49 ms
each transaction time taken = 49 ms
...
..
.
each transaction time taken = 49 ms
each transaction time taken = 49 ms
total time taken = 4935 ms
avg total time taken = 49 ms

第一次事务需要约120-150毫秒，这是由于查询解析和执行的时间。随后的事务只需要约50毫秒。然而，这个时间仍然相对较高，但由于我的数据库位于不同的服务器上（需要排除网络问题）。

接下来是批量插入的方式，通过`preparedStatement.executeBatch()`实现高效插入。

public int[] writeInABatchWithCompiledQuery(int records) {
    PreparedStatement preparedStatement;
    try {
        Connection connection = getDatabaseConnection();
        connection.setAutoCommit(true);
        String compiledQuery = "INSERT INTO TESTDB.EMPLOYEE(EMPNO, EMPNM, DEPT, RANK, USERNAME)" +
                " VALUES" + "(?, ?, ?, ?, ?)";
        preparedStatement = connection.prepareStatement(compiledQuery);
        for(int index = 1; index <= records; index++) {
            preparedStatement.setInt(1, index);
            preparedStatement.setString(2, "empo number-"+index);
            preparedStatement.setInt(3, index+100);
            preparedStatement.setInt(4, index+200);
            preparedStatement.setString(5, "usernames");
            preparedStatement.addBatch();
        }
        long start = System.currentTimeMillis();
        int[] inserted = preparedStatement.executeBatch();
        long end = System.currentTimeMillis();
        System.out.println("total time taken to insert the batch = " + (end - start) + " ms");
        System.out.println("total time taken = " + (end - start)/records + " s");
        preparedStatement.close();
        connection.close();
        return inserted;
    } catch (SQLException ex) {
        System.err.println("SQLException information");
        while (ex != null) {
            System.err.println("Error msg: " + ex.getMessage());
            ex = ex.getNextException();
        }
        throw new RuntimeException("Error");
    }
}

使用批量插入的方式，100次事务所需的时间如下：

total time taken to insert the batch = 127 ms

对于1000次事务：

total time taken to insert the batch = 341 ms

因此，将100次事务的时间从逐条插入的方式（约5000毫秒）降低到了批量插入的方式（约150毫秒）。

有人问道，记录的长度是否会影响插入时间？我有3个Varchar列，它们的值是URI，即使将8555条记录作为批量插入，插入时间仍然需要大约3.5分钟！

根据我的理解，记录大小可能会影响从应用服务器向数据库服务器的数据传输时间，但插入时间影响不大。我在本地的Oracle数据库中尝试了3个大小为125字节的列，对于10000条记录的批量插入，需要约145到300毫秒。详细代码参见[这里](https://github.com/prayagupd/database-entertainment/blob/master/src/main/java/SingleBatchTransactionPerf.java)。而对于10000条记录的多次事务插入，需要20秒，详细代码参见[这里](https://github.com/prayagupd/database-entertainment/blob/master/src/main/java/MultipleTransactionPerf.java)。

还有人问，批处理的插入数量是否有限制？我有一些分析跟踪代码，它在内存中跟踪分析条目，然后单个线程以固定间隔运行并插入记录。每60秒可能会有数千条记录。

对于那些好奇的人，我在Oracle数据库上测试了1,000、10,000、100,000和1,000,000条记录的批处理方法，插入时间非常低。无论批处理中插入的记录数是多少，每次插入的平均时间都约为0.2毫秒。我使用System.nanoTime()获取更准确的时间。

以上是关于使用JDBC进行批量插入的高效方法的内容。

0