title: Recursive UNION ALL to parse string taking very long
body: <p>I am trying to speed up this recursive UNION ALL as shown below, but I cannot think how to do it. Maybe a while loop but I am not sure. The movement data is stored as one long string of encoded movement data and the script recursively calls the select statement to parse/extract this data and then it is all casted. </p>
  
  <p>I would really like to understand more about speeding up recursive union all's or finding another way. I don't believe indexing is a problem so this is not really a possible solution.</p>
  
  <p>"RouteData" is the long string that is parsed by fixed length intervals.</p>
  
  <p>Here is a sample of the encoded data:</p>
  
  <pre><code>ScenarioPID : 3
  LegID  :1
  RoutePart : 0x0000000000000000000100000000000000000000000000
  RouteData : 0x40323AAAAAAAAAAB00013FA6FFD663CCA3310000001F00403 ... (goes on)
  cnt : 37
  sequence : 1
  StartTime : 8828
  </code></pre>
  
  <hr>
  
  <p>The final output data looks like this for one track.</p>
  
  <pre><code>ScenarioPID LegID   sequence    TrackID Offset  TimeOffset  Length  StartTime
  3             1        1           1      0         0        6300    8828
  3             1        2           1      0.0449    31       6300    8828
  3             1        3           1      0.8942    325      6300    8828
  3             1        4           1      0.9736    356      6300    8828
  3             1        5           1      1         369      6300    8828
  </code></pre>
  
  <hr>
  
  <pre><code>USE nss_demo;
  
  DECLARE @scenario1 INT;
  DECLARE @DAY_START INT;
  DECLARE @DAY_END INT;
  DECLARE @TRAIN_TYPE VARCHAR(50);
  DECLARE @TRACK_TYPE VARCHAR(50);
  
  SET @scenario1 = 3;
  SET @DAY_START = 0;
  SET @DAY_END = 7;
  SET @TRAIN_TYPE = 'Empty Train';
  SET @TRACK_TYPE = 'East Track';
  
  DECLARE @KM_START INT;
  DECLARE @KM_END INT;
  
  SET @KM_START = 0;
  SET @KM_END = 200;
  
  WITH movement
       AS (SELECT m.scenariopid,
                  m.legid,
                  Substring(routedata, 1, 23)                   AS RoutePart,
                  Substring(routedata, 24, Len(routedata) - 23) AS RouteData,
                  Len(routedata) / 23 - 1                       AS cnt,
                  1                                             AS sequence,
                  m.starttime
           FROM   output.movement m
           WHERE  scenariopid = @scenario1
                  AND m.starttime BETWEEN ( @DAY_START * 86400 ) AND
                                          ( @DAY_END * 86400 )
           UNION ALL
           SELECT scenariopid,
                  legid,
                  Substring(m1.routedata, 1, 23)                      AS RoutePart
                  ,
                  Substring(m1.routedata, 24,
                  Len(m1.routedata) - 23) AS RouteData,
                  Len(m1.routedata) / 23 - 1                          AS cnt,
                  sequence + 1                                        AS sequence,
                  m1.starttime
           FROM   movement m1
           WHERE  m1.cnt &gt; 0),
       casttable
       AS (SELECT tt.scenariopid,
                  tt.legid,
                  tt.sequence,
                  tt.trackid,
                  tt.offset,
                  tt.timeoffset,
                  tr.[length],
                  tt.starttime
           FROM   (SELECT scenariopid,
                          legid,
                          sequence,
                          Cast(trackidbin AS SMALLINT) AS TrackID,
                          Sign(Cast(offsetbin AS BIGINT)) *
                          ( 1.0 +
                          ( Cast(offsetbin AS BIGINT) &amp; 0x000FFFFFFFFFFFFF ) *
                          Power(Cast(2 AS FLOAT), -52) )
                          *
                          Power(Cast(2 AS FLOAT), ( Cast(offsetbin AS BIGINT) &amp;
                                                    0x7ff0000000000000
                                                  ) /
                          0x0010000000000000
                          - 1023)                      AS Offset,
                          Cast(timebin AS INT)         AS TimeOffset,
                          starttime                    AS StartTime
                   FROM   (SELECT legid,
                                  scenariopid,
                                  sequence,
                                  Substring(routepart, 9, 2)  AS TrackIDBin,
                                  Substring(routepart, 11, 8) AS OffsetBin,
                                  Substring(routepart, 19, 4) AS TimeBin,
                                  starttime
                           FROM   movement) t) tt
                  INNER JOIN input.track tr
                          ON tr.trackid = tt.trackid
                             AND tr.scenariopid = tt.scenariopid)
  SELECT *
  FROM   casttable
  ORDER  BY legid,
            sequence
  OPTION (maxrecursion 20000) 
  </code></pre>