0

in according to my question above, I need to insert over 20000 thousands rows in one table of my database. In according to the performance I'm searching for a way to increase the efficiency of this process. My first Idea was to realize this with Java.Thread but i not quite sure if this is save enough. Has someone any good advices for me?

Edit: I already use preparedStatement.addBatch()

Tzachi
  • 55
  • 1
  • 7
commandcraxx
  • 243
  • 1
  • 7
  • 23
  • I don't about multithreading, but u can check this out: http://stackoverflow.com/questions/7195665/java-jdbc-multiple-prepared-statement-bulk-insert – Aashray Feb 27 '13 at 09:14
  • You can Use stored procedure and call it from your java code. – Biswajit Feb 27 '13 at 09:17

3 Answers3

1

Looks safe to me, provided that every thread uses a different Connection object, and then disposes of the resources properly.

Anyway, note that the DB itself has a limit on concurrently running requests (max_connections in MySQL), so it doesn't help creating more threads than this number. Also, consider other optimizations such as batch inserts.

Community
  • 1
  • 1
Eyal Schneider
  • 22,166
  • 5
  • 47
  • 78
1

There is nothing wrong with accessing a database from different threads. As Eyal wrote, just make sure things like Connections only get used with a single thread and properly disposed.

The other question is, if this will actually help your performance. I'd make sure that you did everything else before resorting to multiple threads. Especially using batch statements seems to be the first option to look into if you haven't already.

Jens Schauder
  • 77,657
  • 34
  • 181
  • 348
  • I'm benchmarking the combination of Batch and Thread. Is there a possibility to push the whole batch (e.g of 2000 rows) into the DB or is this already done with calling `preparedStatement.executeBatch()`? – commandcraxx Feb 27 '13 at 10:01
  • ~10mins for one big batch with ~8000 rows. ~7,5min with 10 Threads and 10 smaller batches. I think this should work to increase the performance. – commandcraxx Feb 27 '13 at 10:08
  • The Problem i see is to Synchronize the threads. So i got inconsistence of data insertion if i use more than one thread... – commandcraxx Feb 27 '13 at 11:07
  • Well .. "Concurrency is hard" that's why I recommend to check everything else first. – Jens Schauder Feb 27 '13 at 11:11
0

If I understand you correctly, you need a way to write thousands of query's to your Database. Do you know that you can execute a batch of MySQL queries? There is a question about this already on Stackoverflow, Java: Insert multiple rows into MySQL with PreparedStatement

you can use: PreparedStatement#addBatch() http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#addBatch%28%29

and PreparedStatement#executeBatch() http://docs.oracle.com/javase/6/docs/api/java/sql/Statement.html#executeBatch%28%29

Check out @BalusC 's answer in the link above for more detail.

Community
  • 1
  • 1
Henkes
  • 288
  • 4
  • 16