C#,  Entity Framework,  SQL,  TDD

Stored procedures guided by tests

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 SP, 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;</p>

<p>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;</p>

<p>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 &gt; 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 &lt;= pv.RevisionDate ) AND ( @RevisionEndDate IS NULL OR @RevisionEndDate &gt;= 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 &gt;= 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 &gt; 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</p>

<p>var statEntry = m_StatsService.GetRunStats().First();</p>

<p>Assert.That(statEntry.Rate, Is.EqualTo(100));
}</p>

<p>

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</p>

<p>var statEntry = m_StatsService.GetRunStats().First();</p>

<p>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.

Leave a Reply