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