CREATE OR ALTER PROCEDURE dbo.usp_stringsearch_test1 @rows BIGINT = 1000, -- Rows of test data @puff BIGINT = 1, -- how many times to replicate a newid as "junk text" @gaps VARCHAR(8000) = '1', -- comma-delimited list of gap parameters (@G) @C VARCHAR(100) = 'X', -- character(s) to search for @L BIGINT = 1, -- minimum acceptable value @H BIGINT = 10, -- max acceptable value @max BIGINT = 10, -- maximum number of @C to stugff into the string @mode BIGINT = 2, -- (1,2: loop version; 3,4,5 fnTally versions) @out BIGINT = 1, -- 0 = recommendation only, 1 = include Analysis @I BIGINT = 1, -- Number of test iterations to run (more =better accuracy) @clean BIT = 0 -- Drop the temp table AS /* Created on 20201006 by Alan Burstein. This procedure is for testing and tuning your "tuning parameter". This is a crude, quickly assembled tuning procedure can serve as a template for future, more sophisticated tuning procs for more complex problems. Parameters: @rows - Sample data rows (@rows should be at least as high as @H for best test results) @puff - Max number of NEWIDs to replicate for "string noise"; longer strings=more work @gaps - (20,25,50), comma delimited list of gap parameters to test @C - Search character one+ (up to 100) to be replicated for your search pattern @L - Min (lower bound) instances of @C to search for; @C='X',@L=4 returns "XXXX" @H - Max (upper bound) instances of @C to search for; @C='X',@L=6 returns "XXXXXX" @max - Maximum length of the sample search string added to the sample data @I - Number of test run iterations (more iterations = more accuracy) @out - 0 = Analysis only, 1 = suggest best tuning param value Summary of different modes (1,2: loop version; 3,4: fnTally exact, 5,6,7=1 reduction,8,9,10 = 2 reductions) */ BEGIN ;--==== 0. Prep ;--======================================================================================= BEGIN -- collapse/expand for debugging SET NOCOUNT ON; --==== 0.1. Print which "mode" you are running IF @mode=1 PRINT 'Mode #1: '+ 'Scalar recursive - Procedural solution, dynamic (approximation + exact) - SLOW!!'; IF @mode=2 PRINT 'Mode #2: '+ 'Loop/Scalar Recursive - Procedural solution: approximation values to temp table'; IF @mode=3 PRINT 'Mode #3: '+'fnTally no sort - exact, serial'; IF @mode=4 PRINT 'Mode #4: '+'fnTally no sort - exact, parallel (via make_parallel)'; IF @mode=5 PRINT 'Mode #5: fnTally no sort - Dynamic (approx + exact), serial'; IF @mode=6 PRINT 'Mode #6: fnTally no sort - Dynamic - parallel'; IF @mode=7 PRINT 'Mode #7: fnTally no sort - Dynamic - parallel + string reduction'; IF @mode=8 PRINT 'Mode #8: fnTally no sort - Dynamic - parallel + 2 approximations'; IF @mode=9 PRINT 'Mode #9: fnTally no sort - Dynamic8k - serial + 2 approximations'; IF @mode=10 PRINT 'Mode #10: fnTally no sort - Dynamic8k - parallel+2 approximations'; --==== 0.2. Misc IF OBJECT_ID('tempdb..#perftest') IS NOT NULL DROP TABLE #perftest; CREATE TABLE #perftest (gap BIGINT, runtime BIGINT); DECLARE @II BIGINT = 1; END; ;--==== 1. Start test ;--======================================================================================= WHILE @II <= @I BEGIN --==== 1.0. Prep (note: in the real-world you would take a sampling from real data here) IF OBJECT_ID('tempdb..##strings') IS NOT NULL DROP TABLE ##strings; CREATE TABLE ##strings (stringId INT IDENTITY PRIMARY KEY, string VARCHAR(8000)); INSERT ##strings(string) SELECT REPLICATE(NEWID(),ABS(CHECKSUM(NEWID())%@puff/2)+1)+ REPLICATE(@C,ABS(CHECKSUM(NEWID())%@max)+5)+ REPLICATE(NEWID(),ABS(CHECKSUM(NEWID())%@puff/2)+1) FROM dbo.fnTally(1,@rows) AS t; --==== 1.1. Test Prep: cleanup and create gap parameters DECLARE @G BIGINT, @ST DATETIME, @X BIGINT; DECLARE @gaptbl TABLE (G BIGINT PRIMARY KEY); INSERT @gaptbl(G) SELECT s.Item FROM dbo.DelimitedSplit8K(@gaps,',') AS s; --==== 1.2. Start the test WHILE EXISTS (SELECT * FROM @gaptbl) BEGIN -- 1.2.1. Retrieve the next gap value SELECT TOP(1) @G=gt.G FROM @gaptbl AS gt ORDER BY gt.G; DELETE FROM @gaptbl WHERE G=@G; -- 1.2.2. Temp table cleanup IF OBJECT_ID('tempdb..#strings2') IS NOT NULL DROP TABLE #strings2; -- 1.2.3. Set Start Time SELECT @ST=GETDATE(); IF @mode=1 -- Loop/Scalar Recursive - SLOW BEGIN SELECT @X = dbo.stringSearchV1(@C,s.string,f.L,f2.H,1) FROM ##strings AS s CROSS APPLY (VALUES(dbo.stringSearchV1(@C,s.string,@L,@H,@G))) AS f(L) CROSS APPLY (VALUES(f.L+IIF(f.L IN (0,@H),0,@G-1))) AS f2(H); END; IF @mode=2 -- Scalar recursive - Procedural solution, dynamic (approximation+exact) BEGIN -- Isolate the upper and lower bounds, store in #strings2: SELECT gap = @G, s.string, s.stringId, L = f.L, H = f.L+IIF(f.L IN (0,@H),0,@G-1) INTO #strings2 FROM ##strings AS s CROSS APPLY (VALUES(dbo.stringSearchV1(@C,s.string,@L,@H,@G))) AS f(L); -- 1.2.4.2. Assign the function output to the variable @X SELECT @X = dbo.stringSearchV1(@C,s.string,s.L,s.H,1) FROM #strings2 AS s END; IF @mode=3 -- fnTally no sort - exact algorithm, Serial BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,1) AS f WHERE f.L >= @L OPTION (MAXDOP 1); END; IF @mode=4 -- fnTally no sort - exact parallel (via make_parallel) BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,1) AS f CROSS APPLY dbo.make_parallel() AS nada WHERE f.L >= @L END; IF @mode=5 -- fnTally no sort - dynamic (approximation + exact), Serial BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,@G) AS f0 CROSS APPLY dbo.stringSearch(@C,s.String,f0.L,f0.H,1) AS f -- CROSS APPLY dbo.stringSearch(@C,SUBSTRING(s.String, -- f0.Idx, LEN(s.string)+1),f0.L,f0.H,1) AS f WHERE f.L >= @L OPTION (MAXDOP 1); END; IF @mode=6 -- fnTally no sort - dynamic, parallel BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,@G) AS f0 CROSS APPLY dbo.stringSearch(@C,s.String,f0.L,f0.H,1) AS f WHERE f.L >= @L OPTION (QUERYTRACEON 8649); END; IF @mode=7 -- fnTally no sort - dynamic, parallel, string reduce BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,@G) AS f0 CROSS APPLY dbo.stringSearch(@C,SUBSTRING(s.String, f0.Idx, LEN(s.string)+1),f0.L,f0.H,1) AS f WHERE f.L >= @L OPTION (QUERYTRACEON 8649); END; --== Modes 8, 9 and 10 use a two-phase reduction DECLARE @G2 BIGINT = @G/3+1; -- split by 1/3rd, add one for a minimum of 2 IF @mode=8 -- fnTally no sort - dynamic, parallel, two reductions BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,@G) AS f0 CROSS APPLY dbo.stringSearch(@C,s.String,f0.L,f0.H,@G2) AS f1 CROSS APPLY dbo.stringSearch(@C,s.String,f1.L, IIF(f1.L+2<=@H,f1.L+2,@H),1) AS f WHERE f.L >= @L OPTION (QUERYTRACEON 8649); END; IF @mode=9 -- fnTally no sort - dynamic, parallel, two reductions BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch8k(@C,s.String,@L,@H,@G) AS f0 CROSS APPLY dbo.stringSearch8k(@C,s.String,f0.L,f0.H,@G2) AS f1 CROSS APPLY dbo.stringSearch8k(@C,s.String,f1.L, IIF(f1.L+2<=@H,f1.L+2,@H),1) AS f WHERE f.L >= @L OPTION (MAXDOP 1); END; IF @mode=10 -- fnTally no sort - dynamic, parallel, two reductions BEGIN SELECT @X = f.L FROM ##strings AS s CROSS APPLY dbo.stringSearch(@C,s.String,@L,@H,@G) AS f0 CROSS APPLY dbo.stringSearch(@C,s.String,f0.L,f0.H,@G2) AS f1 CROSS APPLY dbo.stringSearch(@C,s.String,f1.L, IIF(f1.L+2<=@H,f1.L+2,@H),1) AS f WHERE f.L >= @L OPTION (QUERYTRACEON 8649); END; ;-- 1.2.4. Collect performance metrics INSERT #perftest VALUES(@G,DATEDIFF(MS,@st,GETDATE())); END; ;--==== 1.3. Next Iteration SET @i-=1; END; --==== 2. Test Results ;--======================================================================================= ; ;--==== 2.1. Return a basic analysis SELECT f.Gap, f.Total, [Min]=f.Mn, [Max]=f.Mx, [TAvg]=AVG(f.Total) OVER (ORDER BY f.Total) FROM ( SELECT p.gap, SUM(p.runtime), MIN(p.runtime), MAX(p.runtime), AVG(p.runtime) FROM #perftest AS p GROUP BY p.gap ) AS f(Gap,Total,Mn,Mx,Av); --==== 2.2. Recomendation based on the best gap parameter IF @out = 1 BEGIN SELECT TOP(1) recommended = f.Gap -- TOP can also take a param FROM ( SELECT p.gap, SUM(p.runtime), MIN(p.runtime), MAX(p.runtime), AVG(p.runtime) FROM #perftest AS p GROUP BY p.gap ) AS f(Gap,Total,Mn,Mx,Av) ORDER BY f.Total; END; ; ;--==== 3. Temp table cleanup ;--======================================================================================= IF @mode = 2 DROP TABLE #strings2 IF @clean = 1 DROP TABLE ##strings; /* Example test (start with a small number of rows to see how things scale) DECLARE @rows BIGINT = 2000, -- Rows of test data @puff BIGINT = 5, -- how many times to replicate a newid as "junk text" @gaps VARCHAR(8000) = '5,10,15,20,25,30,35', -- Gap parameters to test with @C VARCHAR(100) = 'X', -- character(s) to search for @L BIGINT = 1, -- minimum acceptable value @H BIGINT = 1000, -- max acceptable value @max BIGINT = 1100, -- maximum number of @C to stugff into the string @I BIGINT = 5, -- iterations @out BIGINT = 1; -- general performance details of test run (0=no,1=yes) --==== 8K - Dynamic with 1 reduction - Serial EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 5, @out, @I, 1; --==== VARCHAR MAX - Dynamic with 1 reduction(#6) & 1 string reduction(#7) - PARALLEL SELECT @puff *= 10/*500*/, @I=5, @H=2000, @max=2000; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 6, @out, @I, 1; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 7, @out, @I, 1; --==== 8K & VARCHAR MAX - Dynamic with 2X reductions (@G2=@G/2+1) - PARARLLEL SELECT @gaps = '100,250,500,800,1000,2000', @I=10; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 8, @out, @I, 1; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 10, @out, @I, 1; --==== Two more tests SELECT @gaps = '500,800,1000,1500,2000', @I=10,@H=2000, @max=2000; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 8, @out, @I, 1; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 10, @out, @I, 1; SELECT @gaps = '800,1000,1500,2000,3000', @I=10,@H=3000, @max=3000; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 8, @out, @I, 1; EXEC dbo.usp_stringsearch_test1 @rows, @puff, @gaps, @C, @L, @H, @max, 10, @out, @I, 1; */ END; GO