Download the Code icon

I recently got the chance to work on a T-SQL challenge that involved identifying a subsequence within a sequence. I found the challenge to be interesting and therefore decided to dedicate this month's and next month’s columns to it. I'll focus on iterative solutions this month and set-based solutions next month. As always, after you read about the challenge, I urge you to try solving it yourself before looking at my solutions.

Related: "Divide and Conquer Halloween"

The Challenge

Our challenge involves finding the locations of a subsequence in a sequence. For example, consider the sequence 1, 1, 7, 5, 9, 1, 7, 1, 7, 5, 9, and the subsequence 1, 7, 5, 9. The subsequence can be found in positions 2 through 5 and 8 through 11 in the original sequence.

An example for a practical use case is identifying a certain subsequence in a DNA sequence representing a possible mutation. In such a case the four DNA nucleobases (G, A, T, and C) make the elements of the sequence instead of the integers in our example, but the principle is the same. Use the code in Listing 1 to create a table called T1 and populate it with a small set of sample data representing the full sequence.

  1. SET NOCOUNT ON;
  2. USE tempdb;
  3. GO
  4.  
  5. IF OBJECT_ID(N'dbo.T1', N'U') IS NOT NULL DROP TABLE dbo.T1;
  6. GO
  7. CREATE TABLE dbo.T1
  8. (
  9.   keycol INT NOT NULL
  10.     CONSTRAINT PK_T1 PRIMARY KEY,
  11.   val INT NOT NULL,
  12.     CONSTRAINT UNQ_T1_val_keycol UNIQUE(val, keycol)
  13. );
  14.  
  15. -- Small set of sample data to check correctness
  16. INSERT INTO dbo.T1(keycol, val) VALUES
  17.   (1, 1),(2, 1),(3, 7),(4, 5),(5, 9),(6, 1),(7, 7),(8, 1),(9, 7),(10, 5),(11, 9);

Suppose you're given the input subsequence in a table variable. Your task is to implement code that identifies the start and end positions of the occurrences of the input subsequence in the full sequence. The following code declares such a table variable and populates it with a sample input sequence with a placeholder to add your solution code:

  1. -- Input table variable @P
  2. DECLARE @P AS TABLE
  3. (
  4.   keycol INT NOT NULL
  5.     PRIMARY KEY,
  6.   val INT NOT NULL,
  7.     UNIQUE(val, keycol)
  8. );
  9.  
  10. -- sample input; try with other inputs as well
  11. INSERT INTO @P(keycol, val) VALUES
  12.   (1, 1),(2, 7),(3, 1),(4, 7);
  13.  
  14. -- <add your solution here>

Of course, you can change the sample input as you wish to test your solution with different subsequences. Figure 1 shows the desired result against the small set of sample data for the input subsequence 1,7,1,7.

  1. minkey          maxkey
  2. -----------     -----------
  3. 6               9

Figure 2 shows the desired result for the subsequence 1,7,5,9. Observe in Figure 2 that if there are multiple occurrences, you need to identify all of them.

  1. minkey          maxkey
  2. -----------     -----------
  3. 2               5
  4. 8               11

Use the small set of sample data to validate the correctness of your solution. To test your solution’s performance, you need to fill the table with a larger set of sample data. You can use the code in Listing 2 for this purpose.

  1. IF OBJECT_ID(N'dbo.GetNums', N'IF') IS NOT NULL DROP FUNCTION dbo.GetNums;
  2. GO
  3. CREATE FUNCTION dbo.GetNums(@low AS BIGINT, @high AS BIGINT) RETURNS TABLE
  4. AS
  5. RETURN
  6.   WITH
  7.     L0   AS (SELECT c FROM (SELECT 1 UNION ALL SELECT 1) AS D(c)),
  8.     L1   AS (SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B),
  9.     L2   AS (SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B),
  10.     L3   AS (SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B),
  11.     L4   AS (SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B),
  12.     L5   AS (SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B),
  13.     Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
  14.             FROM L5)
  15.   SELECT TOP(@high - @low + 1) @low + rownum - 1 AS n
  16.   FROM Nums
  17.   ORDER BY rownum;
  18. GO
  19.  
  20. -- Large set of sample data to check performance
  21. TRUNCATE TABLE dbo.T1;
  22.  
  23. INSERT INTO dbo.T1(keycol, val)
  24.   SELECT n AS keycol, ABS(CHECKSUM(NEWID())) % 10 + 1 AS val
  25.   FROM TSQL2012.dbo.GetNums(1, 10000000) AS Nums;

