Technical Article

Performance Testing Script

,

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 code it tests, as listed in the script, can be found here.

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

www.jessemclain.com

http://jessesql.blogspot.com

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

Rate

3.2 (5)

You rated this post out of 5. Change rating

Share

Share

Rate

3.2 (5)

You rated this post out of 5. Change rating