In a current solution we're testing our NHibernate app against an SQLite (in-memory) databases. The database is created from scratch before each test runs. This guarantees that each individual test gets it's own fresh copy of the DB.

In a perfect world my tests would give me a green bar and they'd do it in about 1 second flat for the whole suite :) I can't avoid failing tests every now and again, but I REALLY want to get my tests running as fast as humanly possible.

Testing against the database is never going to be as fast as using mocks, but I just don't dig mocks right now. So, what to do?

Tip 1: Only Create One Session Factory for a Test Run

I noticed that our solution was creating a new NHibernate session factory at the beginning of each test fixture. The session factory is a heavy object that takes time to create.

Most test runners run tests in a single app-domain, so you can make your SessionFactory static and only initialise it once for the entire test run.

Here's an example of our common persistent test fixture base class.

public abstract class TestFixtureBase
{
    [TestFixtureSetUp]
    public void Before_Testing_Begins()
    {
        //expensive operation so done once per test run
        if(UoW.SessionFactory == null)            
            UoW.Configure("", UoW.Environment.Test, Configure);            
    }
} 

Tip 2: In-Memory Drop/Create is Fastest

I tried a number of different approaches to NHibernate based testing with SQLite. Here's what I found...

Drop/Create In-Memory Before Each Test (best approach found so far)

Here's the test run results for our solution that uses a Drop/Create for each test. This is against an In-Memory SQLite database.

18.71 seconds for 79 tests using drop/create. Not brilliant. But the tests running in-memory SQLite are about 30% faster than the file-based alternative.

I also tried using the SQLite connection-pool ("Data Source=:memory:;Pooling=True;Max Pool Size=1;") but this seemed to offer no improvement (in fact I think it was worse!).

Deleting Data Before Each Test

I thought I'd trying something else to see if I could speed things up. Instead of drop/create, another strategy is to delete all the data in your database. Thanks to this not-so-useful NHibernate trick,
my good friend Olly at Zolv discovered that you can do this: _session.Delete("from Object o").

Lets's see how that goes...

32.69 seconds for 79 tests using delete all strategy. Even worse.

This also needs to use a file-based SQLite database since it is relying on an existing db to be in place at the beginning of each test (rather than drop/create which actually creates the db in memory before each test). The file-based SQLite database is much slower than the in memory one.

There is a chance that NHibernate 2.1 might be faster at this (I'm testing this with NHibernate 2.0 which does a SQL delete for each individual row).

Copying Blank Database Before Each Test

Another thing I thoght I could try was to create a blank SQLite database file at the beginning of each test run, and then make a copy of it before each test, so each test starts with a blank database. My thinking was that a file-system file-copy would be that quickest way of getting a blank database.

This performed better than the previous "deleting data" approach, but still not as fast as the in-memory database.

26.13 seconds for 79 tests using file-copy approach. Close, but no cigar.

Where to Next?

Hmm, I'm not really happy with my 18 second test runs, they're too slow to make me want to run them regularly. Would be interesting to hear how this compares with other peoples test times.