The code first defines a helper function called GetNums and then uses it to populate the table T1 with 10 million rows. Good luck!

Solution 1: Using a Recursive Query

As I mentioned, this month I’ll focus on iterative solutions. The first solution uses a recursive query to handle the task. Listing 3 contains the complete solution code minus the previously supplied code to declare and populate the input table variable @P.

  1. WITH C AS
  2. (
  3.   SELECT
  4.     T1.keycol AS minkey,
  5.     P.keycol AS P_keycol,
  6.     T1.keycol AS T1_keycol
  7.   FROM @P AS P
  8.     INNER JOIN dbo.T1
  9.       ON P.val = T1.val
  10.   WHERE P.keycol = 1
  11.  
  12.   UNION ALL
  13.  
  14.   SELECT
  15.     PRV.minkey,
  16.     P.keycol AS P_keycol,
  17.     T1.keycol AS T1_keycol
  18.   FROM C AS PRV
  19.     INNER JOIN @P AS P
  20.       ON P.keycol = PRV.P_keycol + 1
  21.     INNER JOIN dbo.T1
  22.       ON T1.keycol = PRV.T1_keycol + 1
  23.       AND P.val = T1.val
  24. )
  25. SELECT minkey, T1_keycol AS maxkey
  26. FROM C
  27. WHERE P_keycol = (SELECT MAX(keycol) FROM @P);

The first query in the CTE body is the anchor member:

  1. SELECT
  2.   T1.keycol AS minkey,
  3.   P.keycol AS P_keycol,
  4.   T1.keycol AS T1_keycol
  5. FROM @P AS P
  6.   INNER JOIN dbo.T1
  7.     ON P.val = T1.val
  8. WHERE P.keycol = 1

The query filters only the first element from the input subsequence in the table variable @P, joining @P and T1 to identify all occurrences of the first subsequence value in the sequence. The key of this first value is identified as the potential minimum key (aliased minkey). The keys of the subsequence and the sequence are also returned and aliased as P_keycol and T1_keycol, respectively.

The second query in the CTE body is the recursive member:

  1. SELECT
  2.   PRV.minkey,
  3.   P.keycol AS P_keycol,
  4.   T1.keycol AS T1_keycol
  5. FROM C AS PRV
  6.   INNER JOIN @P AS P
  7.     ON P.keycol = PRV.P_keycol + 1
  8.   INNER JOIN dbo.T1
  9.     ON T1.keycol = PRV.T1_keycol + 1
  10.     AND P.val = T1.val

This query joins the previous round’s result (aliased as PRV) with the table variable @P (aliased as P) and the table T1. The purpose of these joins is to identify the next value in both the subsequence and the sequence and produce a match only if the next value in both is the same (P.val = T1.val). This recursive query runs repeatedly until no more matches are found.

Finally, the outer query filters only the rows from the CTE that represent surviving last subsequence elements (P_keycol equals maximum key in @P), meaning the entire subsequence was found:

  1. SELECT minkey, T1_keycol AS maxkey
  2. FROM C
  3. WHERE P_keycol = (SELECT MAX(keycol) FROM @P);

This solution is pretty straightforward, but unfortunately it doesn’t perform very well. The reason is that the query execution performs a lot of I/O operations against the internal worktable (spool) that it generates to store the intermediate result sets. You don’t have any control over this process and no say about the indexing of this worktable or any of its other aspects. It took 11 seconds for this solution to complete on my system, applying 13 million logical reads.

Solution 2: Using a Loop

