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.
