by

Testing script for Stored Procedures

This script helps developers test the performance of a stored procedure or block of code. Although it was written specifically to assist me in comparing stored procedures, it can easily be modified for other testing purposes.

The script expects two tables, 'Seq1' and 'Seq2' to exist, and be of the structure as listed in the header comment of the script. It will test the code of interest by running it using different input sizes, multiple times, and record the performance of each run. 

The user should modify the values of these variables:

@RunsPerInputSize - the more runs at each input size, the more consistent the results

@InputSizeIncrement - the algorithm will start at this input size and also increment by it

@TotalNumberIncrements - the total number of different input sizes to test

For example, with @RunsPerInputSize = 2, @InputSizeIncrement = 50, and @TotalNumberIncrements = 3, the testing script will run the code to be tested with the following input sizes, in this order: 50, 100, 150, 50, 100, and 150.

Written by Jesse McLain

http://jessesql.blogspot.com
Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
src="https://www.snip2code.com/Embed/12159/Testing-script-for-Stored-Procedures?startLine=0"></iframe>
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
/************************************************************************************************* ** File: "20090217 - testing script.sql" ** Desc: This is a more generalized script to run performance testing on the sequence ** comparison code. It can be modified to test any other spds. This script requires ** the existence of two tables, Seq1 and Seq2, which should have the following structure: ** ** CREATE TABLE Seq1 ( ** CodeLineTxt varchar(max), /* stores the original line of code */ ** CodeLineNum int not null identity(1,1), /* stores the line number */ ** MatchLineNum int /* stores the matching line of code from spd #2 */ ** ) ** ** Return values: report & results ** ** Called by: ** ** Parameters: ** Input ** ---------- ** none ** ** Output ** ----------- ** none ** ** Auth: Jesse McLain ** Email: jesse@jessemclain.com ** Web: www.jessemclain.com ** Blog: http://jessesql.blogspot.com/2009/02/comparing-spds-part-3-performance.html ** ** Date: 02/16/2008 ** *************************************************************************************************** ** Change History *************************************************************************************************** ** Date: Author: Description: ** -------- -------- ------------------------------------------- ** 20080216 Jesse McLain Created script **************************************************************************************************/ SET NOCOUNT ON DECLARE @RunsPerInputSize int ; SET @RunsPerInputSize = 1 /* #runs of @InputSize to execute */ DECLARE @InputSizeIncrement int ; SET @InputSizeIncrement = 50 /* @InputSize to increment btwn outer runs */ DECLARE @TotalNumberIncrements int ; SET @TotalNumberIncrements = 1 /* #increments to execute */ DECLARE @StartTime datetime ; SET @StartTime = GETDATE() DECLARE @StopTime datetime ; SET @StopTime = GETDATE() DECLARE @Seq1 varchar(max) ; SET @Seq1 = 'Test Sequence "' DECLARE @Seq2 varchar(max) ; SET @Seq2 = 'Test Sequence "' DECLARE @TestValue varchar(2) ; SET @TestValue = '' /* holder to load values into seq tables */ DECLARE @InputSize int ; SET @InputSize = 0 /* input size for the current run */ DECLARE @InputValueIdx int ; SET @InputValueIdx = 1 /* counter */ DECLARE @RunIdx int ; SET @RunIdx = 1 /* counter */ DECLARE @IncrIdx int ; SET @IncrIdx = 1 /* counter */ DECLARE @Seq1Size int DECLARE @Seq2Size int DECLARE @Seq1Sizea int DECLARE @Seq2Sizea int DECLARE @PcntMatch decimal(9, 2) CREATE TABLE #PerformanceResults ( InputSize int, RunStart datetime, RunDone datetime, PcntMatch decimal(9, 2) ) /* the straight-forward approach to testing would be to start at the smallest input size, run through as many runs as we need for that, move on to the next input size, test that, until we test the max input size. We don't do that here. The problem with that approach is that if there's an external process running during the testing of an input size, the results for that size might be false. The approach here is to test the min size once, then the next largest size, until the max size is tested, then start all over and repeat until we've tested each inputsize as many as "@RunsPerInputSize" times. */ SET @RunIdx = 1 /* outer loop to increment the number of runs per input size */ WHILE @RunIdx <= @RunsPerInputSize BEGIN SET @IncrIdx = 1 /* inner loop to increment each input size */ WHILE @IncrIdx <= @TotalNumberIncrements BEGIN PRINT 'Testing size ' + LTRIM(STR(@IncrIdx)) + '/' + LTRIM(STR(@TotalNumberIncrements)) + ', for run ' + LTRIM(STR(@RunIdx)) + '/' + LTRIM(STR(@RunsPerInputSize)) SET @InputSize = @IncrIdx * @InputSizeIncrement /* insert rows into test table 1 */ TRUNCATE TABLE Seq1 SET @InputValueIdx = 1 WHILE @InputValueIdx <= @InputSize BEGIN SET @TestValue = CHAR(FLOOR(RAND() * 10) + 65) + CHAR(FLOOR(RAND() * 10) + 65) INSERT INTO Seq1 (CodeLineTxt) VALUES (@TestValue) SET @Seq1 = @Seq1 + @TestValue SET @InputValueIdx = @InputValueIdx + 1 END UPDATE Seq1 SET MatchLineNum = 0 SET @Seq1 = @Seq1 + '"' /* insert rows into test table 2 */ TRUNCATE TABLE Seq2 SET @InputValueIdx = 1 WHILE @InputValueIdx <= @InputSize BEGIN SET @TestValue = CHAR(FLOOR(RAND() * 10) + 65) + CHAR(FLOOR(RAND() * 10) + 65) INSERT INTO Seq2 (CodeLineTxt) VALUES (@TestValue) SET @Seq2 = @Seq2 + @TestValue SET @InputValueIdx = @InputValueIdx + 1 END UPDATE Seq2 SET MatchLineNum = 0 SET @Seq2 = @Seq2 + '"' /* wrap the executing code around timers to test */ SET @StartTime = GETDATE() EXEC spd_SequenceCompare SET @StopTime = GETDATE() /* record results */ SELECT @Seq1Size = COUNT(*) FROM Seq1 SELECT @Seq2Size = COUNT(*) FROM Seq2 SELECT @Seq1Sizea = COUNT(*) FROM Seq1 T1 WHERE MatchLineNum <> 0 SELECT @Seq2Sizea = COUNT(*) FROM Seq2 T1 WHERE MatchLineNum <> 0 SET @PcntMatch = 100.0 * (@Seq1Sizea / (1.0 * @Seq1Size) + @Seq2Sizea / (1.0 * @Seq2Size)) / 2 INSERT INTO #PerformanceResults (InputSize, RunStart, RunDone, PcntMatch) VALUES (@InputSize, @StartTime, @StopTime, @PcntMatch) SET @IncrIdx = @IncrIdx + 1 END SET @RunIdx = @RunIdx + 1 END SELECT InputSize, NumberOfRuns = COUNT(*), AverageRunTime = AVG(CONVERT(decimal(9, 2), CONVERT(varchar(max), DATEDIFF(ss, RunStart, RunDone)) + '.' + CONVERT(varchar(max), DATEDIFF(ms, RunStart, RunDone)))), AveragePercentMatch = AVG(PcntMatch) FROM #PerformanceResults GROUP BY InputSize DROP TABLE #PerformanceResults SET NOCOUNT OFF
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus