Saving in-memory H2 database to disk

Question

How can I save/load full embedded h2 in-memory database to some file or directory in binary mode for faster loading.

I want to use this for caching data so I don't have to run all the lines of create table/insert clauses every time.

Selfsame

Can you just use the file DB vs. the in-memory version? I assume you're doing custom development using a framework (e.g. Grails) that you haven't mentioned. – Cordula

This is a custom project with no frameworks involved. To be honest I'm not sure what the performance implications of file db are, I want speed and the data will always fit in-memory so I assumed that in-memory db will be the fastest. The data will be query only so I don't want any unnecessary save checks. – Selfsame

@marc Using 'the file DB' is a different thing. It would be super useful in unit test if one could set up a in-memory test data set, with java or any other language, then make a backup the dataset, and use it to restore to initial state after each test. – Burchette

Answer 1

Instead of using an in-memory database, you could use a regular (persisted) database. You can still use in-memory tables even then (create memory table).

The easiest way to persist a completely in-memory database to disk is to use the SCRIPT TO 'fileName' SQL statement. This will create an SQL script. The data is stored in text form, which doesn't sound like the most efficient solution. However usually the bottleneck is the disk anyway and not formatting / parsing the text.

Another option is to create another database, link the tables with the in-memory database (using create linked table or the link_schema function), and then use create table as select to persist the tables.

Orbital

Inserting the clauses is what I currently do and for 1 gig of text it takes some time and it's not io. sorry but I want binary option. – Selfsame

Updated my answer. For SCRIPT: if it's not IO, what is it then? Did you use a profiling tool, such as the built-in profiler? – Orbital

Answer 2

As I found out it is possible to use H2 virtual FS that is much faster then default FS. So, I do this way:

//to save dump
connection.prepareStatement("SCRIPT TO 'memFS:data.sql'").execute();
//to load dump
connection.prepareStatement("RUNSCRIPT FROM 'memFS:data.sql'").execute();

Rueful