[CFAMX-10295] Improve performance of InventoryItemMetricDetail query - HotSchedules JIRA

Details

Type: Feature Request
Status: Closed (View Workflow)
Priority: Critical
Resolution: Completed
Affects Version/s: None
Fix Version/s: None
Component/s: None
Labels:
- Performance-HighTraffic
- TechDebt

Description

exec sp_executesql 'SELECT this_.InventoryItemMetricDetailId as Inventor1_114_0_, this_.InventoryItemId as Inventor2_114_0_, this_.IntervalStart as Interval3_114_0_, this_.ManagerTransactionCount as ManagerT4_114_0_, this_.RawTransactionCount as RawTrans5_114_0_, this_.SystemTransactionCount as SystemTr6_114_0_, this_.LastYearTransactionCount as LastYear7_114_0_, this_.ServiceType as ServiceT8_114_0_, this_.ForecastId as ForecastId114_0_ FROM Forecasting.InventoryItemMetricDetail this_ WHERE ((this_.ForecastId = @p0 and this_.IntervalStart >= @p1) and this_.IntervalStart < @p2)',N'@p0 bigint,@p1 datetime,@p2 datetime',@p0=6130758,@p1='2020-05-30 00:00:00',@p2='2020-05-31 00:00:00'

Suggestions:

Create a new Non Clustered index on columns (ForecastID, IntervalStart) similar to the index on Forecasting.InventoryItemMetricDetail table, but include *Count and ServiceType columns. As seen by Missing Index hint from execution plan above, adding this index will improve performance by 98%