We have a 90000 line CSV file that is being looped thru in a method. The preexisting MBean component was using BMT and one transaction per row. Each row is loaded into a table in Oracle db (insert or update depending on whether the entity exists or not in that table).
We are using JPA with SLSB now. The SLSB is reading a property from a properties file to get the txBatchSize. This variable determines how many rows per tx. e.g., if txBatchSize = 500, then there will be one tx per 500 rows in the CSV file. The idea was to optimize performance. Performance actually degrades (to 3 hrs for 500 batchSize from 37 mins for 1 batchSize). This seems counter-intuitive to me and unexpected. Shouldn't the performance improve with less tx's? I thought there was significant overhead in using BMT and 90000 tx's?
Please advise as to why this is occurring. We'd like to improve performance. thx.
NOTE: the following behavior was consistent b/n the old and new code/build. There is an initial "burst" and then slows down.
Initially processing approx. 700 merchants per 1-2 seconds.
Around 27000 merchants slows down to approx. 15 merchants per 1-2 seconds.
Around 56000 merchants slows picks up to approx. 30 merchants per 1-2 seconds.