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.
