Query slow when use count(*) and inner join

My query is taking around 5 seconds to run. It returns almost 5000 records.

SELECT 
    c.CompanyID, 
    c.CompanyName, 
    psr.PartnerSRFName, 
    psr.PartnerSRLName, 
    be.MonthlyFee, 
    (
        SELECT Count(*) 
        FROM Units
        INNER JOIN Properties ON Units.PropertyID = Properties.PropertyID
        WHERE Properties.CompanyID = c.CompanyID
    ) TotalUnits
FROM Company c
LEFT JOIN Partner_SalesRep_CompanyMap psrcm ON c.CompanyID = psrcm.CompanyID
LEFT JOIN Partner_SalesReps psr ON psrcm.Partner_SalesRepsID = psr.AssignedID
LEFT JOIN Billing_Exemption be ON be.CompanyID = c.CompanyID
WHERE c.LeadSourceCompanyID = 1 AND Active = 1

If I remove the Select Count(*) ..., it runs very fast, but I need this data. Is there a way to improve this query?

Thanks

Answer

Indexes recommended by Gordon Linoff are a must-have.

Another thing you could to is move the computation from an inlined query to a joined subquery. This might allow the RDBMS to optimize the query, since it is now explicit that there is no need to repeat the computation for each record (only distinct values of CompanyID actually make a difference):

SELECT 
    c.CompanyID, 
    c.CompanyName, 
    psr.PartnerSRFName, 
    psr.PartnerSRLName, 
    be.MonthlyFee, 
    COALESCE(x.TotalUnits, 0) TotalUnits
FROM Company c
LEFT JOIN Partner_SalesRep_CompanyMap psrcm ON c.CompanyID = psrcm.CompanyID
LEFT JOIN Partner_SalesReps psr ON psrcm.Partner_SalesRepsID = psr.AssignedID
LEFT JOIN Billing_Exemption be ON be.CompanyID = c.CompanyID
LEFT JOIN (
    SELECT Properties.CompanyID, COUNT(*) TotalUnits
    FROM Units
    INNER JOIN Properties ON Units.PropertyID = Properties.PropertyID
    GROUP BY Properties.CompanyID
) x ON x.CompanyID = c.CompanyID
WHERE c.LeadSourceCompanyID = 1 AND Active = 1

Another option would be to turn merge the subquery with the outer query (by adding more joins to the outer query), turn on aggregation on the entire outer query, and use a unique column in table Units or Properties to do the count . I am not a big fan of this one, as, usually, the earlier you aggregate the better efficiency you get.