I've a typical scenario & need to understand best possible way to handle this, so here it goes -

I'm developing a solution that will retrieve data from a remote SOAP based web service & will then push this data to an Oracle database on network. Also, this will be a scheduled task that will execute every 15 minutes.

I've event queues on remote service that contains the INSERT/UPDATE/DELETE operations that have been done since last retrieval, & once I retrieve the events for last 15 minutes, it again add events for next retrieval.

Now, its just pushing data to Oracle so all my interactions are INSERT & UPDATE statements.

There are around 60 tables on Oracle with some of them having 100+ columns. Moreover, for every 15 minutes cycle there would be around 60-70 Inserts, 100+ Updates & 10-20 Deletes.

This will be an executable jar file that will terminate after operation & will again start on next 15 minutes cycle.

So, I need to understand how should I handle WRITE operations (best practices) to improve performance for this application as whole ?

Current Test Code (on every cycle) -

• Connects to remote service to get events.
• Creates a connection with DB (single connection object).
• Identifies the type of operation (INSERT/UPDATE/DELETE) & table on which it is done.
• After above, calls the respective method based on type of operation & table.
• Uses Preparedstatement with positional parameters, & retrieves each column value from remote service & assigns that to statement parameters.
• Commits the statement & returns to get event class to process next event.
• Above is repeated till all the retrieved events are processed after which program closes & then starts on next cycle & everything repeats again.

Thanks for help !