Optimize Mysql table Insert - Java

Question

I have a loop doing millions of rows sequential Insert in a single Mysql table.

I would like to know if it is possible to parallelize Insert or use some resource that increases insertion performance.

Code:

 public static java.sql.Connection getConexaoMySQL() {       
    //atributo do tipo Connection 
    try { 
        String driverName = "com.mysql.jdbc.Driver"; 
        Class.forName(driverName);  
        String serverName = "localhost";  
        String mydatabase ="tweets"; 
        String url = "jdbc:mysql://" + serverName + "/" + mydatabase; String username = "root";  
        String password = "admin";  
        connection = DriverManager.getConnection(url, username, password); 
        if (connection != null) { 
            status = ("Banco de Dados--->Conectado com sucesso!"); 
        } 
        else { 
            status = ("Banco de Dados--->Não foi possivel realizar conexão"); 
        } 
        return connection; 
    } 
    catch (ClassNotFoundException e) 
    { 
        System.out.println("O driver expecificado nao foi encontrado."); 
        return null; 
    } catch (SQLException e) {  
        System.out.println("Nao foi possivel conectar ao Banco de Dados."); 
        return null; 
    } 
} 

public static void insert(List<TweetsDB> list){
    for (TweetsDB x : list) { 

        preparedStmt.setString (1, x.getCandidate);
        preparedStmt.setString (2, x.getIDTweet);
        preparedStmt.setString (3, x.getIDUser);
        preparedStmt.setString (4, x.getUserScreenName);
        preparedStmt.setString (5, x.getUserName);
        preparedStmt.setString (6, x.getRetweets);
        preparedStmt.setTimestamp(7, x.getDate);
        preparedStmt.setString (8, x.getText);
        preparedStmt.setString (9, x.getHashtags);

        // execute the preparedstatement
        preparedStmt.execute();
    }   
}

Answer 1

If you have a looping and are using java can separate the task into threads according to the amount of connection pool Voce can use in the database.

If you need to store the status of each Insert this is the best option.

But if it ensures that there are no errors beyond the use of threads you can create a kind of buffer that sends each time 10k Inserts...

---

First run the query SHOW VARIABLES;

Look for the connection limit variable, it must be max_connections

This is the limit of connections that the database can handle at the same time.

---

Then deploy the threads in your java code

final int maxPool = 150;


public void executa(){
    final List<List<String>> listaTodosInserts = divideInserts(maxPool, "arquivoCSV");

    for (List<String> listaInserts : listaTodosInserts ){
        insert(listaInserts);
    }
}


public void insert(final List<String> listaInserts){
    new Thread() {
       public void run() {

           for (String insert : listaInserts){
               // preparedStmt.execute(insert);
           }
       }
    }.start();      
}

Answer 2

By Batch and faster because you don’t keep calling run inside the for in each iteration, but preparedStatement.addBatch(); and after the for:

  preparedStatement = conn.prepareStatement(comandoSQL);
    for(ProdutoPrecoVenda produtoPrecoVenda: produto.getProdutosPrecoVenda()) {

        preparedStatement.setQueryTimeout(30);
        preparedStatement.setLong(1, produtoPrecoVenda.getTabelaPreco().getId()); 
//id tabela de preco
        preparedStatement.setLong(2, produto.getId()); //id produto

        preparedStatement.addBatch();

    }
   try {
        preparedStatement.executeBatch();
        preparedStatement.clearBatch();
    } catch (Exception e) {
        System.out.println("ERRO INCLUIR PRODUTO PRECO VENDA:" + produto.getCodigoPrincipal());
        throw e;
    }finally {
        preparedStatement.close();
    }