0

I have wrote a java code to parse xml file using jaxb and then insert the data from xml file to my sql. The code does insertion to different 20 tables based on the xml tags. My problem is that my code is running for last 3 days and it could insert 5 millions record to my sql data base! In this way I need to wait a month to finish the job.. Can you give me some hint how can I make my code efficient?

kiarash
  • 23
  • 4
  • Are you inserting one record at a time? MySQL supports multi-insert: http://stackoverflow.com/questions/6889065/inserting-multiple-rows-in-mysql – weston Apr 06 '17 at 12:50
  • 1
    Do you really need the queries in a XML format? Can't you have another format (like plain SQL)? – Sergio Lema Apr 06 '17 at 12:50
  • @weston I guess even a multi-insert (or batch insert) wouldn't be suitable here due to the amount of data. The internal cache for a potencial rollback wouldn't be amused. Using a external way, like "Load Data" from the answer, would be more suitable. – Tom Apr 06 '17 at 13:11
  • @weston cool. but still need to read from xml file and store in a java structure then do multiple insertion. So i think java can not store 50 milion – kiarash Apr 06 '17 at 13:14
  • A Java structure? Time to show your code I think. – weston Apr 06 '17 at 16:12

1 Answers1

0

MySQL supports 'LOAD DATA' command and CSV format. So converting your XMLs into prepared CSV and then load by mysql abilities will be fastest solution:

How to import CSV file to MySQL table

Community
  • 1
  • 1
Alex Chernyshev
  • 1,719
  • 9
  • 11