Insert to database a lot of data with batch insert

Question

I create a program that inserts to MySql database millions of values. I read about batch insert that will optimize my program and make it faster but when I tried to do it, it worked in the same way. Instead of inserting each value to the database I kept in a list each time 500 values and then insert them in one big loop like this:

for(int i=0;i<500;i++)
{
   insertData(list.get(i));
}

Then i remove all the values in the list and start collecting 500 values again. Shouldn't it work better?
My Insert code is:

public void insertToNameTable(String id,String name) throws SQLException
       {
           PreparedStatement ps=null;

            ps= conn.prepareStatement("INSERT INTO NameTable values(?,?,?)",user.getId(),user.getName());


            ps.setString(1,id);
            ps.setString(2,name);
            ps.setBoolean(3,false);
            ps.executeUpdate();

       }

I have some questions:
1.why it isn't work faster when i do batch insert?
2.how many values I should enter each time in order to make it faster?(500,1000,10000) the more values enter together is better?
3. is the way I insert the values to my database is the best way?

Answer 1

This is the efficient way for batch insert.

Connection connection = new getConnection();
Statement statement = connection.createStatement();
 
for (String query : queries) {
    statement.addBatch(query);
}
statement.executeBatch();
statement.close();
connection.close();

Answer 2

Questions 1&2:

User Neil Coffey some time ago said:

The notion that prepared statements are primarily about performance is something of a misconception, although it's quite a common one.

Another poster mentioned that he noted a speed improvement of about 20% in Oracle and SQL Server. I've noted a similar figure with MySQL. It turns out that parsing the query just isn't such a significant part of the work involved. On a very busy database system, it's also not clear that query parsing will affect overall throughput: overall, it'll probably just be using up CPU time that would otherwise be idle while data was coming back from the disk.

So as a reason for using prepared statements, the protection against SQL injection attacks far outweighs the performance improvement. And if you're not worried about SQL injection attacks, you probably should be...

Here is the original post:
PreparedStatements and performance
and in my opinion all answers are worth reading. I think you expect PreparedStatement to be some kind of magician that would increase your inserting speed significantly and that is why you are disappointed with the improvement you get.

Question 3:
The proper way of using PreparedStatement is preparing a statement and then setting values and updating database in a loop. Here is a good example: Reusing a PreparedStatement multiple times