Configuring the system to cope with the low performance of PostgreSQL INSERT

Currently, based on the PostgreSQL ODBC driver (only one could work with multi-line prepared statements)

library (odbc);
cdb <- DBI :: dbConnect (drv = odbc :: odbc (), driver = "PostgreSQL", ...);
on.exit ({DBI :: dbDisconnect (cdb);}, add = TRUE);
DBI :: dbBegin (cdb);
sth <- DBI :: dbSendStatement (cdb, paste (rep ("INSERT INTO  (, ...) VALUES (?, ...,?); ", 100), collapse =" ")); # Link up to 8,000 placeholders at the same time (ref in the source?) - use 100 multi-line statements in this example
DBI :: dbBind (sth, bvallist); # Here, & # 39; bvallist & # 39; is a list of values ​​to be linked in the prepared statement of several lines - they have tested and verified that the values ​​in the database are correct after INSERT
num_recs <- DBI :: dbGetRowsAffected (sth);
DBI :: dbClearResult (sth);
DBI :: dbCommit (cdb);

  • NB I have also used the following with similar performance: (A) prepared statements with only one INSERT (both in odbc Y RPostgres controllers) as well as (B) assemble the multi-line INSERT SQL string without the use of prepared statements.