executeBatch()` on a prepared INSERT runs s are ~1–2 orders of magnitude slower than the Appender

Start with executeBatchedPreparedStatement() in DuckDBPreparedStatement.java around L727–749 and the result handling around L233–238. Trace execute_prepared_statement in src/jni/duckdb_java.cpp and PreparedStatement::Execute in src/main/prepared_statement.cpp, then compare the InternalAppender path. Done means the proposed batching and opt-in insert rewrite preserve JDBC errors and fallback behavior while removing avoidable per-row overhead.

PreparedStatement.executeBatch() is currently a convenience emulation: it executes every batched parameter row as an individual statement execution, each paying a JNI crossing, per-value parameter conversion, a full query lifecycle in the DuckDB core, and (on the way back) a freshly built result-set metadata object and DuckDBResultSet — per row. For plain parameter-only INSERTs, the same data written through DuckDBAppender bypasses all of it and is dramatically faster.

Analysis today

1. Java loop — executeBatchedPreparedStatement() calls execute() once per batched parameter row ([DuckDBPreparedStatement.java ~L727–749]). The only batching benefit is that the loop is wrapped in a single transaction (startTransaction()), which saves the per-row commit/WAL flush.

2. Per-row result machinery — every execute() call then builds a DuckDBResultSetMetaData (JNI string arrays for column names/types via build_meta) and a new DuckDBResultSet, only to read the changed-rows count out of it ([DuckDBPreparedStatement.java ~L233–238]). For a batch of N rows that is N metadata objects and N result sets of garbage whose only payload is a long.

3. Per-row JNI + parameter conversion — _duckdb_jdbc_execute converts each parameter individually via GetObjectArrayElement + to_duckdb_value (an instanceof chain per value) before calling stmt->Execute(params) ([src/jni/duckdb_java.cpp, execute_prepared_statement]).

4. Full statement lifecycle in the core — PreparedStatement::Execute is not a lightweight re-execute: it constructs an ExecuteStatement and sends it through ClientContext::RunInternalStatement (duckdb core, src/main/prepared_statement.cpp), i.e. pending query, executor, pipeline scheduling, PhysicalInsert on a 1-row chunk, and a materialized result — per row.

By contrast, DuckDBAppender writes values into a 2048-row data chunk through direct ByteBuffers and hands the whole chunk to the storage layer via duckdb_append_data_chunk — no planner, no executor, one JNI crossing per chunk instead of several per row. Constraints (NOT NULL, PK/unique via index append) are still enforced at flush, so correctness-wise the storage path is equivalent for plain inserts.

Proposed

1. Stop building metadata/result sets per row for CHANGED_ROWS results (semantics-preserving). When the statement returns a row count, return it natively as a long from the JNI call instead of constructing DuckDBResultSetMetaData + DuckDBResultSet per execution. This helps every update/insert, not just batches.

2. Batch bind across JNI (semantics-preserving). Add a native entry point that carries all N parameter rows of a batch in one call and loops on the C++ side. This amortizes the JNI crossing and the per-value GetObjectArrayElement round trips while keeping the executor path and exact JDBC error semantics.

3. Opt-in rewrite of batched parameter-only INSERTs to the internal appender, behind a connection property (precedent: MySQL Connector/J rewriteBatchedStatements=true). In the JNI layer, detect a bare parameter-only single-table INSERT; if it matches, drain the batch through an InternalAppender chunk inside the surrounding transaction; on any deviation (cast required, ON CONFLICT/RETURNING, partial columns) fall back to the current path. Opt-in because failure attribution within a batch changes (chunk-level instead of statement-level).