Spring jdbcTemplate vs PreparedStatement. Performance difference

Question

I am using Oracle 11g. I have 3 tables(A,B,C) in my database A <one-many> B <many-one> C I have a piece of code, that performs three inserts: firstly in A and C, after that in B. This piece of code is executed a lot of times(200000) and makes 200000 insert operations in each table.

I have two ways to make an insertion:

1. jdbc PreparedStatement:

   DataSource ds = jdbcTemplate.getDataSource();
   
   try (Connection connection = ds.getConnection();
        PreparedStatement statement = connection.prepareStatement(sql1);
        PreparedStatement statement2 = connection.prepareStatement(sql2);
        PreparedStatement statement3 = connection.prepareStatement(sql3);) {
   
        connection.setAutoCommit(false);
        final int batchSize = 20;
        int count = 0;
   
        for (int i=1; i<= total; i++ ) {
   
            // Define sql parameters
   
            statement.setString(1, p1);
   
            statement2.setString(1, p2);
            statement2.setString(2, p3);
   
            statement3.setInt(1, p4);
            statement3.setString(2, p5);
   
            statement.addBatch();
            statement2.addBatch();
            statement3.addBatch();
   
            if (++count % batchSize == 0) {
                       statement.executeBatch();
                       statement.clearBatch();
                       statement2.executeBatch();
                       statement2.clearBatch();
                       statement3.executeBatch();
                       statement3.clearBatch();
                       connection.commit();
                       System.out.println(i);
            }
         }
   
         statement.executeBatch();
         statement.clearBatch();
         statement2.executeBatch();
         statement2.clearBatch();
         statement3.executeBatch();
         statement3.clearBatch();
         connection.commit();
      } 
      catch (SQLException e) {
          e.printStackTrace();
      }
   }
   

2. Spring jdbcTemplate:

    List<String> bulkLoadRegistrationSql = new ArrayList<String>(20);
   
    for (int i=1; i<= total; i++ ) {
   
        // 1. Define sql parameters p1,p2,p,3p4,p5
   
        // 2. Prepare sql using parameters from 1
        String sql1String = ...
        String sql2String = ...
        String sql3String = ...
   
        bulkLoadRegistrationSql.add(sql1String);
        bulkLoadRegistrationSql.add(sql2String);
        bulkLoadRegistrationSql.add(sql3String);
   
        if (i % 20 == 0) {
             jdbcTemplate.batchUpdate(bulkLoadRegistrationSql
                 .toArray(new  String[bulkLoadRegistrationSql.size()]));
             //Clear inserted batch
             bulkLoadRegistrationSql = new ArrayList<String>(20);     
        }
   
   }
   

I measured execution time for total = 200000 and results are very confusing for me. Spring jdbcTemplate is executed in 1480 seconds, jdbc PreparedStatement in 200 seconds

I looked into jdbcTemplate source and found, that it uses Statement underneath, which should be less efficient than PreparedStatement. However the difference in results is too big and I am not sure if this happens just because of the difference between Statement and PreparedStatement. What are your ideas on that? Should the results theoretically be equaled if jdbcTemplate is replaced on namedParameterJdbcTemplate?

Answer 1

Yes it should be much closer, assuming the majority of the time was spent waiting for the responses from the database. Spring has its own overhead so you will have some more resource consumption on the client side.

In a prepared statement using placeholders, Oracle only parses the SQL once, and generates the plan once. It then caches the parse results, along with the plan for the SQL. In your JDBCTemplate example, each SQL statement looks different to the parser and will therefore require a full parse and plan generation by the server. Depending on your Oracle server's horsepower, this will result in an increased response time for each SQL statement. For 200,000 SQL statements, a net increase of 1280 seconds translates into an additional 6.4 milliseconds per call. That, to me, seems like a reasonable increase due to the additional parsing required.

I suggest adding some timing information to the database calls, so you can confirm that the SQL response time is lower in the improved version.

Answer 2

Spring JDBCTemplate also has methods that use prepared Statement. Please Refer this link Your test results make sense - you cannot compare execution using prepared statement and Ordinary SQL.

There are overloaded batchUpdate methods in Spring JDBCTemplate that uses Prepared statements for example the below function.

int[][] batchUpdate(String sql, final Collection batchArgs, final int batchSize, final ParameterizedPreparedStatementSetter pss)