CODEWITHSUNDEEP
javamysql
CBozanic
CBozanic

Reputation: 11

Slow INSERT into SQL using JAVA

I am trying to read, split into meaningful variables and then insert the information of a 4Gb text file into a database. The reading (and printing when required) of a sample 1.2Mb takes few hundred mili seconds but inserting the information into the database takes many seconds (30+). I am using two databases (the file contains two different types of records). My code looks something like this:

    try {
    reader = new BufferedReader(new FileReader(filename));
    openConnection();
    ch = reader.read();
    while (ch != -1) {
        if ((char)ch == '1') {
            sql = parseCliente();
            st.executeUpdate(sql);
        }
        else if ((char)ch == '2') {
            sql = parseRegistro();
            st.executeUpdate(sql);
        }
        else if (ch == -1) break;
        else {
            ch = reader.read();
        }
    }
    closeConnection();
}
catch (IOException e) {
    e.printStackTrace();
} catch (SQLException e) {
    e.printStackTrace();
}

My code actually works but I need a faster method or the operation would take days!

Upvotes: 0

Views: 475

Answers (1)

CBozanic
CBozanic

Reputation: 11

I changed strategy and loaded the processed data into a CSV file and from there I transferred it to the DB using the LOAD DATA INFILE command. I went from 37 seconds to less than 1 sec for my sample.

Upvotes: 1

Related Questions