Generally, SQL databases perform best when working on the whole
problem at once0. Instead of selecting row-by-row or—god forbid—joining tables
in the application code, we should use a single query to fetch all the
required data—and nothing more—at once. The very same is true for modifying
data: to delete three rows we should use one
delete statement with the right
clause. But how about inserting three rows at
As a matter of fact there is a very old SQL feature making this
clause can list multiple rows:
INSERT INTO tbl ( c1 , c2 , c3 ) VALUES ('r1c1', 'r1c2', 'r1c3') , ('r2c1', 'r2c2', 'r2c3') , ('r3c1', 'r3c2', 'r3c3')
The main drawback of this approach is that the number of rows affects the query string itself as well as the number and position of the bind parameters1. That's no big deal for databases—nowadays, almost all SQL is executed as “dynamic SQL” anyway. Nevertheless it might be a big deal in your code base. Using a batch API is therefore better in the general case2.
Besides the ease of use, the two approaches have also a semantic difference: most batch APIs just execute the statement multiple times in a latency optimized manner3. That means, each execution can fail independently of each other. If one out of three rows breaks a constraint, the other two will still get inserted.
Inserting multiple rows with a single statement is different: the SQL standard implies a all-or-nothing semantic for single statements4. If one out of three rows in the example above breaks a constraint, none will be inserted. Depending on your requirements, this may or may not be the desired behavior.
This semantic difference also affects statement level triggers: they'll fire multiple times when using a typical batch API, but only once when inserting multiple rows with a single statement.
To conclude this topic: inserting multiple rows with a single
insert statement is useful if the all-or-nothing semantic
is desired and the number of rows is fixed or at least within known
boundaries. In almost all other cases, batch APIs offer a better approach to
reduce network latency.
As mentioned above, using a batch API is the common way to cut
latencies. One very nice thing about most batch APIs is that they also