Batching SQL INSERTs for Performance

When a channel inserts rows one at a time, every INSERT is a separate database round-trip. At volume that overhead dominates, a load a native bulk import finishes in 30 minutes can take hours one row at a time. Batching many rows into a single multi-row INSERT collapses those round-trips and recovers most of the lost throughput.

Group rows into a single message

On the source node, set Record Grouping to Row Count and choose how many rows to include in each group (see the DB Query Result source). Each message delivered to the channel then contains that many rows instead of one, so a single mapping or custom-script node can emit one INSERT for the whole batch.

Size the group to stay under the database's maximum statement/packet size. MySQL and MariaDB, for example, reject statements larger than max_allowed_packet (commonly 64 MB). A safe group size is roughly that limit divided by the average serialized row size.

Build a multi-row INSERT

Walk the rows in the grouped message, append one VALUES tuple per row, then execute the whole statement once with qie.doUpdateQuery.

var sql = new java.lang.StringBuilder();
sql.append('INSERT INTO patient_visit (patient_id, ticket_number, visit_id) VALUES ');

var rowCount = message.getRowCount();
for (var i = 1; i <= rowCount; i++) {
   if (i > 1) {
      sql.append(',');
   }
   sql.append("('")
      .append(message.getNode('patient_id', i))
      .append("','")
      .append(message.getNode('ticket_number', i))
      .append("','")
      .append(message.getNode('visit_id', i))
      .append("')");
}

qie.doUpdateQuery('myDatabase', sql.toString());   // one round-trip for the whole batch

qie.doUpdateQuery returns the number of rows affected. The manual concatenation above does not escape the values it interpolates. See the caution below.

Alternative: a per-row template with SQL escaping

Defining the tuple as a template and evaluating it per row keeps the formatting out of the loop body and, by passing 'sql' as the escapeFor argument, escapes each value safely:

var rowTemplate = "('{p:patientId}','{p:ticket}','{p:visitId}')";
var sql = new java.lang.StringBuilder(
   'INSERT INTO patient_visit (patient_id, ticket_number, visit_id) VALUES ');

for (var i = 1; i <= message.getRowCount(); i++) {
   if (i > 1) {
      sql.append(',');
   }
   var params = qie.newParameterMap();
   params.put('patientId', message.getNode('patient_id', i));
   params.put('ticket',    message.getNode('ticket_number', i));
   params.put('visitId',   message.getNode('visit_id', i));

   sql.append(qie.evaluateTemplate(rowTemplate, params, 'sql'));
}

qie.doUpdateQuery('myDatabase', sql.toString());

Escape values that come from message data

Both examples build SQL by interpolating message values into the statement. Always escape those values. Pass 'sql' as the escapeFor argument to qie.evaluateTemplate (as in the second example), or escape each value yourself. Concatenating raw, unescaped message content into a SQL string risks broken statements and SQL injection.