索引尋求 OR 運算符的成本

我有一個很慢的查詢，執行需要 15 秒。該查詢從視圖中獲取數據，在 where 子句中，我的條件很少，包括 OR 和 AND。

exec sp_executesql N'SELECT 
   [GroupBy1].[A1] AS [C1]
   FROM ( SELECT 
       COUNT(1) AS [A1]
       FROM  (SELECT 
   [Searchview].[ReportId] AS [ReportId], 
   [Searchview].[ReportDateTime] AS [ReportDateTime], 
   [Searchview].[SearchDetailPageId] AS [SearchDetailPageId], 
   [Searchview].[FormId] AS [FormId], 
   [Searchview].[ReportSearchActivityNumber] AS [ReportSearchActivityNumber], 
   [Searchview].[IsMed] AS [IsMed], 
   [Searchview].[SearchActivityNumber] AS [SearchActivityNumber], 
   [Searchview].[AgentId] AS [AgentId], 
   [Searchview].[FileCode] AS [FileCode], 
   [Searchview].[AgentPhone] AS [AgentPhone], 
   [Searchview].[AgentCounty] AS [AgentCounty], 
   [Searchview].[AgentIsMed] AS [AgentIsMed], 
   [Searchview].[AgentDistrictId] AS [AgentDistrictId], 
   [Searchview].[County] AS [County], 
   [Searchview].[City] AS [City], 
   [Searchview].[Institution] AS [Institution], 
   [Searchview].[ReportDate] AS [ReportDate], 
   [Searchview].[ReportTime] AS [ReportTime], 
   [Searchview].[SubUnit] AS [SubUnit], 
   [Searchview].[SupplementNumber] AS [SupplementNumber], 
   [Searchview].[AccessNumber] AS [AccessNumber], 
   [Searchview].[SearchStatus] AS [SearchStatus], 
   [Searchview].[Reviewed] AS [Reviewed], 
   [Searchview].[SearchName] AS [SearchName], 
   [Searchview].[NatureOfSearch] AS [NatureOfSearch], 
   [Searchview].[InvestBy] AS [InvestBy], 
   [Searchview].[SecondaryAgent] AS [SecondaryAgent], 
   [Searchview].[Disposed] AS [Disposed], 
   [Searchview].[Property] AS [Property], 
   [Searchview].[PropertyDescriptions] AS [PropertyDescriptions], 
   [Searchview].[Forfeiture] AS [Forfeiture], 
   [Searchview].[ColdCaseNumber] AS [ColdCaseNumber], 
   [Searchview].[DateOccurred] AS [DateOccurred], 
   [Searchview].[TimeOccurred] AS [TimeOccurred], 
   [Searchview].[FirstSubmissionDate] AS [FirstSubmissionDate], 
   [Searchview].[IsReportOffline] AS [IsReportOffline], 
   [Searchview].[IsSupplement] AS [IsSupplement], 
   [Searchview].[PrimaryAgentId] AS [PrimaryAgentId], 
   [Searchview].[SecurityLevel] AS [SecurityLevel]
   FROM [dbo].[Searchview] AS [Searchview]) AS [Extent1]
       LEFT OUTER JOIN [dbo].[SerachDetailsPages] AS [Extent2] ON [Extent1].[SearchDetailPageId] = [Extent2].[Id]
       WHERE ((N''Public'' = [Extent1].[SecurityLevel]) OR (N''Private'' = [Extent1].[SecurityLevel]) OR ([Extent1].[PrimaryAgentId] = @p__linq__0) 
       OR ( EXISTS (SELECT 
           1 AS [C1]
           FROM  [dbo].[SerachDetailsPages] AS [Extent3]
           INNER JOIN (SELECT 
   [Permissions].[Id] AS [Id], 
   [Permissions].[AgentId] AS [AgentId], 
   [Permissions].[UserId] AS [UserId]
   FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4] ON [Extent3].[FormId] = [Extent4].[Id]
           WHERE ([Extent1].[SearchDetailPageId] = [Extent3].[Id]) AND ([Extent4].[UserId] = @p__linq__1) AND ([Extent4].[AgentId] = @p__linq__2)
       ))
       ) 
       AND ([Extent1].[ReportDateTime] >= @p__linq__3) AND ([Extent1].[ReportDateTime] <= @p__linq__4) AND ([Extent1].[AgentId] = @p__linq__5) AND ([Extent1].[IsReportOffline] <> 1) 
       AND ([Extent1].[IsSupplement] <> 1)
   )  AS [GroupBy1] ',N'@p__linq__0 int,@p__linq__1 int,@p__linq__2 varchar(8000),@p__linq__3 datetime2(7),@p__linq__4 datetime2(7),@p__linq__5 varchar(8000)
   ',@p__linq__0=9049,@p__linq__1=9049,@p__linq__2='330068',@p__linq__3='2010-06-25 00:00:00',@p__linq__4='2011-06-25 23:59:00',@p__linq__5='330068'

