Improving Performance of CTE

Question

I need to have a report with calculation of some values from different tables.
I'm using CTE and subquery for calculations but the query is slow.
I tried a few attempts to improve the performance but no success.
Actual query has more colums but I copied some part of it to give idea of my query structure.
Thanks for help.

begin
with CTE_FirmList
(
    FirmId,
    FirmName,
    StartDate,
    EndDate,
    TgId,
    TgName,
    TgCity,
    ProjectName
)
as
(
    select 
        distinct f.Id FirmId,
        f.Name FirmName,
        f.StartDate,
        f.EndDate,
        t.Id TgId,
        t.Name TgName,
        c.Name TgCity,
        p.Name ProjectName
    from Firm f
        left join Tg t on t.Id = f.TgId
        left join City c on c.Id=t.CityId 
        left join Country co on co.Id=f.CountryId
        left join FirmProject p on p.FirmId=f.Id
)

select
    FirmId,
    FirmName,
    
    (select COUNT(0) 
    from FirmPersonnel p 
        inner join CTE_FirmList f on f.FirmId=p.FirmId
    where f.FirmId=qq.FirmId
    ) TotalPersonnelCount,
    
    (select COUNT(0) 
    from FirmPersonnel p 
        inner join CTE_FirmList f on f.FirmId=p.FirmId 
    where f.FirmId=qq.FirmId and p.PersonnelType in (1) 
    ) PersonnelCount1,

    (select COUNT(0) 
    from FirmPersonnel p 
        inner join CTE_FirmList f on f.FirmId=p.FirmId 
    where f.FirmId=qq.FirmId and p.PersonnelType in (2) 
    ) PersonnelCount2,
            
    (select count(p.Id) 
    from FirmProject p 
    where p.FirmId=qq.FirmId
    ) TotalProjectCount,
    
    (select count(p.Id) 
    from FirmProject p 
    where p.FirmId=qq.FirmId
        and p.ProjeStatus in (1)
    ) ProjectCount1,
    
    (select count(p.Id) 
    from FirmProject p 
    where p.FirmId=qq.FirmId
        and p.ProjeStatus in (2)
    ) ProjectCount2
from CTE_FirmList qq
where ProjectName like '%search condition%'
order by TgCity,TgName,FirmName
end

Here is the sample data and desired output:

Answer 1

You can definitely simplify the query. It is a bit hard to follow, but something like this should have better performance:

select f.FirmId, f.FirmName,
       p.TotalPersonnelCount, p.PersonnelCount1, p.PersonnelCount2
       count(distinct ProjectId) as TotalProjectCount,
       count(distinct case when ProjectStatus = 1 then ProjectId end) as ProjecctCount1,
       count(distinct case when ProjectStatus = 2 then ProjectId end) as ProjecctCount2
from (select f.Id as FirmId, f.Name as FirmName, f.StartDate, f.EndDate,
             t.Id TgId, t.Name as TgName, c.Name as TgCity,
             p.Name as ProjectName, p.ProjectId, p.status
      from Firm f left join
           Tg t
           on t.Id = f.TgId join
           City c
           on c.Id = t.CityId left join
           Country co
           on co.Id = f.CountryId left join
           FirmProject p
           on p.FirmId = f.Id
       where p.name like '%search condition%'
      ) fp join
      (select p.FirmId, count(*) as TotalPersonnelCount,
              sum(case when PersonnelType in (1) then 1 else 0 end) as PersonnelCount1, 
              sum(case when PersonnelType in (2) then 1 else 0 end) as PersonnelCount2
       from FirmPersonnel p 
       group by fp.FirmId 
      ) p
      on fp.FirmId = p.FirmId
group by FirmId, FirmName
order by FirmName