title: SQl Server CTE RECURSIVE how to improve performance for large data?
body: <p>I have a bad performance in my CTE Recursive script. it took 1:23 minutes to execute with 12925 records. can I add some paging, or anything to improve the performance. here's my code</p>
  
  <pre><code>WITH cte(GenealogyID,GenealogySponsorId,GenealogyTypeId,CustomerId,Level) AS
  (   
      SELECT
          e.Id AS GenealogyID, 
          e.GenealogySponsorId,
          e.GenealogyTypeId, 
          e.CustomerId,
          0 AS Level 
      FROM Genealogy AS e
      WHERE e.CustomerId = 53591  AND e.GenealogyTypeId = 50
  
      UNION ALL 
  
      SELECT 
          child.Id AS GenealogyID,  
          child.GenealogySponsorId, 
          child.GenealogyTypeId, 
          child.CustomerId,
          Level+1 
      FROM Genealogy child 
      JOIN cte parent on parent.GenealogyID = child.GenealogySponsorId
      where child.GenealogyTypeId &gt; 0 AND child.CustomerID &lt;&gt; 53591
  )
  select count(*) from cte
  </code></pre>