Speeding up unit tests that need a clean database?

[Nyenyec N <ny...@gmail.com>]

Hi,

I have several JUnit tests that I run as part of the builds and also
from inside the Eclipse IDE.
So far my app uses SQL Server and HSQLDB. In both cases I found a way
to quickly create a clean database (with no tables) use it and discard
it after each test.

In SQL Server I use the CREATE/DROP DATABASE call, in HSQLDB I simply
use an in-memory instance. Creating and discarding a database takes
about 2 secs in SQL Server and 1 sec in HSQLDB.

In Derby however, connecting to a URL like this
"jdbc:derby:temp/testdb;create=true"
takes about 20 secs on the same machine (P4, 1Gb mem).
Is there a way to do this quicker or approximate the behavior
described above in some other way?

Thanks,
Nyenyec

Re: Speeding up unit tests that need a clean database?

[Jack Klebanoff <kl...@Mutagen.Net>]

Nyenyec N wrote:

>Hi,
>
>I have several JUnit tests that I run as part of the builds and also
>from inside the Eclipse IDE.
>So far my app uses SQL Server and HSQLDB. In both cases I found a way
>to quickly create a clean database (with no tables) use it and discard
>it after each test.
>
>In SQL Server I use the CREATE/DROP DATABASE call, in HSQLDB I simply
>use an in-memory instance. Creating and discarding a database takes
>about 2 secs in SQL Server and 1 sec in HSQLDB.
>
>In Derby however, connecting to a URL like this
>"jdbc:derby:temp/testdb;create=true"
>takes about 20 secs on the same machine (P4, 1Gb mem).
>Is there a way to do this quicker or approximate the behavior
>described above in some other way?
>
>Thanks,
>Nyenyec
>
>  
>
Before a JVM can use Derby it must load th Derby classes. A fairly large 
number of classes are involved in creating a database, so loading them 
takes an appreciable amount of time. This is unavoidable the first time 
that you create a database in the JVM. The second time that you create a 
database the classes will probably still be loaded, so the second 
database creation should be faster.

You want to group tests together to economize on class loading and 
database creation overhead. Of course, if you go overboard on this it is 
hard to diagnose problems and to maintain the tests.

Jack Klebanoff

Re: Speeding up unit tests that need a clean database?

[Suresh Thalamati <ts...@Source-Zone.org>]

Hi

  I don't think Derby has CREATE/DROP database  statements.  Derby has 
support for  backup/restore.
One way to simulate DROP/CREATE  in test scripts is  to restore/create 
(URL properties : restoreFrom and createFrom)
 an empty database from a backup after each test.   Backup will take
some time,  but I guess in your test environment 
 that is going to be only one time overhead.


ij scriipt:
---create a new database
connect 'jdbc:derby:wombat;create=true';
---take database backup before doing anything else
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE('ext/mybackup');
connect 'jdbc:derby:wombat;shutdown=true';
connect 'jdbc:derby:wombat;restoreFrom=ext/mybackup/wombat';

You should be able to make above call through JDBC based java program..


Thanks
-suresh


Nyenyec N wrote:

>Hi,
>
>I have several JUnit tests that I run as part of the builds and also
>from inside the Eclipse IDE.
>So far my app uses SQL Server and HSQLDB. In both cases I found a way
>to quickly create a clean database (with no tables) use it and discard
>it after each test.
>
>In SQL Server I use the CREATE/DROP DATABASE call, in HSQLDB I simply
>use an in-memory instance. Creating and discarding a database takes
>about 2 secs in SQL Server and 1 sec in HSQLDB.
>
>In Derby however, connecting to a URL like this
>"jdbc:derby:temp/testdb;create=true"
>takes about 20 secs on the same machine (P4, 1Gb mem).
>Is there a way to do this quicker or approximate the behavior
>described above in some other way?
>
>Thanks,
>Nyenyec
>
>  
>