0

Some of the tasks in my team are related to stored procedure optimization. I am mainly a .NET developer, but I thought of writing a generic procedure for data comparison while procedures are being changed. The main goals are:

  • ensure that changes to not break existing functionalities (same inputs should provide same output)
  • can be run from time to time to easy spot bugs introduced by optimizations
  • should also provide basic profiling information, so that "optimized" version is actually faster
  • should be run from SQL Server

The code:

-- 
-- Description: compares the results returned by two stored procedures. Comparison is performed using a 'loopback' linked server and using openquery, so the final query
--              must obey openquery limitations. It returns all rows that are within the first result set and not within the second and viceversa. If all result sets are 
--              empty, results are equivalent (order does not matter)
--
-- PARAMS:
--      @Procedure1FullName: procedure 1 full name (i.e. database.schema.proc_name)
--      @Params1Str: procedure 1 params as string (e.g. @param1 = value1, @param2 = 'value2)'
--      @Procedure2FullName: procedure 2 full name
--      @Params2Str: procedure 2 params as string
--      @ResultSetStr: result set column specification (it is required for usage of procedure in SQL 2012+)
--      @LoopBackServerName: loopback (same server) linked server name - required to use openquery on the same server (and database)
--      @Debug: outputs debug info
--
-- =============================================
ALTER PROCEDURE [dbo].[uspCompareProcedureResults]
(
    @Procedure1FullName VARCHAR(255),
    @Params1Str VARCHAR(MAX),
    @Procedure2FullName VARCHAR(255),
    @Params2Str VARCHAR(MAX),
    @ResultSetStr VARCHAR(MAX),
    @LoopBackServerName VARCHAR(255) = 'loopback',
    @ForceShowDetails BIT = 0,
    @Debug BIT = 0
)
AS
BEGIN
    DECLARE @SQL NVARCHAR(MAX) = ''
    DECLARE @InputStr NVARCHAR(MAX)

    -- escaping string parameters
    SET @Params1Str = REPLACE(@Params1Str, '''', '''''')
    SET @Params2Str = REPLACE(@Params2Str, '''', '''''')

    SET @InputStr = @Procedure1FullName + '(' + @Params1Str + ')'

    SET @SQL = '
        DECLARE @StartTime datetime;
        DECLARE @Diff1 BIGINT;
        DECLARE @Diff2 BIGINT;

        -- executing and measuring time for the first procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R1
        FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure1FullName + ' ' + @Params1Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
        SET @Diff1 = DATEDIFF(ms, @StartTime, GETDATE());

        -- executing and measuring time for the second procedure
        SET @StartTime = GETDATE();
        SELECT * INTO #R2
        FROM OPENQUERY(' + @LoopBackServerName + ', ''set fmtonly off exec ' + @Procedure2FullName + ' ' + @Params2Str + ' WITH RESULT SETS (( ' +  @ResultSetStr + '))'');
        SET @Diff2 = DATEDIFF(ms, @StartTime, GETDATE());

        -- changing all float columns to decimal to ensure correct comparison
        DECLARE @InnerSQL NVARCHAR(MAX) = N''''

        select @InnerSQL += ''alter table #R1 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R1[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        SET @InnerSQL = N'''';
        select @InnerSQL += ''alter table #R2 alter column '' + QUOTENAME(COLUMN_NAME) + '' DECIMAL(28, 6);''
        FROM tempdb.INFORMATION_SCHEMA.COLUMNS
        where table_name like ''#R2[___]%'' and DATA_TYPE = ''float'';

        EXEC (@InnerSQL);

        -- creating temporary tables to hold result sets differences
        SELECT ''R1 \ R2'' AS [R1 \ R2], * INTO #R12 
        FROM #R1 
        WHERE 1 = 0

        SELECT ''R2 \ R1'' AS [R2 \ R1], * INTO #R21
        FROM #R1 
        WHERE 1 = 0

        -- inserting data
        INSERT INTO #R12 
        SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R1 
        EXCEPT
        SELECT ''R1 \ R2'' AS [R1 \ R2], * FROM #R2;

        INSERT INTO #R21
        SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R2 
        EXCEPT
        SELECT ''R2 \ R1'' AS [R2 \ R1], * FROM #R1;

        -- difference flag
        DECLARE @IsDiff BIT = 0
        IF EXISTS (SELECT 1 FROM #R12) OR EXISTS (SELECT 1 FROM #R21)
            SET @IsDiff = 1

        SELECT ''' + @InputStr + ''' AS ''' + LEFT(@InputStr, 128) + ''', @IsDiff AS ''Diff results'', ''R1'' AS [R1], @Diff1 AS ''Duration1 [ms]'', @Diff2 AS ''Duration2 [ms]'';

        -- showing details if a difference exists or details must be output
        if (@IsDiff  = 1 OR ' + CAST(@ForceShowDetails AS VARCHAR) + ' = 1)
        BEGIN
            SELECT ''Results for first procedure'' AS ''Results for first procedure'', * FROM #R1;
            SELECT ''Results for second procedure'' AS ''Results from the second procedure'', * FROM #R2;
            SELECT * FROM #R12
            SELECT * FROM #R21
        END
    '

    if (@Debug = 1)
    BEGIN
        PRINT '@SQL = ' + @SQL
        PRINT 'SQL len = ' + CAST(LEN(@SQL) AS VARCHAR(MAX))
    END

    EXEC (@SQL)
END

Call example:

declare @paramsStr VARCHAR(max) = '@year=2014,@month=6'
declare @resultSetStr VARCHAR(MAX) = 'kpi_id INT, kpi_value NUMERIC(18, 2)'
exec uspCompareProcedureResults 
    @Procedure1FullName = '[loopback].[DB].[usr].[get_data]', @Params1Str = @paramsStr, 
    @Procedure2FullName = '[loopback].[DB].[usr].[get_data_next_gen]', @Params2Str = @paramsStr, 
    @ResultSetStr = @resultSetStr, @ForceShowDetails = 0, @Debug = 1
GO 

Limitations / notes / known issues:

  • Requires a linked server to point to the same instance (used by openquery)
  • The procedure must return only one result set
  • All floats are converted to decimals (fixed point numbers) in order to avoid minor float differences
  • The procedure can be adapted to also run in SQL Server 2008 (just remove WITH RESULTS SETS)

The procedure does its work, but I am wondering: are there easier/better options of achieving the comparison.

2

You should look at Red Gate's tool set. They have a SQL unit testing tool called SQL Test. It might help you with a more comprehensive approach.

|improve this answer|||||
  • I took a look upon their tool and it seems very good. However, at this point I was interested in providing basic support for back-end developments. Thanks for pointing out the tool set from RedGate (currently I use only SQLSearch2). – Alexei Jan 30 '16 at 11:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.