Prerequisites: Basic knowledge of C# and Entity Framework is desired.

When I was working on Test Analytics Solution, I did not expect to use any stored procedures for the project. However, as the database grew, the ORM framework became inefficient for some of the use-cases, relying on joining and aggregating data from many tables. No matter what I tried, SQL profiler showed extremely slow, dynamic SQL statements, bloated as Trump’s ego. The performance was a victim of generalization.

From the other side, a stored procedure (SP) completed the job perfectly. It took fractions of seconds instead of minutes. Obviously, I had to go forward using the stored procedure. It meant leaving a critical part of the business logic in a SQL world, poorly testable and exposed to regressions. It was one of those moments, as I explored in my previous post, that I struggled to apply TDD practices.

I started to think about how to incorporate a stored procedure into the TDD cycle. It seemed like a hard task in the beginning, but I found a solution surprisingly fast. The idea was very simple: to build a stair which connects high-level language tests with the database dungeons. After I had built it, the SP started to evolve, and tests guided its evolution. Furthermore, I managed to adopt tests from the previous solution (non-SP solution). Re-enabling them one by one, I re-created the same functionality, but this time incomparably faster.

Here is the stored procedure we are using in production:

CREATE PROCEDURE [dbo].[GetTestStats] @Branch VARCHAR(128)  
,@Changeset VARCHAR(128)  
,@BuildConfigId INT  
,@SubmitterId INT  
,@TestName VARCHAR(1024)  
,@AgentId INT  
,@RevisionStartDate DATETIME  
,@RevisionEndDate DATETIME  
,@ShowOnlyUnstableTests BIT  
,@ErrorMessage VARCHAR(1024)  
,@TestSessionStartDate DATETIME  
,@TestSessionEndDate DATETIME  
AS  
BEGIN  
-- SET NOCOUNT ON added to prevent extra result sets from  
-- interfering with SELECT statements.  
SET NOCOUNT OFF;

DECLARE @BranchNew VARCHAR(128) = @Branch  
DECLARE @ChangesetNew VARCHAR(128) = @Changeset  
DECLARE @BuildConfigIdNew INT = @BuildConfigId  
DECLARE @SubmitterIdNew INT = @SubmitterId  
DECLARE @TestNameNew VARCHAR(1024) = @TestName  
DECLARE @AgentIdNew INT = @AgentId  
DECLARE @ErrorMessageNew VARCHAR(1024) = @ErrorMessage;

WITH TestRunsStats (  
TestId  
,STATE  
,TIME  
,TestName  
,TestSessionDate  
)  
AS (  
SELECT TestId  
,STATE  
,TIME  
,DisplayName AS TestName  
,ts.Date as TestSessionDate  
FROM TestRuns tr WITH(NOLOCK)  
INNER JOIN SuiteRuns sr WITH(NOLOCK) ON sr.Id = tr.SuiteRunId  
INNER JOIN TestSessions ts WITH(NOLOCK) ON sr.TestSessionId = ts.Id  
INNER JOIN ProductVersions pv WITH(NOLOCK) ON ts.ProductVersionId = pv.Id  
LEFT JOIN BuildRunInfoes bri WITH(NOLOCK) ON ts.BuildRunInfoId = bri.Id  
LEFT JOIN Submitters sub WITH(NOLOCK) ON ts.SubmitterId = sub.Id  
INNER JOIN Agents ag WITH(NOLOCK) ON ts.AgentId = ag.Id  
INNER JOIN Tests t WITH(NOLOCK) ON tr.TestId = t.Id  
WHERE tr.STATE > 3  
AND (  
@ChangesetNew IS NULL  
OR pv.Revision = @ChangesetNew  
)  
AND (  
@BranchNew IS NULL  
OR pv.Branch = @BranchNew  
)  
AND (  
@BuildConfigIdNew IS NULL  
OR bri.BuildConfigId = @BuildConfigIdNew  
)  
AND (  
@SubmitterIdNew IS NULL  
OR sub.Id = @SubmitterIdNew  
)  
AND (  
@AgentIdNew IS NULL  
OR ag.Id = @AgentIdNew  
)  
AND (  
@TestNameNew IS NULL  
OR DisplayName LIKE '%' + LOWER(@TestNameNew) + '%'  
)  
AND (  
@RevisionStartDate IS NULL  
OR @RevisionStartDate <= pv.RevisionDate ) AND ( @RevisionEndDate IS NULL OR @RevisionEndDate >= pv.RevisionDate  
)  
AND (  
@ErrorMessageNew IS NULL  
OR tr.[Message] LIKE '%' + @ErrorMessageNew + '%'  
)  
AND (  
@TestSessionStartDate IS NULL  
OR @TestSessionStartDate &lt;= ts.Date ) AND ( @TestSessionEndDate IS NULL OR @TestSessionEndDate >= ts.Date  
)  
)  
,TestRunsStatsGroupped  
AS (  
SELECT TestRunsStats.TestId  
,TestRunsStats.TestName  
,CAST(AVG(CAST(TIME AS BIGINT)) AS INT) AS 'Average'  
,Count(TIME) AS 'Samples'  
,100 \* SUM(CASE  
WHEN STATE = 4  
THEN 1  
ELSE 0  
END) / SUM(CASE  
WHEN STATE > 3  
THEN 1  
ELSE 0  
END) AS 'Rate'  
,CAST(FLOOR(ISNULL(STDEV(TIME), 0)) AS INT) AS 'StDev'  
,MAX(TestSessionDate) as 'LastRunOn'  
FROM TestRunsStats WITH(NOLOCK)  
GROUP BY TestId  
,TestName  
)  
SELECT TestId  
,TestName  
,Average  
,Samples  
,Rate  
,StDev  
,LastRunOn  
FROM TestRunsStatsGroupped WITH(NOLOCK)  
WHERE (  
@ShowOnlyUnstableTests IS NULL  
OR @ShowOnlyUnstableTests = 0  
OR (  
@ShowOnlyUnstableTests = 1  
AND [Rate] &lt; 100  
)  
)  
ORDER BY TestName  
OPTION (RECOMPILE)  
END

