How to reinitialize in-memory database as fast as possible?

[Fyodor Kupolov]

Hello Thomas,

We use H2 for in-memory integration testing and a database is
reinitialized before any test case. DB_CLOSE_DELAY=-1 connection
parameter keeps the database alive during the testing phase; and the
following steps are done before running a test method:
1) DROP ALL OBJECTS --To clear the changes made by previous tests
1) Then I execute series of DDL and insert statement to create a clean
instance.

Everything is working fine except that DROP ALL OBJECTS takes more
than 50% of total testing time. I've profiled the tests and found that
DROP ALL OBJECTS spends too much time on checking constraints and
other stuff not very important in my case. DROP SCHEMA also takes long
time. So I have a question if there is an option to reset the in-
memory database faster or you can propose an alternative approach?

Thank you in advance!
Fyodor

[Thomas Mueller]

Hi,

Thanks for your feedback! You are right, DROP ALL OBJECTS is really
slow if there are many tables. I will make it fast in the next
release.

A workaround is to use a new database for each test (disconnect from
the old database, and open a new connection). That is, without using
DB_CLOSE_DELAY=-1, or by setting it manually to 0 before
disconnecting. This only works for in-memory database of course.

Would that be a solution for you? Please tell me if you have any
question about that.

Regards,
Thomas

[Fyodor Kupolov]

Hi Thomas,

Thank you for a fast and helpful tip regarding setting the
DB_CLOSE_DELAY to 0. I'll evaluate it and hope this would help because
we don't use a pooled datasource for tests. I'll inform you about the
result.
You are right about a large number of tables - currently we have more
than 80 with many constraints.

At our company we use H2 for several internal and public projects and
very happy with it. I evangelize it as a better alternative for mysql/
derby or even hsqldb for Java based projects. Thank you for your work!

Regards,
Fyodor

On Jan 17, 8:22 pm, "Thomas Mueller" <thomas.tom.muel...@gmail.com>
wrote:

> Hi,
>
> Thanks for your feedback! You are right, DROP ALL OBJECTS is really
> slow if there are many tables. I will make it fast in the next
> release.
>
> A workaround is to use a new database for each test (disconnect from
> the old database, and open a new connection). That is, without using
> DB_CLOSE_DELAY=-1, or by setting it manually to 0 before
> disconnecting. This only works for in-memory database of course.
>
> Would that be a solution for you? Please tell me if you have any
> question about that.
>
> Regards,
> Thomas
>

[Thomas Mueller]

Hi,

> Thank you for a fast and helpful tip regarding setting the
> DB_CLOSE_DELAY to 0.

I believe this is the best solution.

> You are right about a large number of tables - currently we have more
> than 80 with many constraints.

In the newest release from yesterday, dropping objects is much faster. However dropping the whole database the best solution I think.

Thanks for your help!
Thomas

[Skylar Saveland]

My connection.url is

    jdbc:h2:mem:test;DB_CLOSE_DELAY=0;INIT=RUNSCRIPT FROM './h2-ext/add_to_date.sql'

But, data persists between @Test methods. Is there something I'm missing?

[Noel Grandin]

Try running something like:

org.h2.store.fs.FileUtils.deleteRecursive("mem:test", true);

in your test teardown process.

[Skylar Saveland]

I tried this. But, data still persists between tests.

I've posted this question here: http://stackoverflow.com/questions/26555408/junit-h2-jaxrs-drop-in-memory-database-between-test-methods

Thanks,

Skylar