Skip to content

Server-side batch execution for prepared statements#3997

Merged
katzyn merged 2 commits intoh2database:masterfrom
katzyn:batch
Feb 5, 2024
Merged

Server-side batch execution for prepared statements#3997
katzyn merged 2 commits intoh2database:masterfrom
katzyn:batch

Conversation

@katzyn
Copy link
Contributor

@katzyn katzyn commented Feb 4, 2024 •
edited
Loading

  1. PreparedStatement.execute[Large]Batch() methods now send all rows with parameters in a single request to a remote server instead of execution of every element in a separate request. This significantly reduces execution time of fast commands in large batches with real remote servers. Note: Statement.execute[Large]Batch() methods still execute every command in a separate request, I think these methods are rarely used.
  2. All batch execution methods in both Statement and PreparedStatement don't allocate an empty SQLException to hold other exceptions in it any more. As it was pointed out by @turbanoff, this allocation significantly increases execution time of single-command batches in embedded in-memory mode.

These changes are mostly intended for remote connections, but they work well for embedded in-memory database too:

Reduced benchmark for corner cases (based on benchmark from another PR) 
package org.sample;

import org.openjdk.jmh.annotations.*;
import java.util.concurrent.*;

import java.sql.*;

@OutputTimeUnit(TimeUnit.SECONDS)
@Warmup(iterations = 12, time = 1, timeUnit = TimeUnit.SECONDS)
@Measurement(iterations = 10, time = 2, timeUnit = TimeUnit.SECONDS)
@State(Scope.Thread)
@Fork(2)
public class BatchInsertBenchmark {

    @Param({"1", "10", "100", "1000"})
    int rows;

    private PreparedStatement prepared;

    @Setup(Level.Invocation)
    public void setup() throws ClassNotFoundException, SQLException {
        if (prepared != null) {
            prepared.getConnection().close();
        }
        Connection conn = DriverManager.getConnection("jdbc:h2:mem:");
        Statement statement = conn.createStatement();
        statement.execute("CREATE table TO_FILL(a int primary key, b varchar)");
        statement.close();
        prepared = conn.prepareStatement("insert into TO_FILL(a, b) values (?, ? )");
        prepared.setInt(1, -1);
        prepared.setString(2, "value");
        prepared.executeUpdate();
    }

    @Benchmark
    public long[] executeLargeBatch() throws SQLException {
        for (int i = 0; i < rows; i++) {
            prepared.setInt(1, i);
            prepared.setString(2, "value" + i);
            prepared.addBatch();
        }
        return prepared.executeLargeBatch();
    }

    @Benchmark
    public long[] executeLargeBatchManyFailures() throws SQLException {
        for (int i = 0; i < rows; i++) {
            prepared.setInt(1, 0);
            prepared.setString(2, "value");
            prepared.addBatch();
        }
        try {
            return prepared.executeLargeBatch();
        } catch (BatchUpdateException e) {
            return e.getLargeUpdateCounts();
        }
    }

}

Mainline H2:

Benchmark                                           (rows)   Mode  Cnt      Score      Error  Units
BatchInsertBenchmark.executeLargeBatch                   1  thrpt   20  62437,377 ±  867,732  ops/s
BatchInsertBenchmark.executeLargeBatch                  10  thrpt   20  20825,418 ±  290,052  ops/s
BatchInsertBenchmark.executeLargeBatch                 100  thrpt   20   2618,407 ±  181,622  ops/s
BatchInsertBenchmark.executeLargeBatch                1000  thrpt   20    246,260 ±    1,255  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures       1  thrpt   20  64943,801 ± 1918,061  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures      10  thrpt   20   5053,851 ±   73,892  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures     100  thrpt   20    547,155 ±    6,377  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures    1000  thrpt   20     44,991 ±    0,431  ops/s

This PR:

Benchmark                                           (rows)   Mode  Cnt       Score       Error  Units
BatchInsertBenchmark.executeLargeBatch                   1  thrpt   20  156411,302 ±  5175,599  ops/s
BatchInsertBenchmark.executeLargeBatch                  10  thrpt   20   43891,349 ±  1433,532  ops/s
BatchInsertBenchmark.executeLargeBatch                 100  thrpt   20    4534,062 ±   149,524  ops/s
BatchInsertBenchmark.executeLargeBatch                1000  thrpt   20     467,745 ±     7,260  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures       1  thrpt   20  173071,881 ± 10004,550  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures      10  thrpt   20    5359,828 ±    50,552  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures     100  thrpt   20     592,051 ±    12,298  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures    1000  thrpt   20      61,786 ±     0,341  ops/s

Code proposed for the problem (2) in the mentioned PR:

Benchmark                                           (rows)   Mode  Cnt       Score       Error  Units
BatchInsertBenchmark.executeLargeBatch                   1  thrpt   20  151813,391 ±  4513,336  ops/s
BatchInsertBenchmark.executeLargeBatch                  10  thrpt   20   24564,676 ±   655,469  ops/s
BatchInsertBenchmark.executeLargeBatch                 100  thrpt   20    2480,789 ±    11,202  ops/s
BatchInsertBenchmark.executeLargeBatch                1000  thrpt   20     233,532 ±    19,691  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures       1  thrpt   20  164892,827 ± 10209,532  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures      10  thrpt   20    5216,781 ±    58,656  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures     100  thrpt   20     568,871 ±     7,134  ops/s
BatchInsertBenchmark.executeLargeBatchManyFailures    1000  thrpt   20      57,583 ±     0,750  ops/s

@katzyn katzyn mentioned this pull request Feb 4, 2024
Closed
turbanoff
turbanoff reviewed Feb 4, 2024
View reviewed changes
h2/src/main/org/h2/command/Command.java
}
}

private ResultWithGeneratedKeys executeUpdate(Object generatedKeysRequest, boolean commitIfAutoCommit) {
Copy link
Contributor

@turbanoff turbanoff Feb 4, 2024

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Parameter commitIfAutoCommit is unused

Copy link
Contributor Author

@katzyn katzyn Feb 4, 2024

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Good catch, I changed it temporarily and forgot to revert that change back. I've updated results of benchmark, insertions with many elements in the batch are now faster.

turbanoff
turbanoff reviewed Feb 5, 2024
View reviewed changes
h2/src/main/org/h2/jdbc/JdbcStatement.java
checkClosed();
if (batchCommands == null) {
batchCommands = new ArrayList<>();
closeOldResultSet();
Copy link
Contributor

@turbanoff turbanoff Feb 5, 2024

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

Are you sure closeOldResultSet(); this shouldn't be called always? As we do in other methods.

Copy link
Contributor Author

@katzyn katzyn Feb 5, 2024

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

In other cases batchCommands has at least one element and old results will be cleared by executeUpdateInternal() anyway.

@katzyn katzyn merged commit 4497462 into h2database:master Feb 5, 2024
@katzyn katzyn deleted the batch branch February 5, 2024 10:12
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Reviewers

1 more reviewer

@turbanoff turbanoff turbanoff left review comments

Reviewers whose approvals may not affect merge requirements

Assignees

No one assigned

Labels

None yet

Projects

None yet

Milestone

No milestone

Development

Successfully merging this pull request may close these issues.

2 participants

@katzyn @turbanoff