-- Use Test Output Mode
SET NOCOUNT ON

USE TEST_TVP
GO

-------------------------------------------------------------------------------
-- TESTS
-------------------------------------------------------------------------------
DECLARE @testrepeat int = 1;
WHILE @testrepeat < 11
BEGIN
	DECLARE @filters filtertable;
	DECLARE @xmlfilter xml;
	DECLARE @xmlstring varchar(max);
	DECLARE @num_filters int;
	DECLARE @runcount int = 1;
	DECLARE @repeatcount int;
	DECLARE @numrepeats int = 5;
	DECLARE @startdatetime datetime2(7);

	WHILE @runcount < 14
	BEGIN
		SET @num_filters = CASE @runcount WHEN 1 THEN 1
														WHEN 2 THEN 2
														WHEN 3 THEN 3
														WHEN 4 THEN 4
														WHEN 5 THEN 5
														WHEN 6 THEN 10
														WHEN 7 THEN 25
														WHEN 8 THEN 50
														WHEN 9 THEN 100
														WHEN 10 THEN 250
														WHEN 11 THEN 500
														WHEN 12 THEN 1000
														WHEN 13 THEN 2000
														ELSE 1
														END ;
		DELETE FROM  @filters;
		
		INSERT INTO @filters ( filterid )
		SELECT TOP (@num_filters) id
		FROM dbo.TestData 
		ORDER BY NEWID();

		SET @xmlstring = ( SELECT filterid AS [@id] FROM @filters FOR XML PATH('filter'), ROOT( 'root') ) ;
		SET @xmlfilter =  CAST (@xmlstring AS XML) ;

		SELECT @repeatcount = 0, @startdatetime = SYSDATETIME();
		WHILE @repeatcount < @numrepeats
		BEGIN
			EXEC dbo.GetDetailsWithTVP @filters ;
			SET @repeatcount += 1 ;
		END ;
		INSERT INTO dbo.TestResults ( num_Filters, num_repeats, Method, [nanoseconds] )
		SELECT @num_filters, @numrepeats, 'TVP', DATEDIFF(ns,@startdatetime,SYSDATETIME()) ;

		SELECT @repeatcount = 0, @startdatetime = SYSDATETIME() ;
		WHILE @repeatcount < @numrepeats
		BEGIN
			EXEC dbo.GetDetailsWithXML @xmlfilter ;
			SET @repeatcount += 1 ;
		END ;
		INSERT INTO dbo.TestResults ( num_Filters, num_repeats, Method, [nanoseconds] )
		SELECT @num_filters, @numrepeats,  'XML', DATEDIFF(ns,@startdatetime,SYSDATETIME()) ;

		SELECT @repeatcount = 0, @startdatetime = SYSDATETIME() ;
		WHILE @repeatcount < @numrepeats
		BEGIN
			EXEC dbo.GetDetailsWithXMLExists @xmlfilter ;
			SET @repeatcount += 1 ;
		END ;
		INSERT INTO dbo.TestResults ( num_Filters, num_repeats, Method, [nanoseconds] )
		SELECT @num_filters, @numrepeats, 'XML Exists', DATEDIFF(ns,@startdatetime,SYSDATETIME()) ;

		SELECT @repeatcount = 0, @startdatetime = SYSDATETIME();
		WHILE @repeatcount < @numrepeats
		BEGIN
			EXEC dbo.GetDetailsWithOpenXML @xmlstring ;
			SET @repeatcount += 1 ;
		END ;
		INSERT INTO dbo.TestResults ( num_Filters, num_repeats, Method, [nanoseconds] )
		SELECT @num_filters, @numrepeats, 'OpenXML', DATEDIFF(ns,@startdatetime,SYSDATETIME()) ;

		SET @runcount += 1 ;
	END ;
	SET @testrepeat +=1 ;
END ;

/*

-- Detail
SELECT num_Filters, num_repeats, Method, AVG(nanoseconds) AS [Average], MAX(nanoseconds) AS [Maximum], MIN(nanoseconds) AS [Minimum]
FROM dbo.TestResults
GROUP BY num_Filters, num_repeats, Method
ORDER BY num_Filters, AVG(nanoseconds) ;

-- Summary
SELECT Method, AVG(nanoseconds) AS [Average], MAX(nanoseconds) AS [Maximum], MIN(nanoseconds) AS [Minimum]
FROM dbo.TestResults
GROUP BY Method
ORDER BY AVG(nanoseconds) ;

*/