Extremely Slow Netezza (Database) Batch Inserts

Question

I am using batch inserts and prepared statements to insert data into Netezza. However, the performance is extremely slow. Below is my code:

final int batchSize = 1000;
int count = 0;
for (final MyClass object: myList) {
    ps.setString(1, object.getOne());
    ps.setString(2, object.getTwo());
    ps.setString(3, object.getThree());
    ps.setString(4, object.getFour());
    ps.setString(5, object.getFive());
    ps.setString(6, object.getSix());
    ps.setString(7, object.getSeven());
    ps.setString(8, object.getEight());
    ps.setString(9, object.getNine());
    ps.setString(10, object.get10());
    ps.addBatch();
    if(++count % batchSize == 0) {
        ps.executeBatch();
    }
}
ps.executeBatch(); // insert remaining records

Does anyone know how I could speed this up?

Answer 1

Insert statement batches will not be performant on Netezza the overhead per query will limit the efficiency of these operations. I recommend dumping your data to flat files and executing a nzload/external table insert For an example please refer to Transient External Tables.