CODEWITHSUNDEEP
javamysqlsqldatabaseperformance
John Roberts
John Roberts

Reputation: 5986

MySQL Batch Inserts Inserting Very Slowly

try (BufferedReader br = new BufferedReader(new FileReader("LoadData.txt"))){
        final Connection connection = DriverManager.getConnection("jdbc:mysql://ipAddress:port/database?user=username&password=password&useServerPrepStmts=false&rewriteBatchedStatements=true");
        String sCurrentLine;
        final PreparedStatement ps = connection.prepareStatement("insert ignore into emails (email) value (?)");
        int count = 0;
        final int batchSize = 1000;
        while ((sCurrentLine = br.readLine()) != null) {
            ps.setString(1, sCurrentLine);
            ps.addBatch();
            if(++count % batchSize == 0) {
                ps.executeBatch();
            }
        }
        ps.executeBatch();
        ps.close();
        connection.close();
    } catch (final IOException e) {
        e.printStackTrace();
}

Even with the above code, batch inserting is extremely slow, taking a couple of minutes per each 1000 rows. Does anyone know what's wrong?

Upvotes: 0

Views: 84

Answers (1)

John Roberts
John Roberts

Reputation: 5986

Posting an answer in case this messes with someone else in the future:

My SQL query had the keyword "value". The keyword must be "values" for the batch inserts to work.

Upvotes: 1

Related Questions