I wonder if anyone has come across this behaviour before in the following query if I filter on "i.InspectionTypeID IN (x)" if x results in a large number (like about 2/3 of the entire result set of about 600 000) It takes a significantly longer amount of time to return even no results (15-30 seconds vs instantly otherwise). I can include the query and the execution plans if that helps. I can see a few differences between the queries but no idea how to refactor to improve it. We have indexes around the place that should (and have) improved performance in other places but I seem to be stuck on this one. Indexes don't appear to affect performance. At least what I tried.
Slow Query Plan: https://www.brentozar.com/pastetheplan/?id=SyBDgxSeO
EDIT: New Plan and Updated the Query as per advice from @charlieface
Here's the query:
declare @currentUserID int = 1018
declare @currentCompanyID int = 43
declare @status int = 0
declare @pageSize int = 2147483647
declare @pageNumber int = 1
DECLARE @notPreferredAssetIDsTable TABLE (id INT)
INSERT INTO @notPreferredAssetIDsTable
SELECT RecordID FROM SettingList sl
WHERE SettingID = (SELECT TOP 1 ID FROM Setting
WHERE SystemUserID = @currentUserID AND Name = 'Preferred Assets' AND Status = @status)
AND Value = '0'
AND Status = @status
SELECT i.ID As ID, a.AssetTypeID, at.Name as AssetTypeName, a.Code as AssetCode, a.Name as AssetName, CASE WHEN sections.SectionCount = 1 THEN cr.SectionName ELSE '' END as SectionName, c.Name as ContractName, reg.Name as RegionName, i.Carriageway,
startStatus.StatusDate as StartDate, startPoint.Name as StartPointName, i.ChainageFrom,
CASE WHEN i.Carriageway IS NULL THEN NULL ELSE i.ChainageFrom - (CASE WHEN i.Carriageway = 0 THEN startPoint.Forward ELSE startPoint.Reverse END) END StartDistancePast,
completedStatus.StatusDate As EndDate, endPoint.Name as EndPointName, i.ChainageTo,
CASE WHEN i.Carriageway IS NULL THEN NULL ELSE i.ChainageTo - (CASE WHEN i.Carriageway = 0 THEN endPoint.Forward ELSE endPoint.Reverse END) END EndDistancePast,
CASE WHEN i.Carriageway IS NULL THEN '' ELSE (CASE WHEN (ISNULL(OtherDirectionInspection.ID, 0) > 0) THEN 'Forward/Reverse'
WHEN i.Carriageway = 0 THEN 'Forward' ELSE 'Reverse' END) END as CarriagewayName,
dbo.GetInspectionTypeNamesForInspectionID(i.ID) AS InspectionTypeName, JobCount.Total As JobCount, i.CreatedDate,
CreatedUserName.DisplayName as CreatedUserName, AssignedUserName.DisplayName as AssignedUserName,
i.Comments, i.EntireRoad, i.PlannedDate, latestStatus.InspectionStatus AS LatestInspectionStatus, completedStatus.StatusDate AS CompletedDate, it.CompanyID as InspectionTypeCompanyID, i.ContractID, i.InspectionTypeID, sections.SectionCount,
it.Category as InspectionTypeCategory, latestStatus.ModifiedDeviceID as DeviceID, latestStatus.ModifiedUserID as LatestStatusModifiedUserID, i.JobID, i.CapitalWorkID, cw.Name as CapitalWorkName, AssignedUser.Name as AssignedUserEmail,
InspectionRouteName.Name as InspectionRouteName, i.InspectionGroupID,
il.Location AS InspectionLocation,
i.OtherDirectionInspectionID , CustomText1 , CustomBit1 , CustomDate1 , CustomNumber1 , CustomReferenceItemID1 , CustomText2 , CustomBit2 , CustomDate2 , CustomNumber2 , CustomReferenceItemID2 , CustomText3 , CustomBit3 , CustomDate3 , CustomNumber3 , CustomReferenceItemID3 , CustomText4 , CustomBit4 , CustomDate4 , CustomNumber4 , CustomReferenceItemID4 , CustomText5 , CustomBit5 , CustomDate5 , CustomNumber5 , CustomReferenceItemID5 , CustomText6 , CustomBit6 , CustomDate6 , CustomNumber6 , CustomReferenceItemID6 , CustomText7 , CustomBit7 , CustomDate7 , CustomNumber7 , CustomReferenceItemID7 , CustomText8 , CustomBit8 , CustomDate8 , CustomNumber8 , CustomReferenceItemID8 , CustomText9 , CustomBit9 , CustomDate9 , CustomNumber9 , CustomReferenceItemID9 , CustomText10 , CustomBit10 , CustomDate10 , CustomNumber10 , CustomReferenceItemID10 , CustomText11 , CustomBit11 , CustomDate11 , CustomNumber11 , CustomReferenceItemID11 , CustomText12 , CustomBit12 , CustomDate12 , CustomNumber12 , CustomReferenceItemID12 , CustomText13 , CustomBit13 , CustomDate13 , CustomNumber13 , CustomReferenceItemID13 , CustomText14 , CustomBit14 , CustomDate14 , CustomNumber14 , CustomReferenceItemID14 , CustomText15 , CustomBit15 , CustomDate15 , CustomNumber15 , CustomReferenceItemID15 , CustomText16 , CustomBit16 , CustomDate16 , CustomNumber16 , CustomReferenceItemID16 , CustomText17 , CustomBit17 , CustomDate17 , CustomNumber17 , CustomReferenceItemID17 , CustomText18 , CustomBit18 , CustomDate18 , CustomNumber18 , CustomReferenceItemID18 , CustomText19 , CustomBit19 , CustomDate19 , CustomNumber19 , CustomReferenceItemID19 , CustomText20 , CustomBit20 , CustomDate20 , CustomNumber20 , CustomReferenceItemID20 , CustomText21 , CustomBit21 , CustomDate21 , CustomNumber21 , CustomReferenceItemID21 , CustomText22 , CustomBit22 , CustomDate22 , CustomNumber22 , CustomReferenceItemID22 , CustomText23 , CustomBit23 , CustomDate23 , CustomNumber23 , CustomReferenceItemID23 , CustomText24 , CustomBit24 , CustomDate24 , CustomNumber24 , CustomReferenceItemID24 , CustomText25 , CustomBit25 , CustomDate25 , CustomNumber25 , CustomReferenceItemID25 , CustomText26 , CustomBit26 , CustomDate26 , CustomNumber26 , CustomReferenceItemID26 , CustomText27 , CustomBit27 , CustomDate27 , CustomNumber27 , CustomReferenceItemID27 , CustomText28 , CustomBit28 , CustomDate28 , CustomNumber28 , CustomReferenceItemID28 , CustomText29 , CustomBit29 , CustomDate29 , CustomNumber29 , CustomReferenceItemID29 , CustomText30 , CustomBit30 , CustomDate30 , CustomNumber30 , CustomReferenceItemID30 , CustomText31 , CustomBit31 , CustomDate31 , CustomNumber31 , CustomReferenceItemID31 , CustomText32 , CustomBit32 , CustomDate32 , CustomNumber32 , CustomReferenceItemID32 , CustomText33 , CustomBit33 , CustomDate33 , CustomNumber33 , CustomReferenceItemID33 , CustomText34 , CustomBit34 , CustomDate34 , CustomNumber34 , CustomReferenceItemID34 , CustomText35 , CustomBit35 , CustomDate35 , CustomNumber35 , CustomReferenceItemID35 , CustomText36 , CustomBit36 , CustomDate36 , CustomNumber36 , CustomReferenceItemID36 , CustomText37 , CustomBit37 , CustomDate37 , CustomNumber37 , CustomReferenceItemID37 , CustomText38 , CustomBit38 , CustomDate38 , CustomNumber38 , CustomReferenceItemID38 , CustomText39 , CustomBit39 , CustomDate39 , CustomNumber39 , CustomReferenceItemID39 , CustomText40 , CustomBit40 , CustomDate40 , CustomNumber40 , CustomReferenceItemID40 , CustomText41 , CustomBit41 , CustomDate41 , CustomNumber41 , CustomReferenceItemID41 , CustomText42 , CustomBit42 , CustomDate42 , CustomNumber42 , CustomReferenceItemID42 , CustomText43 , CustomBit43 , CustomDate43 , CustomNumber43 , CustomReferenceItemID43 , CustomText44 , CustomBit44 , CustomDate44 , CustomNumber44 , CustomReferenceItemID44 , CustomText45 , CustomBit45 , CustomDate45 , CustomNumber45 , CustomReferenceItemID45 , CustomText46 , CustomBit46 , CustomDate46 , CustomNumber46 , CustomReferenceItemID46 , CustomText47 , CustomBit47 , CustomDate47 , CustomNumber47 , CustomReferenceItemID47 , CustomText48 , CustomBit48 , CustomDate48 , CustomNumber48 , CustomReferenceItemID48 , CustomText49 , CustomBit49 , CustomDate49 , CustomNumber49 , CustomReferenceItemID49 , CustomText50 , CustomBit50 , CustomDate50 , CustomNumber50 , CustomReferenceItemID50
FROM Inspection i
INNER JOIN InspectionType it On i.InspectionTypeID = it.ID
INNER JOIN SystemUser CreatedUser On CreatedUser.ID = i.CreatedUserID
INNER JOIN Contract c On i.ContractID = c.ID
INNER JOIN Region reg On c.RegionID = reg.ID
INNER JOIN InspectionLocation il ON il.InspectionID = i.ID
LEFT OUTER JOIN Asset a On i.AssetID = a.ID
LEFT OUTER JOIN SystemUser AssignedUser On AssignedUser.ID = i.AssignedUserID
LEFT OUTER JOIN Road r On i.RoadID = r.ID
LEFT OUTER JOIN AssetType at On it.AssetTypeID = at.ID
LEFT OUTER JOIN Point startPoint On i.StartPointID = startPoint.ID
LEFT OUTER JOIN Point endPoint On i.EndPointID = endPoint.ID
LEFT OUTER JOIN CapitalWork cw On i.CapitalWorkID = cw.ID
CROSS APPLY dbo.GetNameForUser_Inline(i.CreatedUserID, i.ContractID, @currentCompanyID, 0) As CreatedUserName
OUTER APPLY dbo.GetNameForUser_Inline(i.AssignedUserID, i.ContractID, @currentCompanyID, 0) As AssignedUserName
OUTER APPLY
(
SELECT TOP 1 SectionName
FROM ContractRoad cr
WHERE cr.RoadID = r.ID
AND cr.ContractID = i.ContractID
AND ((i.Carriageway = 0 AND i.ChainageFrom < cr.ChainageToForward AND cr.ChainageFromForward < i.ChainageTo)
OR (i.Carriageway = 1 AND i.ChainageFrom < cr.ChainageFromReverse AND cr.ChainageToReverse < i.ChainageTo))
AND cr.Status = @status
) cr
OUTER APPLY
(
SELECT count(SectionName) as SectionCount
FROM ContractRoad cr
WHERE cr.RoadID = r.ID
AND cr.ContractID = i.ContractID
AND ((i.Carriageway = 0 AND i.ChainageFrom < cr.ChainageToForward AND cr.ChainageFromForward < i.ChainageTo)
OR (i.Carriageway = 1 AND i.ChainageFrom < cr.ChainageFromReverse AND cr.ChainageToReverse < i.ChainageTo))
AND cr.Status = @status
) sections
OUTER APPLY
(
SELECT TOP 1 StatusDate, LocalTime.LocalTime as StatusDateLocal
FROM InspectionStatus
CROSS APPLY tzdb.UtcToLocal_Inline(StatusDate, 'Australia/Sydney') as LocalTime
WHERE Status = @status
AND InspectionID = i.ID
AND InspectionStatus = 1
ORDER BY StatusDate
) startStatus
OUTER APPLY
(
SELECT TOP 1 StatusDate, LocalTime.LocalTime as StatusDateLocal
FROM InspectionStatus
CROSS APPLY tzdb.UtcToLocal_Inline(StatusDate, 'Australia/Sydney') as LocalTime
WHERE Status = @status
AND InspectionID = i.ID
AND InspectionStatus = 5
ORDER BY StatusDate
) completedStatus
OUTER APPLY
(
SELECT TOP 1 ID, StatusDate, InspectionStatus, ModifiedDeviceID, ModifiedUserID
FROM InspectionStatus
