title: Recursive CTE is very slow despite minimal records
body: <p>I'm trying to write a recursive CTE query similar to <a href="http://www.mcpressonline.com/sql/techtip-combining-multiple-row-values-into-a-single-row-with-sql-in-db2-for-i.html" rel="nofollow noreferrer">this</a> and when limiting my CTE records to an extremely small samples size the results are correct.  I'm assuming it remains correct with more records.  However, if I limit the CTE to ~5000 records (using a where clause) I'm getting 11 second execution times overall.  If I increase that to ~24,000 records (using a where clause) that jumps up to 3 minute execution times.</p>
  
  <p>I haven't run the query for longer than that because I don't want to eat up system resources.</p>
  
  <p>I know I can avoid using a CTE all together for this query, but the intention is for this particular query to be apart of a larger query, so it would be more readable if I could use a CTE.  I'm also pretty confident recursive CTE's are capable of handling much larger data sets, so I'm curious if someone notices something I'm missing.</p>
  
  <p>Here is the query (tables and fields name have been changed):</p>
  
  <pre><code>WITH TEMP (COL1, COL2, CURR, PREV) AS (
      SELECT  COL1, 
              COL2,
              ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) AS CURR,
              ROW_NUMBER() OVER (PARTITION BY COL1 ORDER BY COL2) -1 AS PREV
      FROM    MYLIB.MYTABLE
      WHERE   COLDATE &gt; 20150101 -- Produces about 5000 records
      -- WHERE COLDATE &gt; 20140101 -- Produces about 24000 records
  
  )
  SELECT  COL1, MAX(TRIM(L ',' FROM CAST(SYS_CONNECT_BY_PATH(COL2, ',') AS VARCHAR(256))))
  FROM        TEMP
  START WITH  CURR = 1
  CONNECT BY  COL1 = PRIOR COL1 
  AND         PREV = PRIOR CURR
  GROUP BY    COL1;
  </code></pre>
  
  <p>Note*: COLDATE is only being used to limit the records in the CTE for testing purposes.</p>
  
  <p>The CTE itself doesn't seem to be the issue, I can do a <code>SELECT * FROM TEMP;</code> and it is instantaneous.  I believe I may be using <code>SYS_CONNECT_BY_PATH</code> and/or <code>CONNECT BY</code> incorrectly (IE: I can modify the query to be more efficient)</p>
  
  <p>The link I provided goes into more detail, but what I'm trying to achieve is to turn this:
  <img src="https://i.stack.imgur.com/U46WY.png" alt="Original Table">
  Into this:
  <img src="https://i.stack.imgur.com/II9HW.png" alt="Return Result Set"> On the fly.  </p>
  
  <p>Which is what it is doing at the moment, albeit very slow.</p>
  
  <p>Any insight is greatly appreciated.</p>