> For the complete documentation index, see [llms.txt](https://mariadb.com/docs/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mariadb.com/docs/connectors/mariadb-connector-j/option-batchmultisend-description.md).

# Option batchMultiSend Description

### Definition

Since 1.5.0, the "useBatchMultiSend" option permits sending queries by batch. If disabled, queries are sent one by one, waiting for the result before sending the next one. If enabled, queries will be sent by batch corresponding to the value of the useBatchMultiSendNumber option (default 100). Results will be read after a while, avoiding a lot of network latency when the client and the server aren't on the same host. This option is only used for JDBC executeBatch(). This option is particularly efficient when the client is distant from the server.

Here is a benchmark using a client and server on 2 different hosts (ping of 0.350 ms between 2 hosts):

![use\_batch\_multi\_send](/files/gPb1n816Msg4agbltQUg)

### Standard client-server protocol

By default, the driver interacts with the server using a request–response messaging pattern.

```mermaid
sequenceDiagram
    accTitle: Standard client-server request-response protocol
    accDescr {
        The client sends a query to the server and then blocks, waiting for a response.
        The server processes the query and sends the results back to the client before
        the client can send another request.
    }
    participant C as Client
    participant S as Server
    C->>S: send QUERY
    S-->>C: receive results
```

*Standard protocol: the client blocks after sending a query until the server's results arrive.*

Once the driver sends data, it will remain blocked until data becomes available from the input socket.

### Batch multi-send communication

JDBC permits batching.\
Example:

```java
PreparedStatement preparedStatement = connection.prepareStatement("INSERT INTO test(data1, data2) VALUES (?, ?)");
    for (int i = 0; i < 3; i++) {
        preparedStatement.setInt(1, i);
        preparedStatement.setString(2, "value" + i);
        preparedStatement.addBatch();
    }
    preparedStatement.executeBatch();
```

When the `useBatchMultiSend` option is turned off, batches are processed sequentially, sending each item individually using the traditional request–response messaging pattern.\
Below is an example with a prepared statement (`useServerPrepStmts` enabled):

```mermaid
sequenceDiagram
    accTitle: Standard batch execution with useBatchMultiSend disabled
    accDescr {
        The application submits a batch of statements to the client. The client first
        sends COM_STMT_PREPARE and waits for COM_STMT_PREPARE_OK from the server. It then
        sends each COM_STMT_EXECUTE one at a time, waiting for that item's result before
        sending the next: first data, then second data, then third data, each followed by
        its own result. After the third result is received, the client returns the
        combined results to the application.
    }
    participant C as Client
    participant S as Server
    Note over C: Application batch
    C->>S: send COM_STMT_PREPARE
    Note over S: PREPARING query
    S-->>C: receive COM_STMT_PREPARE_OK
    C->>S: send COM_STMT_EXECUTE for first data
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE first result
    C->>S: send COM_STMT_EXECUTE for 2nd data
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE 2nd result
    C->>S: send COM_STMT_EXECUTE for 3rd data
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE 3rd result
    Note over C: return results
```

*Standard batch (`useBatchMultiSend` disabled): each statement is prepared once, then executed and acknowledged one at a time before the next is sent.*

Same example with "useBatchMultiSend" enabled. Requests are sent in bulk, saving network latency:

```mermaid
sequenceDiagram
    accTitle: Bulk batch execution with useBatchMultiSend enabled
    accDescr {
        The application submits a batch of statements to the client. The client sends
        COM_STMT_PREPARE and waits for COM_STMT_PREPARE_OK, as in the standard case. It
        then sends four COM_STMT_EXECUTE requests back to back, without waiting for any
        result in between. The server processes each EXECUTE query and streams back four
        results in order: first, second, third, and fourth. Only after all four results
        have arrived does the client return the combined results to the application.
    }
    participant C as Client
    participant S as Server
    Note over C: Application batch
    C->>S: send COM_STMT_PREPARE
    Note over S: PREPARING query
    S-->>C: receive COM_STMT_PREPARE_OK
    loop send COM_STMT_EXECUTE for 4 executions
        C->>S: send COM_STMT_EXECUTE
    end
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE first result
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE 2nd result
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE 3rd result
    Note over S: EXECUTE query
    S-->>C: receive COM_STMT_EXECUTE 4th result
    Note over C: return results
```

*Bulk batch (`useBatchMultiSend` enabled): all EXECUTE requests are sent up front, and results stream back afterward, saving round-trip latency.*

Advantages:

* a lot more efficient.

Inconvenient:

* If an error occurs and "continueBatchOnError" is disabled (default enabled), some other data may have already been sent and executed.

**Bulk split**

Data is sent in batches, with each batch size determined by the `useBatchMultiSendNumber` value.\
Once the first send command is issued, reads begin asynchronously. The driver waits until all results for the current batch are received before sending the next batch.

{% @marketo/form formId="4316" %}