To illustrate an SP evolution on a small scale, let’s try to solve the problem defined below.

Your organization runs tests on a build farm. For each test, you want to calculate the Success Rate. If “Test1” passes 3 out of 4 times, the Success Rate is 75%. For performance reasons, you decide to use a stored procedure. You want the stored procedure to be testable.

First test. According to the 3-rules of TDD, I start with a red test. It checks if there is nothing in the database; the SP returns nothing.

[Test]  
public void GetRunStats_NoRunsInDatabase_ReturnsNothing()  
{  
  var context = new TestDataDbContext("TestConnection");  
  var statsService = new TestRunStats(context);  
  
  var results = statsService.GetRunStats();  
  
  Assert.IsEmpty(results);  
}

At this moment the SP looks like this:

CREATE PROCEDURE [dbo].[GetRunStats]  
AS  
BEGIN  
select 'something'  
END

Not a surprise, that assertion at line 7 fails:

Simple change to the ‘select’ statement makes it green.

...  
select 1 from TestRuns  
...

Second test. If there is one successful test in the database, Success Rate is 100%.

[Test]  
public void GetRunStats_OneSuccesfullTestRunInTheDatabase_ReturnsStatsEntryWith100ProcentRate()  
{  
  // create successful test run entry in the database
  var statEntry = m_StatsService.GetRunStats().First();

  Assert.That(statEntry.Rate, Is.EqualTo(100));  
}

To make it green it is enough to change “1” to “100” in the select statement:

...  
select 100 from TestRuns  
...

Third test If there is one failed test in the database, Success Rate is 0%.

[Test]  
public void GetRunStats_OneFailedTestRunInTheDatabase_ReturnsStatsEntryWith0ProcentRate()  
{  
  // create failed test run entry in the database
  var statEntry = m_StatsService.GetRunStats().First();

  Assert.That(statEntry.Rate, Is.EqualTo(0));  
}

To make this one and all other tests green, we need to change the stored procedure like this:

...  
select Success * 100 from TestRuns  
...

And so on until SP evolves to the point where it solves the problem it was designed for. I love to see how a series of small, focused commits shaped the stored procedure. Not a single symbol of it is written without reason, guided by a test.

That’s it. If you are familiar with C# and Entity Framework (EF), you might want to explore the Example project. Readme.md will guide you through the implementation details and suggest a simple exercise. If you are not familiar with C# and EF, I hope you’ve got some inspiration, at least enough to figure out how to adopt the same approach to other languages and frameworks.