Java-MySQL: How to create a Stored Procedure

Question

I Work on a Java 1.7 project with Mysql,

I have a method that insert a lot of data in a table with PreparedStatement, but this cause a Out Of Memory Error in the GlassFish Server.

Connection c = null;
    String query = "INSERT INTO users.infos(name,phone,email,type,title) "
            + "VALUES (?, ?, ?, ?, ?, ";
    PreparedStatement statement = null;
    try {
        c = users.getConnection();
        statement = c.prepareStatement(query);
    } catch (SQLException e1) {
        // TODO Auto-generated catch block
        e1.printStackTrace();
    }

try{
        int i =0;
        for(Member member: members){
            i++;
            statement.setString(1, member.getName());
            statement.setString(2, member.getPhone());
            statement.setString(3, member.getEmail());
            statement.setInt(4, member.getType());
            statement.setString(5, member.getTitle());
            statement.addBatch();
            if (i % 100000 == 0){
                statement.executeBatch();
            }
        }
        statement.executeBatch();
    }catch (Exception ex){
        ex.printStackTrace();
    } finally {
        if(c != null)
        {
            try {
                c.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        if(statement != null){
            try {
                statement.close();
            } catch (SQLException e) {
                // TODO Auto-generated catch block
                e.printStackTrace();
            }
        }
        c = null;
        statement = null;
    }

I think that I need to create a Stored Procedure to avoid this memory issue, but I don't know where to start and if I should create a procedure or a function, and if I will be able to get some kind of response in a return or something?

What do you think about it?

How would creating a stored procedure help? You need to use a **transaction** and **smaller batches**. — Boris the Spider, Aug 23 '16 at 15:11
Try a smaller batch and then look into [`try-with-resources`](https://docs.oracle.com/javase/tutorial/essential/exceptions/tryResourceClose.html). — bradimus, Aug 23 '16 at 15:12
@MarcElBichon Well you're not going to do it with `100,000` row batches. — Kayaman, Aug 23 '16 at 15:15
@MarcElBichon No, but you don't have the memory for 100,000 sized batches, so it's not like you get to choose. Batch sizes are in hundreds, trust me, I've done some batching in my time. — Kayaman, Aug 23 '16 at 15:18
It would be a good idea if you posted where/when the out of memory error occurs. Of course, it might be the case that the overhead comes from just double-storing the PreparedStatement batch data (in the PreparedStatement parameters and in the original Member objects), but the problem might be also due to the sheer number of Member objects you are loading/creating at once. Depending on the reason, there might be multiple ways of optimization viable. Also, a way out might of this be just adjusting your -Xmx parameter of the GlassFish server to assign more memory. — Piotr Wilkin, Aug 23 '16 at 15:20
No, you do not need a stored procedure. You would be a lot better off using MySQL's `load data infile` command. — Shadow, Aug 23 '16 at 15:21

score 0 · Answer 1 · edited May 23 '17 at 12:23

Replacing your insert statement in your prepared statement with a call to a stored procedure in your prepared statement will not affect the memory consumption in any meaningful way.

You are running out of memory because you are using a very large batch size. You should test the performance of different batch sizes - you will find that the performance improvement for larger batches diminishes quickly with batch sizes greater than dozens to hundreds.

You may be able to achieve greater input rates by using load data infile. You would take your many rows of data, create a text file and loading the file. For example, see this question.

You may also consider parallelizing. For example, open multiple connections and insert rows to each connection with separate threads. Likewise, you could try doing parallel loads using load data infile.

You will have to try the various techniques, batch sizes, number of threads (probably not more than one per core), etc. on your hardware setup to see what gives the best performance.

You may also want to look at tuning some of the MySQL parameters, drop (and later recreate) indexes, etc.

Java-MySQL: How to create a Stored Procedure

1 Answers1