sql - How to optimize long running cte

Question

I have one query that is taking 10 minute and giving me 6852104 records. How can I optimize it? I have added execution plan as well.

My query is like this:

    WITH CTE (columns name 
    )
    AS (
    SELECT  columns
    FROM #tempTable b  WITH (NOLOCK)
    INNER JOIN physicaltable1 A ON A.ParentInvoiceItemId = b.InvoiceItemId
    INNER JOIN physicaltable2 c ON c.LinkedInvoiceItemId = a.LinkedInvoiceItemId
    AND c.HospitalId = B.Hospital_Id
    
    UNION ALL
    
    SELECT columns
    FROM CTE LIP
    JOIN physicaltable1         LIC ON LIP.ChildInvoiceItemId = LIC.ParentInvoiceItemId
    INNER JOIN physicaltable12  LIH ON LIH.LinkedInvoiceItemId = LIC.LinkedInvoiceItemId
    AND LIH.HospitalId = LIP.HospitalId
    WHERE LIP.[Level] < 3
    )
    SELECT *
    INTO #newtemptable
    FROM CTE;

question from:https://stackoverflow.com/questions/65933462/how-to-optimize-long-running-cte

Answer 1

Waitting for answers