File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes How to insert to MySql efficiently? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "How to insert to MySql efficiently?" Watch "How to insert to MySql efficiently?" New topic

How to insert to MySql efficiently?

Susan Smith
Ranch Hand

Joined: Oct 13, 2007
Posts: 224
I'm trying to insert data into an empty table. At the end, it will have 60 million+ records.

This is the pseudocode of what I do currently:

It's taking 3.5 - 4 hours. I'm trying to cut down the time as much as possible.

Then I read this article:
It says that:
If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time.

I'm thinking it must be something like this:

What I'm also wondering is whether addBatch mechanism that I do is already like what is mentioned in the article? Can somebody please advise?

Thanks in advance for all your help.
Campbell Ritchie

Joined: Oct 13, 2005
Posts: 46337
I tried that sort of thing, but more complicated, more as a test of performance than anything else, and I got nearly 60,000,000 records in. Took about a week . . .

So I don't think you need worry about 3� hours.

You can't tell from the API how that method is implemented; you could try getting whichever class implements Statement and see whether that tells you. Statement and PreparedStatement are actually interfaces, so they don't have actual implementations.
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

60 million? How often do you perform the insert? I'd recommend putting everything in a SQL file and inserting it directly into the database. That number of records never works that well via JDBC even with batch updates.

[OCA 8 Book] [Blog]
Susan Smith
Ranch Hand

Joined: Oct 13, 2007
Posts: 224
It's a one time insert for the 60+ million. But in other parts in my project, I have to do insert/ update in millions too (< 10 millions approximately), that's why I'm looking for a really really efficient way to make things run more smoothly.
Paul Clapham

Joined: Oct 14, 2005
Posts: 19973

If you're looking for an efficient method, and you have more than one method to choose from, then I would advise setting up some fairly large test data sets and running them through the different methods. Measure whatever counts as "efficiency" (in your case it sounds like it's wall-clock time) and see which method wins.
I agree. Here's the link:
subject: How to insert to MySql efficiently?
It's not a secret anymore!