CODEWITHSUNDEEP
javaoracle-databaseoptimizationconcurrencypublish-subscribe
Maheswaran
Maheswaran

Reputation: 19

Best way to optimize DB inserts using Java

The program is written in java. 25 threads are processing 1 million tasks. Each task saves data into DB hence 1 million times DB insert happens. In order to optimize this, We tried following approach

  1. Tasks save data into ConcurrentLinkedDeque
  2. A thread polls the duque in periodic interval and gets all the available objects at that point in time.
  3. Once the available objects' count reaches a threshold ( say 100K ), then create a thread to save.

But this approach is not improving overall performance.

I would like to reduce the number of times (1 million times currently ) DB insert happens to improve performance. Are there any alternate solution like High Performing - multiple concurrent publisher and single concurrent subscriber kind of implementation ?

Upvotes: 1

Views: 265

Answers (1)

Engel
Engel

Reputation: 199

Reduce the overhead of row-by-row processing by batching commands. Many APIs include ways to batch commands, or you can combine them yourself with a statement like this:

INSERT INTO products (product_no, name, price)
SELECT 1, 'Cheese', 9.99 FROM dual UNION ALL
SELECT 2, 'Bread' , 1.99 FROM dual UNION ALL
SELECT 3, 'Milk'  , 2.99 FROM dual;

Upvotes: 2

Related Questions