Ideally unit test harness should run very fast. Because all unit testing involving a database is quite slowish, we need to apply some effort to ensure that our tests finish in acceptable time.
This slowness might not be a concern when we try out a cool new technology against a tiny set of objects and tests, but it surely does become an issue when we unit test a real life system of any reasonable size. In this post we shall learn how to increase the speed of our test harness.
This post continues the series on unit testing, the previous posts are
How to Benefit from Unit Testing T-SQL: choosing what not to test
How to Benefit from Unit Testing T-SQL: choosing what to test
How to Benefit from Unit Testing T-SQL. Part One.
How to Benefit from Unit Testing T-SQL: Reusing Manual Tests as Parts of Unit Tests
How to Benefit from Unit Testing T-SQL: Exposing Failure in a User-Friendly Way
Benefit from Unit Testing T-SQL: Reuse Unit Tests as Documentation
Do not explicitly test modifications
Explicitly testing modifications is definitely doable, but it is quite slow.
Besides, we cannot easily run such tests concurrently from several connections, unless we use separate databases. Instead, we should test modfications implicitly - we use them to build up at least some of the test data, and verify that our selects return expected results.
Use one and the same test data for all tests
Adding test data specifically for a group of tests and tearing it down afterwards uses up too much precious time. Instead, we should run all tests off of the same test data. As an additional beefit, that allows us to run tests in parallel against the same test database. Of course, when we change test data to accomodate a unit test, this can break other tests. We shall address that in a later post.
Use C#, not T-SQL, to match actual results against expected.
We can love T-SQL as much as anyone else, but it is simply not the best tool for the job. First of all, it cannot capture more than one result set. Besides, matching actual results against expected in C# is in my experience much faster. One and the same C# module can work against all result sets. On the other hand, should we be matching results in T-SQL, a lot of time would be spent parsing and compiling SQL.
Concentrate on testing only the most important and the most complex modules.
We have discussed that before, in How to Benefit from Unit Testing T-SQL: choosing what to test , but it is definitely worth repeating here.
Next steps.
When we use one and the same test data for all tests, we may need to change it from time to time. These changes may break some tests. Fixing them should be fast and easy. We shall address it in the next post.