Nhibernate Nunit - clear database between testcases

We have a rather extensive test suite that takes forever to execute. After each test has completed, the database (MSSQL) needs to be emptied so it is fresh for the next testcase. The way we do this is by temporarily removing all foreign keys, TRUNCATE'ing all tables, and re-adding the FKs.

This step takes somewhere between 2-3 seconds, according to NHProfiler. All the time is seemingly spent with the FK operations.

Our current method is cle开发者_StackOverflow中文版arly not optimal, but which way should we go to improve the performance ? The number of elements which are actually deleted from the DB is completely insignificant compared to the number of operations for the FK removal/additions.

Using an in-memory SQLite database is not an option, as the code under test uses MSSQL specific operations.

You could wrap everything in a transaction and in the end just rollback everything. That's how I do it. It allows also to run tests in parallel.

what about using SQL Server Compact, create the database from the mapping files using nhibernate schema create and load the data for each test. if you are talking about a trivial amount data.

Have a look at this blog post - Using SQL Server Compact Edition for Unit testing

Alternativly you could use Fluent Migrator to create the database schema and load the data for each test.

Why are you even using a DB in your tests? Surely you should be mocking the persistence mechanism? Unless you're actually trying to test that part of the functionality you're wasting time and resources actually inserting/updating/deleting data.

The fact that your tests rely on ms sql specifics and returned data hints at the possibility that your architecture needs looking at.

I'm not meaning to sound rude here - I'm just surprised no one else has picked you up on this.

w://

There are a couple of things that I've done in the past to help speed up database integration tests. First thing I did was I ended up having a sql script that actually creates the entire database from scratch. This can be easily accomplished using a tool like Red-Gate SQL Compare against a blank database.

Second I created a script that removed all of the database objects from an existing database.

Then I needed a script that populated the database with test data. Again, simple to create using Red-Gate tools. You don't need/want a ton of data here, just enough to cover your test cases.

With those items in place, I created one test class with all of my read-only operations in there. In the init of that class, i cleared a local sql server express instance, ran the create script and then ran the populate script. This ensured the database was initialized correctly for all of the read-only tests.

For tests that actually manipulate the database, we just did the same routing as above except that we did it on test init as opposed to class init.

Obviously the more database manipulation tests you have, the longer it will take to run all of your tests. If it becomes unruly, you should look at categorizing your tests and only running what is necessary locally and running the full suite on a continuous integration server.