H2 (File) Delete Performance

Question

I've encountered somewhat problematic performance issues when it comes to a file-based H2 database in AUTO-SERVER mode. I am using H2 version 1.3.174. The database contains a single table with 5 columns. One of the columns is a CLOB, that (on average) contains 1 KB of text data per row. In a single-threaded test run, I've inserted 800,000 rows, which took 409 seconds - OK for me. I've executed a second test run with the following steps:

1. Load the first 100 messages from the database. Order of messages is established via primary key (NUMBER) value.
2. Delete those 100 messages with the following statement: DELETE FROM mytable WHERE id IN (...);

This is done until 790,000 rows of the database are deleted. In my real-world scenario, there would be some processing involved between steps 1 and 2. This second test run took 8.5 hours, on a fast machine under no load! I've observed that during the deletion, H2 created temporary files with names like "mydb.1978734278.38.temp.db", whose size varies fast between 24 and 1,300 MB.

Is this expected behavior? Any ideas what I might be doing wrong? Thanks for any help!

Answer 1

I'm answering my own question with my findings:

1. The main problem were the SELECT statements, this was due to a missing index. Once the proper INDEX was in place the SELECT/DELETE test run only took 2 minutes.
2. There seems to be a space problem when using CLOBS or LONGVARBINARY instead of VARCHAR: the file size was cut in half when using the latter.