In database applications it is often important to ensure that your SQL uses indexes to avoid performance problems. Consider an SQL select statement like this

SELECT text FROM response WHERE questionId = 27 AND participantId = 38

If there is no index on response(questionId, participantId), the database needs to do a full table scan, and if the response table have a lot of rows, this will degrade performance. Such a performance problem might only be observed during testing on large amounts of data, which is not typically something a developer will do before every commit.

It would be useful to be able to detect this kind of problem much earlier, preferably during unit testing. Here I will describe one way to do this using Microsoft SQL Server and .NET:

SQL Server makes a lot of information accessible through performance counters, and one of those performance counters is incremented every time SQL Server does a full table scan. Utilizing that information, we can write a unit test that verifies that indexes are properly in place by asserting that the number of full table scans performed by a piece of code is equal to zero:

using(new AssertSqlFullScanCount(0))
{
   var responses = Response.GetResponses(q, p);
   Assert.That(responses, ...);
}

This code uses the following helper class:

public class AssertSqlFullScanCount : IDisposable
{
   private int expected;
   private long actual;
   private PerformanceCounter fullScans;
   public AssertSqlFullScanCount(int expected)
   {
      this.expected = expected;
      this.fullScans = new PerformanceCounter(
         "MSSQL$SQLEXPRESS:Access Methods",
         "Full Scans/sec");
      this.actual = fullScans.RawValue;
   }

   public void Dispose()
   {
      actual = fullScans.RawValue - actual;
      Assert.That(actual, Is.EqualTo(expected));
   }
}

Do you consider this kind of performance unit testing useful? How would you implement this kind of tests with Oracle

By Lars Thorup