Batch inserts are a useful technique when inserting large numbers of rows into a database at once. Rather than inserting each row individually, we can send a batch to the database at once, avoiding lots of individual network message round-trips and other per-statement inefficiencies. The performance improvements of using batching can be quite considerable. Sometimes database drivers support batching natively, in which case one can create a single prepared statement and then add multiple sets of bind parameters to it before executing the batch. For instance, in JDBC you can do the following:
PreparedStatement stmt = ...; stmt.setString(1, "..."); stmt.setInt(2, 42); ... stmt.addBatch(); // Later, after adding multiple sets of bind parameters we can do: int[] returnCodes = stmt.executeBatch();
I (NEM) am currently doing some work with PostgreSQL that will be inserting very large numbers of rows (many millions), and I like to knock up prototypes in Tcl as a quick way to get to grips with the data. As far as I can tell, neither TDBC nor Pgtcl natively support any kind of batch API. However, we can fake it on top of normal prepared statements by making use of SQL multi-row VALUES expressions [L1 ]:
INSERT INTO some_table VALUES (a, b, c), (d, e, f), ...
By preparing a statement with placeholders for a large number of rows we can then build up the set of bind parameter values and execute in one go, getting the same benefits as for a batch API. As a test case, we will insert data from the worldcitiespop.txt (41.9MB) [L2 ] CSV file that contains roughly 3.2 million rows. (NB if you really just want to load a CSV into Postgres, consider the "COPY ... FROM ..." command, which is much faster than any other methods).
We will use TDBC for simplicity, although I would say that Pgtcl is significantly faster in my testing. The test code is as follows:
package require tdbc::postgres
set BATCH_SIZE 1
set COLUMNS {country city accentcity region population latitude longitude}
tdbc::postgres::connection create db ;# specify -user/-host etc if necessary
puts "Creating/truncating table"
db allrows {
CREATE TABLE IF NOT EXISTS world_cities(
country text,
city text,
accentcity text,
region text,
population integer,
latitude decimal,
longitude decimal
)
}
db allrows { TRUNCATE world_cities }
# Prepare a batch insert statement of the given size
proc prepareBatchInsert {table columns batchSize} {
set sql "INSERT INTO ${table}([join $columns ,]) VALUES "
for {set i 0} {$i < $batchSize} {incr i} {
if {$i > 0} { append sql , }
set tuple [list]
foreach column $columns {
lappend tuple [format {:%s%d} $column $i]
}
append sql ([join $tuple ,])
}
db prepare $sql
}
set batchStmt [prepareBatchInsert world_cities $COLUMNS $BATCH_SIZE]
set in [open worldcitiespop.txt]
chan configure $in -encoding iso8859-1
gets $in header
set start [clock seconds]
db transaction {
set i 0
set batch [dict create]
while {[gets $in line] >= 0} {
foreach column $COLUMNS value [split $line ,] {
if {$value ne ""} {
dict set batch $column$i $value
}
}
if {[incr i] % $BATCH_SIZE == 0} {
$batchStmt execute $batch
set batch [dict create]
#puts -nonewline [format "\r%8d" $i]
#flush stdout
}
}
if {[dict size $batch] > 0} {
# Prepare a batch for the remaining elements
set remaining [expr {$i % $BATCH_SIZE}]
set stmt [prepareBatchInsert world_cities $COLUMNS $remaining]
$stmt execute $batch
}
}
set end [clock seconds]
set time [expr {$end - $start}]
close $in
db foreach row {select count(1) from world_cities} { set rows [dict get $row count] }
puts "\nInserted $i/$rows rows in $time seconds ([expr {$rows/$time}] rows/second)"
db closeThe results for a BATCH_SIZE of 1 (i.e. no batching) vs 1000 are as follows:
Batch size 1 : Inserted 3173958/3173958 rows in 1483 seconds (2140 rows/second) Batch size 1000: Inserted 3173958/3173958 rows in 108 seconds (29388 rows/second)
As we can see, batching provides an order of magnitude performance improvement vs unbatched inserts. With Pgtcl I can get better still - 63479 rows/second - but the code is significantly more complex due to the lower-level API.
Harm Olthof 2017-08-16: note that each time your loop executes $stmt execute $batch a record set is created which allocates memory. This could have impact on your perfomance (although you seem to have a very high performing Postgres server). If you change your code as follows, you should get more inserts per second:
set rs [$batchStmt execute $batch] incr rows [$rs rowcount] $rs close