General J2EE: JDBC batch inserts/updates

  1. JDBC batch inserts/updates (2 messages)

    Hi all, Basically my current webapp has to do a batch of both inserts and updates into MySQL database. The batch size can vary from a few thousands to 50000 records. The problem is that, the batch contains both inserts and updates. At the moment, I'm using JPA but the performance is getting worse when the database growing. It takes around 8 minutes to do a batch of 13000 records. Essentially, since each record has a custom-regenerated primary key, JPA actually has to try to retrieve the record from the database check if this is an updsate. So now if I have 50000 records to be inserted/updated, JPA have to do 50000 lookups, which is painfully slow as the database reaches the size of millions row. Of course I can do a batch select based on the list of primary keys in the batch and then performance an update or insert accordingly. However, I'm just wondering if there is a better way to do mix updates/inserts batch in JDBC? Maybe using a stored procedure which checks for existing record to perform appropriate action? Thanks.

    Threaded Messages (2)

  2. Clarify[ Go to top ]

    Just to clarify the above question: I have no knowledge of which records in the batch need to be updated or inserted. However, each record has a custom generated primary which can be used to tell whether it is an update or an insert. If the primary key is in the database, it's an update otherwise, it's an insert.
  3. Re: JDBC batch inserts/updates[ Go to top ]

    Try to add DB index to column that lookup from table. It should increale performance of lookup operation and whole batch operation.