How to improve import speed on SQL Workbench/J

0
votes

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
2
amazon-redshiftsql-workbench-j
Which DBMS product and JDBC driver are you using? Are you maybe sending that over a slow network connection? Do you have many indexes defined on the confidential table? What are the data types of those three columns? - a_horse_with_no_name
Relational DB, jdbc:redshift, warehouse network with good bandwidth, "confidental" include only 2 indexes , 3 x varchar data type. - klisim

2 Answers

1
votes

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.

0
votes

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."