The second solution implements the same approach as in the previous solution, but instead of using a recursive CTE I use an explicit loop. Listing 4 contains the complete solution code.

  1. CREATE TABLE #T
  2. (
  3.   minkey    INT NOT NULL,
  4.   P_keycol  INT NOT NULL,
  5.   T1_keycol INT NOT NULL,
  6.   PRIMARY KEY(P_keycol, T1_keycol)
  7. );
  8.  
  9. DECLARE
  10.   @i AS INT = 1,
  11.   @cnt AS INT = (SELECT MAX(keycol) FROM @P);
  12.  
  13. INSERT INTO #T(minkey, P_keycol, T1_keycol)
  14.   SELECT
  15.     T1.keycol AS minkey,
  16.     P.keycol AS P_keycol,
  17.     T1.keycol AS T1_keycol
  18.   FROM @P AS P
  19.     INNER JOIN dbo.T1
  20.       ON P.val = T1.val
  21.   WHERE P.keycol = 1;
  22.  
  23. WHILE @@rowcount > 0
  24. BEGIN
  25.   SET @i += 1;
  26.  
  27.   INSERT INTO #T(minkey, P_keycol, T1_keycol)
  28.     SELECT
  29.       PRV.minkey,
  30.       P.keycol AS P_keycol,
  31.       T1.keycol AS T1_keycol
  32.     FROM #T AS PRV
  33.       INNER JOIN @P AS P
  34.         ON P.keycol = PRV.P_keycol + 1
  35.       INNER JOIN dbo.T1
  36.         ON T1.keycol = PRV.T1_keycol + 1
  37.         AND P.val = T1.val
  38.     WHERE PRV.P_keycol = @i - 1
  39.     /* OPTION(RECOMPILE) */;
  40. END;
  41.  
  42. SELECT minkey, T1_keycol AS maxkey
  43. FROM #T
  44. WHERE P_keycol = @cnt;
  45.  
  46. DROP TABLE #T;

The code starts by defining a temporary table called #T to store the intermediate results. Then the code declares and initializes a couple of variables: @i is an iteration counter initialized with 1 and later will be incremented by 1 in each iteration of the loop, and @cnt holds the number of elements in the subsequence.

Next, the INSERT statement handles the task that in the previous solution was handled by the anchor member—namely, filters the first element in the subsequence and joins to T1 to find all values in the sequence representing the potential start of the subsequence. The result is stored in #T.

Then the code runs a loop that keeps going as long as the previous INSERT returned some rows. In each iteration, the INSERT handles the task that in the previous solution was handled by the recursive member—namely, matches to the elements from the previous round the next element from the sequence if it’s the expected next element according to the subsequence.

Finally, the last query returns the start and end keys of all occurrences of the subsequence in the sequence (those where P_keycol = @cnt).

When I ran the code in Listing 4 as provided (without the RECOMPILE query option), I got the same plan for all executions of the INSERT in the body of the loop. Figure 3 shows the resulting plan.

Plan for Queries in Listing 4 Without RECOMPILE

The first plan was generated for an estimate of about 1 million matches; it was then reused in all subsequent iterations. The problem is that all subsequent iterations return a far smaller number of matches, and the existing plan isn't the most efficient one for them. This query took 10 seconds to complete on my system, applying 3.5 million logical reads.

You can improve the performance of the solution by using the RECOMPILE query option in the INSERT statement in the loop’s body. This will force SQL Server to create a new plan for each iteration of the statement, one that's more suitable based on new estimates. I uncommented the RECOMPILE query option in Listing 4 and ran the code again.

I got different plans for the first iteration of the INSERT statement (suitable for a large number of matches) and for all remaining iterations (suitable for a small number of matches), as Figure 4 shows.

Plans for Queries in Listing 4 With RECOMPILE

This time the query completed in 7 seconds, doing a bit more logical reads (3.85 million), but overall it performed better.

Solution 3: Using the Divide and Conquer Halloween Approach

Even with the RECOMPILE option added in the previous solution, there’s still room for improvement. Notice in the plans in both Figure 3 and Figure 4 the Table Spool (Eager Spool) operators. The optimizer added those operators to the plans for Halloween Protection purposes so as not to handle the same rows more than once. You can find more information on Halloween Protection in an excellent series of articles by Paul White about Halloween Protection.

