0

My setup - Oracle DB 12.1C, Spring application with Hibernate.

table:

create table war
(
  id      int generated by default as identity                  not null constraint wars_pkey primary key,
  t1_id   int references t1 (id) on delete cascade              not null,
  t2_id   int references t2 (id) on delete cascade              not null,
  day     timestamp                                             not null,
  diff    int                                                   not null
);

I would like to insert 10 000 records into table. With repository.saveAll(<data>) it takes 70s, with using JpaTemplate.batchUpdate(<insert statements>) it takes 68s. When I create new temporary table without constraints it takes 65s.

What is the best/fastest way, how to insert this amount of records into Oracle DB? Unfortunately CSV is not an option.

Lukas Forst
  • 802
  • 1
  • 8
  • 25
  • 10000 rows is absolutely not considered "huge" these days. Not even "big". That's actually a really small number –  Aug 31 '18 at 12:12
  • You've ruled out what is probably the fastest option (CSV through SQL Loader or an external table). So, how fast do you want this to be? Are you open to forgetting Hibernate and writing some Oracle SQL or PL/SQL? – APC Aug 31 '18 at 12:14
  • @APC unfortunately we can't use CSV since this is forbidden by customer. I'm open to almost all options - it just needs to be done via JAVA/Kotlin code. – Lukas Forst Aug 31 '18 at 12:20
  • So where do the 10000 records come from? Presumably it's not one user entering this huge amount of data in a GUI? ( @a_horse_with_no_name is correct to say that 10000 rows is not huge for a database but it's an awful lot of typing). – APC Aug 31 '18 at 12:31
  • @APC it is generated by our algorithm -> it has to be inserted by JVM application itself. – Lukas Forst Aug 31 '18 at 12:37
  • the performance issue do not come from hibernate or jdbc. You should be able to insert 10k rows that contains 4 ints and 1 timestamp far faster than 70sec ( unless the latency / bandwidth between the java code and database is really awful). Do a CPU sampling (using jvisualvm for example) to find out what is taking time. Update your question with the sampling result if you need help to understand how to use these info. – Thierry Aug 31 '18 at 12:55
  • 10k is nothing really. My guess is that you're pushing data across the network and your app is not on same local subnet (or anywhere near) your Oracle instance. Not sure of your situation of course, but I've seen slow insert performance testing on my workstation, and much faster performance once code is released to UAT/Prod environments. – tbone Aug 31 '18 at 13:15

1 Answers1

0

My solution was to redesign our model - we used int_array for storing diff -> this was almost 10 times faster than first solution.

Lukas Forst
  • 802
  • 1
  • 8
  • 25