sql-server - 仅对 Where IN 子句中的特定值进行慢速查询
问题描述
我想知道是否有人在以下查询中遇到过这种行为，如果我在“i.InspectionTypeID IN (x)”上进行过滤，如果 x 导致大量（如大约 600 000 的整个结果集的大约 2/3）即使没有结果也需要更长的时间才能返回（15-30 秒 vs 立即）。如果有帮助，我可以包含查询和执行计划。我可以看到查询之间的一些差异，但不知道如何重构以改进它。我们在其他地方应该（并且已经）提高性能的地方有索引，但我似乎被困在这个地方。索引似乎不会影响性能。至少我试过了。
慢查询计划：https ://www.brentozar.com/pastetheplan/?id=SyBDgxSeO
编辑：新计划并根据@charlieface 的建议更新了查询
这是查询：
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
WHERE Status = @status AND InspectionStatus.InspectionStatus <> 7
AND InspectionID = i.ID
ORDER BY CASE WHEN InspectionStatus.InspectionStatus = 6 THEN 1 ELSE 0 END, StatusDate DESC
) latestStatus
OUTER APPLY
(
SELECT COUNT(ID) As Total FROM Job
WHERE InspectionID = i.ID
AND ParentJobID IS NULL
AND Status = @status
) JobCount
OUTER APPLY
(
SELECT i2.ID
FROM dbo.Inspection i2
WHERE i2.OtherDirectionInspectionID = i.ID
AND Status = @status
) OtherDirectionInspection
OUTER APPLY
(
SELECT TOP 1 ir.Name FROM InspectionRoute ir
INNER JOIN InspectionGroup ig ON ig.InspectionRouteID = ir.ID
WHERE ir.Status = 0
AND ir.CompanyID = 43
AND ig.Status = 0
AND ig.ID = i.InspectionGroupID
) InspectionRouteName
WHERE i.Status = 0 AND i.ParentInspectionID IS NULL
AND il.Status = 0
--AND ((i.AssetID IS NOT NULL AND i.AssetID NOT IN (SELECT ID FROM @notPreferredAssetIDsTable)) OR i.AssetID IS NULL)
AND it.Category <> 2
AND c.IsArchived = 0
AND it.AssetTypeID = 3 AND a.AssetTypeID IS NOT NULL AND a.AssetTypeID = 3
AND i.InspectionTypeID IN (3)
AND i.ContractID IN (90,118)
AND (latestStatus.InspectionStatus IN (5,6,1,2,3) OR completedStatus.StatusDate IS NOT NULL OR latestStatus.InspectionStatus IS NULL)
AND ((completedStatus.StatusDateLocal >= '2021-02-01 00:00:00' AND completedStatus.StatusDateLocal <= '2021-02-05 23:59:59') OR (i.PlannedDate >= '2021-02-01 00:00:00' AND i.PlannedDate <= '2021-02-05 23:59:59') OR (i.CreatedDate >= '2021-02-01 00:00:00' AND i.CreatedDate <= '2021-02-05 23:59:59' AND (latestStatus.InspectionStatus not in (6, 5) OR latestStatus.InspectionStatus IS NULL)))
AND ((CASE WHEN i.AssignedUserID IS NULL THEN CreatedUser.CompanyID ELSE AssignedUser.CompanyID END = 43) OR (CASE WHEN i.AssignedUserID IS NULL THEN CreatedUser.CompanyID ELSE AssignedUser.CompanyID END = 1
AND i.ContractID IN (90,118)) OR (CASE WHEN i.AssignedUserID IS NULL THEN CreatedUser.CompanyID ELSE AssignedUser.CompanyID END = 79
AND i.ContractID IN (90)) OR (CASE WHEN i.AssignedUserID IS NULL THEN CreatedUser.CompanyID ELSE AssignedUser.CompanyID END = 80
AND i.ContractID IN (90)) OR (CASE WHEN i.AssignedUserID IS NULL THEN CreatedUser.CompanyID ELSE AssignedUser.CompanyID END = 81
AND i.ContractID IN (90)) OR (CASE WHEN i.AssignedUserID IS NULL THEN CreatedUser.CompanyID ELSE AssignedUser.CompanyID END = 82
AND i.ContractID IN (90)))
ORDER BY ID
OFFSET @pageSize * (@pageNumber - 1) ROWS
FETCH NEXT @pageSize ROWS ONLY
OPTION (RECOMPILE)
解决方案
推荐阅读
- python - 使用 Masking 为 tf.data.Dataset 找到正确的形状馈入 LSTM
- android - Android桌面环境名称？
- flutter - 构建失败可能是由于插件中的 AndroidX 不兼容。该工具即将尝试使用Jetifier解决不兼容问题
- java - 如何在使用 jaxb 进行编组时删除额外的转义字符
- c# - C#解密AES CBC pkcs1填充，有System.Security.Cryptography.PaddingMode.PKCS7但没有PKCS1
- pyautogui - pyautogui.click 挂起/停止程序
- javascript - 如何访问这个 div
- android - Android Kotlin - 无法理解如何在 sdk 库（包含活动）中传递值
- ios - 查找带有核心 NFC 的 miFare NFC 的 ATQA 和 SAK
- vue.js - 带有 vue 的离子存储