In "Divide and Conquer Halloween," I covered the generic form of the iterative algorithm used in this article—the divide and conquer algorithm. I explained the need for Halloween Protection when the source and target of the iterative INSERT statement are the same. I provided a solution that implements the same algorithm but avoids the need for Halloween Protection simply by using two temporary tables instead of one, alternating between them so that in each round the source and target are different. I named this approach Divide and Conquer Halloween. Listing 5 contains the solution code that implements this approach.

  1. CREATE TABLE #T1
  2. (
  3.   minkey    INT NOT NULL,
  4.   P_keycol  INT NOT NULL,
  5.   T1_keycol INT NOT NULL,
  6.   PRIMARY KEY(P_keycol, T1_keycol)
  7. );
  8.  
  9. CREATE TABLE #T2
  10. (
  11.   minkey    INT NOT NULL,
  12.   P_keycol  INT NOT NULL,
  13.   T1_keycol INT NOT NULL,
  14.   PRIMARY KEY(P_keycol, T1_keycol)
  15. );
  16.  
  17. DECLARE
  18.   @i AS INT = 1,
  19.   @cnt AS INT = (SELECT MAX(keycol) FROM @P);
  20.  
  21. INSERT INTO #T1(minkey, P_keycol, T1_keycol)
  22.   SELECT
  23.     T1.keycol AS minkey,
  24.     P.keycol AS P_keycol,
  25.     T1.keycol AS T1_keycol
  26.   FROM @P AS P
  27.     INNER JOIN dbo.T1
  28.       ON P.val = T1.val
  29.   WHERE P.keycol = 1;
  30.  
  31. WHILE @@rowcount > 0
  32. BEGIN
  33.   SET @i += 1;
  34.  
  35.   IF @i % 2 = 0
  36.   BEGIN
  37.     TRUNCATE TABLE #T2;
  38.  
  39.     INSERT INTO #T2(minkey, P_keycol, T1_keycol)
  40.       SELECT
  41.         PRV.minkey,
  42.         P.keycol AS P_keycol,
  43.         T1.keycol AS T1_keycol
  44.       FROM #T1 AS PRV
  45.         INNER JOIN @P AS P
  46.           ON P.keycol = PRV.P_keycol + 1
  47.         INNER JOIN dbo.T1
  48.           ON T1.keycol = PRV.T1_keycol + 1
  49.           AND P.val = T1.val
  50.       OPTION(RECOMPILE);
  51.   END;
  52.   ELSE
  53.   BEGIN
  54.     TRUNCATE TABLE #T1;
  55.  
  56.     INSERT INTO #T1(minkey, P_keycol, T1_keycol)
  57.       SELECT
  58.         PRV.minkey,
  59.         P.keycol AS P_keycol,
  60.         T1.keycol AS T1_keycol
  61.       FROM #T2 AS PRV
  62.         INNER JOIN @P AS P
  63.           ON P.keycol = PRV.P_keycol + 1
  64.         INNER JOIN dbo.T1
  65.           ON T1.keycol = PRV.T1_keycol + 1
  66.           AND P.val = T1.val
  67.       OPTION(RECOMPILE);
  68.   END;
  69. END;
  70.  
  71. IF @i % 2 = 0
  72.   SELECT minkey, T1_keycol AS maxkey
  73.   FROM #T1
  74.   WHERE P_keycol = @cnt;
  75. ELSE
  76.   SELECT minkey, T1_keycol AS maxkey
  77.   FROM #T2
  78.   WHERE P_keycol = @cnt;
  79.  
  80. DROP TABLE #T1, #T2;

Figure 5 shows the plans for the different executions of the INSERT statements in the body of the loop, with no special treatment added for Halloween Protection.

Plans for Queries in Listing 5

This solution completed in 3.5 seconds on my system, applying only 370,000 logical reads.

Until Next Month

The performance of the iterative solution improved quite dramatically with the Divide and Conquer Halloween approach. This is the best performance I managed to achieve with an iterative solution in T-SQL. The next challenge is to find a set-based solution that's as efficient as possible. You have a month to work on solutions of your own, before I present several possible solutions. Good luck, and have fun!

Related: Identifying a Subsequence in a Sequence, Part 2