Permanent Connection

By establishing the connection once outside the loop, we do not pay the overhead of establishing the connection on every single row insertion. Notice also, that the connection is performed within the parenthesis in the try what means that will be automatically closed when exiting the try block for whatever reason. This is an important good practice that prevents from keeping unused open connections with the database.

    public void ingestData() {
        try (LoansReader csv = new LoansReader();
             Connection connection = utils.connect();
             Statement stmt = connection.createStatement())
        {
            Loan loan;
            while ((loan = csv.read()) != null) {
                String sql = utils.getInsertSql(loan);
                stmt.executeUpdate(sql); //insert the row
                connection.commit();
                stats.send(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

Problem:

If we examine the code further, we notice that we are executing the SQL statement without first preparing it:

                stmt.executeUpdate(sql); //insert the row

Why is this a problem? Let us first take a look at the work that is done by the execute. First, the SQL statement is compiled and a first query plan is generated as a result. Then the optimizer performs a series of transformations on the initial query plan to get the final query plan. This work is repeated over and over again for every single row insert and is quite expensive compared to a row insert.

Test 3 Error: Unprepared SQL Statements

The solution lies in eliminating the compilation and optimization step by preparing the SQL statement before the insert loop.