title: Hierarchical Table and Max Level
body: <p>I am using following function in sql to get levels of my nodes but currently my nodes become more than 20000 ,So running this function takes 50 sec ,how can I optimize it ?</p>
  
  <pre><code>   WITH cteLevels
  AS
  (
  SELECT
    TopicId
    ,ParentId
  
    ,1        AS [Level]
  FROM   Accounting.Topics
  WHERE parentId = 0 
  
  
  UNION ALL
  
  SELECT
    Accounting.Topics.topicId
    ,Accounting.Topics.Parentid
     ,[Level] + 1 AS [Level]
   FROM   Accounting.Topics
  
         INNER JOIN cteLevels
           ON ( Accounting.Topics.ParentId = cteLevels.TopicID ) 
  
  )
  
  -- Viewing Data
  SELECT
    *
  FROM   cteLevels
  </code></pre>