THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Alexander Kuznetsov

Benefit from Unit Testing T-SQL: Speed up Your Test Harness

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.

Published Friday, October 15, 2010 3:04 PM by Alexander Kuznetsov


No Comments
New Comments to this post are disabled

About Alexander Kuznetsov

Alex Kuznetsov has been working with object oriented languages, mostly C# and C++, as well as with databases for more than a decade. He has worked with Sybase, SQL Server, Oracle and DB2. He regularly blogs on, mostly about database unit testing, defensive programming, and query optimization. Alex has written a book entitled "Defensive Database Programming with Transact-SQL" and several articles on and Currently he works as an agile developer.

This Blog


Privacy Statement