Topic
  • No replies
SystemAdmin
SystemAdmin
210 Posts

Pinned topic Performance question on inserting records to Database using JPA

‏2009-04-28T18:32:34Z |
I am trying to change some of my code to use JPA in standalone application, not in a container.

The goal is to insert a large amount of records ( above 10,000 records ) to the database. I tried two different implementations:

1. A single transaction commit:

... trans.begin(); 

while( ( record = get_record_to_insert() ) != 

null ) 
{ ... ... em.persist(record); 
} trans.commit();

2. Multiple transaction commits (commit every 100 records):

... trans.begin(); counter = 0; 

while( ( record = get_record_to_insert() ) != 

null ) 
{ ... ... em.persist(record); counter++; 

if( (counter % 100) == 0 ) 
{ trans.commit(); trans.begin(); 
} 
} trans.commit();

For 10,000 records, the implementation 1 only takes 37 seconds. But implementation 2 takes 17 minutes.
I am using MySQL, the primary key is a BIGINT mapped to Java type long.
I understand that the implementation 2 will take longer time than implementation 1, but I do not expect a significant difference like this (17 minutes vs. 37 seconds).

Can some body help me understand why?

In addition, I timed each transaction commit in implementation 2, it is progressively increasing. Start with an empty table, the first commit (100 records per commit) takes 1 second, the second one takes 1.5 seconds.... At the 50th commit, it takes for than 2 minutes...

Did I do something wrong?

I appreciate any help!

Thanks

John