Slow Performance of Update / merge into sStatements

[Andrii Odnoviunenko]

Hello, 

We face the following  performance   issue of the H2 Database V2 in the embedded mode is not good 

 (UPDATE statement for 1 column in a table with 1 Million dataset with a unique index column ) takes about 40 seconds

jdbc:h2:file:XXXXXX:CACHE_SIZE=6000000;QUERY_CACHE_SIZE=16;

also "merge into" statements with a join between 2 indexed columns are comparably slow. 
In general the performance is times worse than in a test with Oracle database

various settings have been tried (increasing cache size helped a bit, but not much
I have also tried the "split" mode and changing page size to other values (there have ben only marginally small performance gains)

Please advice what the reason can be? Which other settings may cause this performance issue?

[Andreas Reichel]

Greetings!

1) your write "embedded" mode, but your URL shows File access (not Memory)

2) you don't state the H2 version you are using

3) maybe provide a simplified, self containing test for people to look at the details

In general, large DML on indexed columns/tables can lead to "write amplification". I filed a similar issue 2 years back.

The received advice was:

1) remove indexes, update, create indexes (effective, but not nice)

2) update smaller portions (is this what you mean with "split" mode?)

Best regards

Andreas

--
You received this message because you are subscribed to the Google Groups "H2 Database" group.
To unsubscribe from this group and stop receiving emails from it, send an email to h2-database...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/d5484788-daed-48e8-9ca1-3369e11f3350n%40googlegroups.com.