07/03/08

Don't write your test suite to create and destroy databases for each run. Instead, make each test method start a transaction and roll it back. We just made that move at work on a DAL project, and the test suite went from 500+ seconds to run the whole thing down to around 100. It also allowed us to remove a lot of "undo" code in the tests.

This means ensuring your test helpers always connect to their databases on the same connection (transactions are connection-specific). If you're using a connection pool where leased conns are bound to each thread, this means rewriting tests that start new threads (or leaving them "the old way"; that is, create/drop). It also means that, rather than running slightly different .sql files per test or module, you instead have a base of data and allow each test to add other data as needed. If your rollbacks work, these can't pollute other tests.

Obviously, this is much harder if you're doing integration testing of sharded systems and the like. But for application logic, it'll save you a lot of headache to do this from the start.

This depends highly on what DBMS you're using and how it's configured - in SQL Server, for example, a rollback of a transaction requires the transaction log to be read out-of-order, which can be slow. Truncating a table or dropping a database is usually much quicker, as those operations are not logged.

Also, open transactions can block other operations, including shared resources such as tempdb, so that should be kept in mind as well if testing on a shared system.