0

I have a spring batch with flows, steps, readers, processors, writers which is copying data from one DB source to another (being an Oracle DB). The batch is working fine and fast with some millions of data. I now want to reuse this same exact method below to write the same data in a PostgreSQL DB hence the getSessionFactory() method call returning the appropriate session factory depending on my needs. But the problem is that nothing seems to happen or it is just anormally very long.

I would like to know why there is such a difference. Is there some kind of limitation on the number of inserts we can do in parallel with PostgreSQL? Or is there something wrong with this code?

public void myMethod(List<MyObject> list) {
    SessionFactory sessionFactory = getSessionFactory();
    sessionFactory.getCurrentSession().doWork(connection -> {
        StringBuilder stringBuilder = new StringBuilder("INSERT INTO \"SOME_TABLE\" (\"ID\", \"SOME_COLUMN\", \"UPDATED_AT\") VALUES (?, ?, ?)");
        try (PreparedStatement preparedStatement = connection.prepareStatement(stringBuilder.toString())) {
            int i = 0;
            for (MyObject myObject : list) {
                preparedStatement.setBigDecimal(1, new BigDecimal(myObject.getId()));
                preparedStatement.setBigDecimal(2, new BigDecimal(myObject.getSomeColumnValue()));
                preparedStatement.setDate(3, new Date(System.currentTimeMillis()));
                preparedStatement.addBatch();
                if (++i % 500000 == 0 || i == list.size()) {
                    preparedStatement.executeBatch();
                }
            }
        }
    });
}
David B.
  • 81
  • 2
  • 9
  • 4
    Same cause as this maybe? https://stackoverflow.com/q/37942063 –  Jan 16 '19 at 12:27
  • Thank you for your answer, it must explain the cause of my problem for sure. However I am trying to know where I could possibly put your line ```System.setProperty("spring.jdbc.getParameterType.ignore", "true"); ```. The entry point of my batch is in a ```private static void main``` method, executing something like: ```SpringApplication.run(Application.class, args);``` – David B. Jan 16 '19 at 12:48
  • If you are doing straight inserts, check out the copy command in PostgreSQL. It is notionally similar to SQL Loader in Oracle. Maybe Spring has an interface for it. – Hambone Jan 16 '19 at 12:51
  • 1
    Possible duplicate of [Slow insert on PostgreSQL using JDBC](https://stackoverflow.com/questions/37942063/slow-insert-on-postgresql-using-jdbc) – William Robertson Jan 16 '19 at 20:34
  • @WilliamRobertson That question and its answer are specifically about using spring-jdbc though, which is not used here. – Mark Rotteveel Jan 17 '19 at 19:14
  • Ah OK. Vote retracted. – William Robertson Jan 17 '19 at 19:39
  • @DavidB. Try any of these options to set the System property. https://stackoverflow.com/questions/36895711/can-i-define-system-properties-within-spring-boot-configuration-files – Shankar Jan 18 '19 at 23:45

0 Answers0