當我檢查執行計劃時，我可以看到一些索引搜尋（集群）非常高（98%），並且 SentryOne 計劃瀏覽器顯示這些索引搜尋是頂級操作是那些運營商成本很高。

根據我的分析，這些索引搜尋來自permissions視圖，每當我從查詢中評論以下部分時，

OR ( EXISTS (SELECT 
           1 AS [C1]
           FROM  [dbo].[SerachDetailsPages] AS [Extent3]
           INNER JOIN (SELECT 
   [Permissions].[Id] AS [Id], 
   [Permissions].[AgentId] AS [AgentId], 
   [Permissions].[UserId] AS [UserId]
   FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4] ON [Extent3].[FormId] = [Extent4].[Id]
           WHERE ([Extent1].[SearchDetailPageId] = [Extent3].[Id]) AND ([Extent4].[UserId] = @p__linq__1) AND ([Extent4].[AgentId] = @p__linq__2)
       )) 

它跑得很快。

我擁有的選項是更改查詢。但由於查詢是從 .net 應用程序（linq-sql）生成的，我無法控制它。任何人都可以提出任何可行的解決方案。Permissions 視圖是內部連接中幾個表的聯合。當我單獨執行查詢的註釋部分時，它很快並且索引搜尋成本更低。

我為索引搜尋（聚集）高（98％）的主鍵創建了一個非聚集索引，但該計劃仍然使用相同的 pk 聚集索引。

當我試圖弄清楚這個問題時，我遇到了一篇有趣的文章。當我註釋掉查詢中提到的部分然後它執行得很快，我可以看到查詢在執行計劃中使用並行處理。所以我想我註釋掉的那部分查詢是強制序列化導致執行時間長。查詢的那部分是否會導致序列化？任何的想法？

目前需要 15 秒或進行索引搜尋，因為您現在的記錄較少。

$$ Extent1 $$條件應該寫在這裡

FROM [dbo].[Searchview] AS [Searchview] where [Searchview].[SecurityLevel] in('Private','Public') and so on)

我認為這將改善基數估計。

如果視圖返回 int id 代替 ‘Private’,‘Public’ 則更好。

部分OR ( EXISTS (SELECT 你不再[dbo].[SerachDetailsPages]需要

OR  EXISTS (SELECT 1
       FROM [dbo].[Permissions] AS [Permissions]) AS [Extent4]
where  [Extent4] condition inside exists)

最後，如果您只需要計數，那麼為什麼要提及這麼多列。

經過長時間的分析，我終於能夠將性能提高 80%。

就像我在文章中提到的那樣，當我評論 sql 查詢塊時，查詢執行得更快並且在並行模式下，這導致了速度變慢。查詢中使用的視圖非常複雜，內部連接和左連接很少，因此在執行計劃中，索引查找（對於具有兩列的簡單表）和其他運算符很昂貴。

後來當我把注意力轉向“為什麼查詢沒有在並行模式下並行執行”時，我得到了一些答案。我讀了這篇文章 強制並行執行計劃，這對我有幫助。

我不必強制執行並行執行計劃，而是必須從查詢中刪除“並行抑制組件”。開發人員Scalar-valued function在查詢中使用Cast了一個，datetime這就是停止並行執行計劃的事情。我刪除了該函式並CAST直接在視圖定義中使用，查詢開始快速執行。

我仍然必須在視圖中包含處理空字元串的邏輯（在 中處理scalar-value function），但至少我得到了一些答案。

我建議所有正在為查詢進行性能改進的人也嘗試以下步驟；如果您的 SQL Server 版本支持並行執行，並且您在伺服器級別或查詢級別啟用了並行執行；

1. 檢查查詢是否以並行模式執行。
2. 如果不檢查強制並行執行的組件。您可以在前面提到的文章中找到。

引用自：https://dba.stackexchange.com/questions/210556