'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 |
|---|
