How to improve import speed on SQL Workbench/J

Question

Tried like below, but it imports terribly slow, with speed 3 rows/sec

WbImport -file=c:/temp/_Cco_.txt
         -table=myschema.table1
         -filecolumns=warehouse_id,bin_id,cluster_name
         ---deleteTarget
         -batchSize=10000
         -commitBatch 

Answer 1

WbImport uses INSERT to load data. This is the worst way to load data into Redshift.

You should be using the COPY command for this as noted in the Redshift documentation:

"We strongly recommend using the COPY command to load large amounts of data. Using individual INSERT statements to populate a table might be prohibitively slow."

Answer 2

WbInsert can use the COPY API of the Postgres JDBC driver.

To use it, use

WbImport -file=c:/temp/_Cco_.txt
         -usePgCopy
         -table=myschema.table1
         -filecolumns=warehouse_id,bin_id,cluster_name

The options -batchSize and -commitBatch are ignored in that case, so you should remove them.

SQL Workbench/J will then essentially use the equivalent of a COPY ... FROM STDIN. That should be massively faster than regular INSERT statements.

This requires that the input file is formatted according to the requirements of the COPY command.