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;

	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 <= 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] < 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.

Leave a Reply

Your email address will not be published. Required fields are marked *