How to Optimize depth-first-search query

[sreenigaddam]

Hi All,

We are looking for help to optimize the depth-first search query, our requirement is start from root and fetch the entire hierarchy and transfer the data , below query works, but not performing on volume data 

here is the actual query and sample data 
Any pointers would be greatly helpful . 
thanks a lot  .. 

With FILES(FILE_ID, REVISION, PARENT_ID) As (Select A.FILE_ID, A.REVISION,
      A.PARENT_ID
    From (Select F.FILE_ID, F.REVISION, F.PARENT_ID, F.IS_DELETED, Row_Number()
          Over (Partition By F.LOOKUP_KEY, F.PATH_DEPTH Order By F.REVISION_TIME
          Desc) RANK From DEMO_FILE_REVS F
        Where F.SERVICE_INSTANCE_ID = 1 And F.NAMESPACE = 'content:catalog' And
          F.REVISION_TIME <= SYSDATETIME()) A
    Where A.RANK = 1 And A.IS_DELETED = 0),
  FILE_TREE(FILE_ID, REVISION, PARENT_ID, LVL, PATHTEXT) As (Select F.FILE_ID,
      F.REVISION, F.PARENT_ID, 0, '\\' + Cast(F.FILE_ID As NVARCHAR(MAX)) +
      '\' As PATHTEXT From FILES F
    Where F.PARENT_ID = -1 Union All Select F.FILE_ID, F.REVISION, F.PARENT_ID,
      FT.LVL + 1, FT.PATHTEXT + Cast(F.FILE_ID As VARCHAR(MAX)) + '\'
    From FILES F,
      FILE_TREE FT
    Where F.PARENT_ID = FT.FILE_ID) Select F.SERVICE_INSTANCE_ID, F.FILE_ID,
  F.PARENT_ID, F.FULL_PATH, F.NAME, F.NAMESPACE, F.REVISION, F.REVISION_TIME,
  F.IS_DELETED, F.ITEM_TYPE, F.CONTENT_TYPE, F.OBJ_TYPE, F.OBJ_TYPE_VER,
  F.TARGET_PATH, F.RENAMED_FROM_PATH, F.OWNED_BY_NAME, F.OWNED_BY_TYPE,
  F.SYS_CREATED_BY_NAME, F.SYS_CREATED_BY_TYPE, F.SYS_MODIFIED_BY_NAME,
  F.SYS_MODIFIED_BY_TYPE, F.SYS_CREATED_TIME, F.SYS_MODIFIED_TIME,
  F.CREATED_BY_NAME, F.CREATED_BY_TYPE, F.MODIFIED_BY_NAME, F.MODIFIED_BY_TYPE,
  F.CREATED_TIME, F.MODIFIED_TIME, F.LANGUAGE, F.CONTENT_CHARSET,
  F.CONTENT_LENGTH, F.CONTENT_MODIFIED_TIME, F.CUST_FLAGS, F.EXT_ATTRIBUTES,
  F.CONTENT_ID, F.CONTENT_EXT_ID, F.CONTENT_UUID, F.CONTENT_IL_DATA, A.ACL_ID,
  A.ACL_JSON, Case
    When F.ITEM_TYPE = 'folder' Then 1
    Else 0
  End As IS_FOLDER From FILE_TREE IV
  Inner Join DEMO_FILE_REVS As F On
      IV.FILE_ID = F.FILE_ID And IV.REVISION = F.REVISION
  Left Join DEMO_ACL_GROUPS As A On
      F.ACL_ID = A.ACL_ID Order By IV.PATHTEXT

sample data structure from DEMO_FILE_REVS view

DEMO-ACL_GROUPS