'Which columns should I index based on bottom SQL query?

I am having a SQL timeout issue (during daily data refresh for PowerBI report), and trying to add Index(s) on a table.

Since there is no index nor Primary Key, I am thinking about doing Clustered Index first and possibly Non-Clusterd Index after (if needed).

Which column(s) should I do the Indexing based on bottom SQL query?

Should I do on bottom columns?

group by Employee_Number, DateLoad

order by punch_start

select org_id,
   org, 
   punch_system_id, 
   employee, 
   position, 
   punch_start, 
   punch_end, 
   punch_time_list,
   total_hours,
   
   ( case when cast (pc1.Rate_1_Pay_Rate as decimal(10, 4)) is null and pc1.Hourly_Rate is null then 
   (case when cast (pc2.Rate_1_Pay_Rate as decimal(10, 4)) is null then 0 
    when cast (pc2.Rate_1_Pay_Rate as decimal(10, 4)) > 200 then pc2.Hourly_Rate 
      else cast (pc2.Rate_1_Pay_Rate as decimal(10, 4)) 
      end
     )
     when cast (pc1.Rate_1_Pay_Rate as decimal(10, 4)) > 200 then pc1.Hourly_Rate 
     else cast (pc1.Rate_1_Pay_Rate as decimal(10, 4)) 
     end
     ) as Hourly_Rate,
   
   (case when isHoliday = 1 then 0 else OT_hours end) as OT_hours, 
   (case when isHoliday = 1 then 0 else DT_hours end) as DT_hours, 
   (case when isHoliday = 1 then total_hours else 0 end) as Hol_OT,
   WeekNum, 
   YearOnly,
   isHoliday,
   (case when pc1.DateLoad is not null then pc1.FLSA_Status else pc2.FLSA_Status end) as FLSA_Status

from (dbo.vw_punch_variance_new_2 as pv 
 
     left outer join 
 
     (select Employee_Number, DateLoad, max(Rate_1_Pay_Rate) as Rate_1_Pay_Rate, 
max(Hourly_Rate) as Hourly_Rate, max(FLSA_Status) as FLSA_Status 
      from dbo.tblPayCorDaily_Historical_Data
      group by Employee_Number, DateLoad) 
     as pc1 
 
     on pv.punch_system_id = pc1.Employee_Number and cast(pv.punch_start as date) = 
cast(pc1.DateLoad as date))
 
     left outer join 
 
     (select Employee_Number, DateLoad, max(Rate_1_Pay_Rate) as Rate_1_Pay_Rate, 
max(Hourly_Rate) as Hourly_Rate, max(FLSA_Status) as FLSA_Status  
      from dbo.tblPayCorDaily_Historical_Data as hist

      where DateLoad = 
      (select max(DateLoad) from dbo.tblPayCorDaily_Historical_Data as hist2 where 
   hist.Employee_Number = hist2.Employee_Number)
      group by Employee_Number, DateLoad) 
      as pc2
  
      on pv.punch_system_id = pc2.Employee_Number

where punch_start < GETDATE()
order by punch_start offset 0 rows


Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source