Best way to avoid repeated sub queries in SQL select statement

Question

I am using a SQL stored procedure to retrieve data from my database. since am using multiple subqueries the query speed is very slow, please suggest the best way to rewrite the below query to avoid the subquery.

    Select Companyid, 
           companyname, 
          (select count(distinct Identifier.SID) 
                  from CompaniesUsers   
                  join Identifier on CompaniesUsers.UniqueId = Identifier.UniqueId  
                 where Companies.CompanyId = CompaniesUsers.CompanyId 
                 and CompaniesUsers.IsActive = 1 
                 and Identifier.IsActive = 1 ) as CustomerCount,
         (select count(distinct CompaniesUsers.UniqueId) 
                 from CompaniesUsers    
                 where Companies.CompanyId = CompaniesUsers.CompanyId    
                 and CompaniesUsers.IsActive = 1 
                 and CompaniesUsers.UniqueId != '-'     )
         -      (Same query used in the CustomerCount) as AnonymousCustomerCount        
         from Companies where Companies.isactive = 1

Here I want to reuse the customer count query for the AnonymousCustomerCount. what is the best way to do this?

Answer 1

Try Outer Apply and see if it works for you:

Select Companyid,
companyname,
CustomerCount.Count,
TotalCount.Total - CustomerCount.Count as AnonymousCustomerCount
from CompanyTable
OUTER APPLY (
select Count
  joining of 2 tables
) AS CustomerCount
OUTER APPLY (
  Subquery Total to get total count by joining 2 tables
) AS TotalCount