CODEWITHSUNDEEP
javamysql
Piccolomomo
Piccolomomo

Reputation: 58

Multiple insert speed up

i'm parsing large log files (5+GB) and extracting ad-hoc placed profiling lines (call name and execution time). I want to insert those lines into a MySql db.

My question is: should I execute the insert statement every time I get the line while parsing or there's some best practice to speed up everything?

Upvotes: 2

Views: 1873

Answers (6)

Jon Onstott
Jon Onstott

Reputation: 13727

If there is any way that you could do a bulk insert, that would help a lot (or at least send your data to the database in batches, instead of making separate calls each time).

Edit

LOAD DATA INFILE sounds even faster ;o)

https://web.archive.org/web/20150413042140/http://jeffrick.com/2010/03/23/bulk-insert-into-a-mysql-database

Upvotes: 7

Eric
Eric

Reputation: 2348

Aside from insert speed, the other problem you may run into is memory. Whatever approach you use, you will still need to consider your memory usage as the records are loaded from the file. Unless you have a hard requirement on processing speed, then it may be a better to use an approach with a predictable memory foot print.

Upvotes: 0

bpgergo
bpgergo

Reputation: 16037

From java JDBC batch insert

Example: You do this with every insert: http://code.google.com/p/hunglish-webapp/source/browse/trunk/src/main/java/hu/mokk/hunglish/lucene/Indexer.java#232

You do this with every batch http://code.google.com/p/hunglish-webapp/source/browse/trunk/src/main/java/hu/mokk/hunglish/lucene/Indexer.java#371

The size of the batch can be determined by the available memory.

Upvotes: 0

DaveH
DaveH

Reputation: 7335

If you don't want to follow the recommendations in Galz's link ( which is excellent BTW ) then try to open the connection and prepare the statement once, then loop round your log files carrying out the inserts ( using the premared statement ), then finally close the statement and connection once at the end. It's not the fastest way of doing the inserts, but it's the fastest way that sticks to a "normal" JDBC approach.

Upvotes: 0

Peter Lawrey
Peter Lawrey

Reputation: 533472

For small updates, the number of transactions is critical for performance. SO if you can perform a number of inserts in the same transaction it will go much faster. I would try 100 inserts per transaction first.

Upvotes: 1

Galz
Galz

Reputation: 6832

There are better options. See http://dev.mysql.com/doc/refman/5.0/en/insert-speed.html

In your case, I think writing the relevant records to a file and then using LOAD DATA INFILE is the best approach.

Upvotes: 3

Related Questions