Code rewrite

Transact-SQL
1

I have the following code, that is taking over a minute to run - can anyone advise a speedier way of getting the information please.

Select Convert(date,Oppo_Opened) as Date, c.comp_sector, count(distinct o.Oppo_OpportunityId) as Enquiries , Max(data.[3Hour]) as [3Hour], Max(data1.[24Hour]) as [24Hour]

From Opportunity o Inner Join Company c On c.Comp_CompanyId = o.Oppo_PrimaryCompanyId
Left Outer Join
(Select distinct Convert(date,o.Oppo_Opened) as Opened, comp_sector,
Count(distinct op.Oppo_OpportunityId) as [3Hour]
From OpportunityProgress op Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
Inner Join Company On Company.Comp_CompanyId = o.Oppo_PrimaryCompanyId
Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7 and Convert(date,op.Oppo_CreatedDate) > DateAdd(Day, -60, Getdate()) and op.Oppo_Stage In ('IQ','HQ','3Q')
and Comp_sector In ('b','p')
Group By Convert(date,o.Oppo_Opened), Comp_Sector
) data On data.Opened=Convert(date,Oppo_Opened) and data.Comp_Sector = c.comp_sector

  Left Outer Join
 (Select distinct Convert(date,o.Oppo_Opened) as Opened, comp_sector, 
       Count(distinct op.Oppo_OpportunityId) as [24Hour]
  From OpportunityProgress op Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
       Inner Join Company On Company.Comp_CompanyId = o.Oppo_PrimaryCompanyId
  Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7 and Convert(date,op.Oppo_CreatedDate) > DateAdd(Day, -60, Getdate()) and op.Oppo_Stage In ('SDQ','24Q')
        and Comp_sector In ('b','p')
  Group By Convert(date,o.Oppo_Opened), Comp_Sector 
   ) data1 On data1.Opened=Convert(date,Oppo_Opened) and data1.Comp_Sector = c.comp_sector

Where Oppo_Deleted Is Null and Convert(date,Oppo_Opened) > DateAdd(Day, -60, Getdate()) and c.Comp_sector In ('b','p')
Group By Convert(date,Oppo_Opened), c.Comp_Sector

2

To start off:

  1. are the join columns indexed in the tables in the query?
  2. Are the columns in the WHERE clauses indexed?
  3. Whenever you have something like this:

Convert(DATE, op.Oppo_CreatedDate) > DateAdd(Day, - 60, Getdate())

SQL will not use any index on the Oppo_CreatedDate column, because of the function calls.

4.. What kind of join does the execution show? Merge? Hash? Loop?

  1. Have statistics been updated recently on the tables involved

  2. How many rows per source table (total) and how many in the result set?