时间戳

首页 > 解决方案 > 减少连接查询的逻辑读取

sql - 减少连接查询的逻辑读取

问题描述

希望有人可以帮助我。我们的客户有一个现有的数据库，这个特定的视图是有问题的并且运行速度非常慢。这是作为顾问向我们提出的，我到处都看到了带有聚集索引扫描的执行计划。我设法通过添加非聚集索引使其成为索引搜索（除了少于 100 行的小表）

然而，我的问题是逻辑读取远非理想。这是查询计划的链接：https://www.brentozar.com/pastetheplan/?id=H1yKuqdnv

这是 IO 统计信息

 SQL Server parse and compile time: 
   CPU time = 3906 ms, elapsed time = 4171 ms.

(5937 rows affected)
Table 'InsuranceVerificationType'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientInsuranceCIV'. Scan count 1, logical reads 4, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VerificationStatusReason'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VerificationStatus'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'VerificationStatusReasonStatus'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Language'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OfficeAuth'. Scan count 5, logical reads 19, physical reads 1, read-ahead reads 13, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Attorney'. Scan count 5, logical reads 72, physical reads 0, read-ahead reads 80, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientAttorney'. Scan count 9, logical reads 870, physical reads 1, read-ahead reads 825, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'InsuranceType'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Carrier'. Scan count 5, logical reads 691, physical reads 1, read-ahead reads 230, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Team'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WorkQueueItemStatus'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PriorityType'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientInsuranceRank'. Scan count 5, logical reads 11583, physical reads 1244, read-ahead reads 11484, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Office'. Scan count 5, logical reads 25, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WorkQueueType'. Scan count 0, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientStatus'. Scan count 3, logical reads 6, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'TaxID'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 11253, logical reads 43778, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'OfficeTaxID'. Scan count 5, logical reads 5102, physical reads 3, read-ahead reads 31, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'WorkQueueItem'. Scan count 5, logical reads 41408, physical reads 2, read-ahead reads 40993, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Patient'. Scan count 5, logical reads 23470, physical reads 0, read-ahead reads 22433, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Account'. Scan count 5, logical reads 13204, physical reads 1, read-ahead reads 12972, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Customer'. Scan count 0, logical reads 37098, physical reads 40, read-ahead reads 26760, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'CustomerAccount'. Scan count 7388, logical reads 75515, physical reads 47, read-ahead reads 20420, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientInsurance'. Scan count 5, logical reads 17379, physical reads 1, read-ahead reads 17158, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ChargeHeaderFollowup'. Scan count 468, logical reads 3278, physical reads 1, read-ahead reads 3432, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'PatientInsuranceVerificationHistory'. Scan count 5280, logical reads 42268, physical reads 3495, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

 SQL Server Execution Times:
   CPU time = 8185 ms,  elapsed time = 80366 ms.

Completion time: 2020-12-17T16:15:59.3325424+08:00

使用 Patient 和 WorkQueueItem，我添加了这些索引

CREATE NONCLUSTERED INDEX [IX_WorkQueueItem_WorkQueueTypeID_ClosedDateUTC] 
ON [dbo].[WorkQueueItem] ( [WorkQueueTypeId],[ClosedDateUtc] ) INCLUDE ( [WorkQueueItemId],[PriorityTypeId],[AssignedTo],[WorkQueueItemStatusId],[ItemKey], [CreatedDateUtc],[CreatedBy],[UpdatedDateUtc],[UpdatedBy],[TeamId],[PatientID], [PatientInsuranceID],[ChargeHeaderID] )

CREATE NONCLUSTERED INDEX [IX_Patient_Active_Deleted_PatientStatusID]
ON [dbo].[Patient]  ( [Active],[Deleted], [PatientStatusID],[OfficeID])
INCLUDE ( [PatientID],[ThirdPartyPatientID],[InsuranceTypeID],[CarAccident],[AltIDStatusID])

这是完整的查询。

    SELECT O.DeptNum as DeptNumber  
   ,O.OfficeID  
   , O.AbbreviatedName AS LocationAbbreviatedName  
   , O.IsMSA  
   , A.PatientID  
   , P.ThirdPartyPatientID   
   , P.InsuranceTypeID AS PatientAccountType  
   , PIN.PatientInsuranceID   
   , A.ThirdPartyAccountID  
   , CU.Name AS FirstName  
   , CU.Name2 AS LastName  
   , CU.Name + ' ' + CU.Name2 AS PatientName  
   , IT.InsTypeDesc AS AccountType  
   , NULL AS RequestType  
   , PIR.Rank  
   , IsNull(C.Carrier, ATT.Firm) as Carrier  
   , P.CarAccident  
   , PIN.PolicyNumber  
   , Case  
     When P.InitialEvalDate Is Null Then  
    '01/01/1900'  
     Else  
    DATEADD(dd, DATEDIFF(dd, 0, P.InitialEvalDate), 0)  
     End InitialEvalDate  
   , Case  
     When P.InitialEvalDate Is Null Then  
    '01/01/1900 12:00 AM'  
     Else  
    P.InitialEvalDate  
     End  AS InitialEvalDateTime  
   , PS.PatientStatusID  
   , PS.Status AS PatientStatus  
   , VS.VerificationStatusDescription AS CIVStatus  
   , VSR.VerificationStatusReasonDescription AS Reason  
   , L.LanguageDescription  
   , P.AltIDStatusID AS ReferralStatus  
   , T.TaxID  
   , T.TaxIDNickname  
   , wqi.WorkQueueItemId  
   , wqi.WorkQueueTypeId  
   , wqt.Name AS WorkQueueTypeName  
   , wqi.PriorityTypeId  
   , pt.Name AS PriorityTypeName  
   , wqi.WorkQueueItemStatusId  
   , wqis.WorkQueueItemStatusName  
   , wqi.AssignedTo  
   , wqi.ItemKey  
   , wqi.CreatedDateUtc  
   , wqi.CreatedBy  
   , wqi.UpdatedDateUtc  
   , wqi.UpdatedBy  
   , wqi.ClosedDateUtc  
   ,Case  
    When wqi.ClosedDateUtc Is NULL Then  
     NULL  
    Else  
     DATEADD(dd, DATEDIFF(dd, 0, wqi.ClosedDateUtc), 0)  
   End ClosedDate  
   , wqi.TeamId  
   , tm.Name AS TeamName  
   , oa.TouchstoneLiveDate  
   ,ivt.InsuranceVerificationTypeID  
   ,ivt.[Description] AS InsuranceVerificationType  
   ,chf.CalcDueDate AS DueDate  
   ,IsNull(C.GenericCarrierCode, 0) as GenericCarrierCode  
   ,C.ThirdPartyCarrierID  
   ,wqi.ChargeHeaderID  
 FROM dbo.WorkQueueItem (NOLOCK) AS wqi 
 LEFT OUTER JOIN dbo.PatientInsurance (NOLOCK)  AS PIN 
 ON PIN.PatientInsuranceID = wqi.PatientInsuranceID   
  INNER JOIN dbo.Patient (NOLOCK) AS P  ON P.PatientID = wqi.PatientId AND P.Active = 1 AND P.Deleted = 0  
  INNER JOIN dbo.Account (NOLOCK)  as A
  ON A.PatientID = P.PatientID             
  INNER JOIN dbo.CustomerAccount (NOLOCK) AS CA ON CA.AccountID = A.AccountID AND CA.Deleted = 0   
  INNER JOIN dbo.Customer (NOLOCK) AS CU ON CU.CustomerID = CA.CustomerID AND CU.Deleted = 0   
 LEFT OUTER JOIN dbo.PatientInsuranceRank (NOLOCK) AS PIR ON PIR.PatientInsuranceID = PIN.PatientInsuranceID AND PIR.Deleted = 0          
  INNER JOIN dbo.WorkQueueType (NOLOCK) AS wqt ON wqt.WorkQueueTypeId = wqi.WorkQueueTypeId
 LEFT OUTER JOIN dbo.PriorityType (NOLOCK) AS pt ON pt.PriorityTypeId = wqi.PriorityTypeId
 LEFT OUTER JOIN dbo.WorkQueueItemStatus (NOLOCK) AS wqis ON wqis.WorkQueueItemStatusId = wqi.WorkQueueItemStatusId   
 LEFT OUTER JOIN dbo.Team (NOLOCK) AS tm ON tm.TeamId = wqi.TeamId      
 LEFT OUTER JOIN dbo.Carrier (NOLOCK) AS C ON C.CarrierID = PIN.CarrierID AND C.Deleted = 0   
  INNER JOIN dbo.Office (NOLOCK) AS O ON O.OfficeID = P.OfficeID AND O.Deleted = 0   
 LEFT OUTER JOIN dbo.InsuranceType (NOLOCK) AS IT ON IT.InsuranceTypeID = IsNull(C.InsuranceTypeID, P.InsuranceTypeID) AND IT.Deleted = 0   
 LEFT OUTER JOIN dbo.PatientAttorney (NOLOCK)  as PATT on Patt.PatientID = P.PatientID AND PATT.AttorneyID <> 0 AND PATT.Deleted = 0  
 LEFT OUTER JOIN dbo.Attorney (NOLOCK) as ATT on ATT.AttorneyID = PATT.AttorneyID   
  INNER JOIN dbo.PatientStatus (NOLOCK) AS PS ON PS.PatientStatusID = P.PatientStatusID AND PS.PatientStatusID IN (1,2,4) AND PS.Deleted = 0      
 LEFT OUTER JOIN dbo.OfficeTaxID (NOLOCK)  AS OT  ON OT.OfficeID = O.OfficeID AND ((GETDATE() >= OT.EffectiveStartDate) AND ((GETDATE() <= OT.EffectiveEndDate) OR (OT.EffectiveEndDate IS NULL)))  
 LEFT OUTER JOIN OfficeAuth (NOLOCK) oa on p.OfficeID = oa.OfficeID  
  INNER JOIN dbo.TaxID (NOLOCK) AS T ON T.TaxIDID = OT.TaxIDID AND T.Deleted = 0   
 LEFT OUTER JOIN dbo.Language (NOLOCK) AS L ON L.LanguageID = CU.LanguageID AND L.Deleted = 0         
 LEFT OUTER JOIN PatientInsuranceVerificationHistory (NOLOCK) as phv  
   ON  phv.PatientInsuranceVerificationHistoryID =   
    (  
        SELECT  TOP 1 PatientInsuranceVerificationHistoryID
        FROM    PatientInsuranceVerificationHistory (NOLOCK)
        WHERE   PatientInsuranceVerificationHistory.PatientInsuranceID = PIN.PatientInsuranceID and deleted = 0
        ORDER BY
      PatientInsuranceVerificationHistory.CreatedDate DESC
    )         
 LEFT OUTER JOIN dbo.VerificationStatusReasonStatus (NOLOCK) AS VSRS on phv.VerificationStatusReasonStatusID = VSRS.VerificationStatusReasonStatusID         
 LEFT OUTER JOIN dbo.VerificationStatus (NOLOCK) as VS on VSRS.VerificationStatusID = VS.VerificationStatusID        
 LEFT OUTER JOIN dbo.VerificationStatusReason (NOLOCK) as VSR on VSRS.VerificationStatusReasonID = VSR.VerificationStatusReasonID  
 LEFT OUTER JOIN dbo.PatientInsuranceCIV (NOLOCK) piciv ON piciv.PatientID = A.PatientID AND piciv.PatientInsuranceID = PIN.PatientInsuranceID  
 LEFT OUTER JOIN dbo.InsuranceVerificationType (NOLOCK) ivt ON ivt.InsuranceVerificationTypeID = piciv.VerificationTypeID  
 LEFT OUTER JOIN dbo.ChargeHeaderFollowup (NOLOCK) chf ON chf.ChargeHeaderID = wqi.ChargeHeaderID AND chf.Deleted = 0  
  
 WHERE wqi.WorkQueueTypeId = 2   
   AND ((PATT.EffectiveEndDate IS NULL) OR (GETDATE() >= PATT.EffectiveStartDate) AND (GETDATE() <= PATT.EffectiveEndDate))   
   AND ((wqi.ClosedDateUtc IS NULL) or (DATEDIFF(Day, wqi.ClosedDateUtc, GETUTCDATE()) < 30)
   )  
   AND ISNULL(PIN.Deleted, 0) = 0

标签： sqlsql-serverquery-optimizationsql-tuning

解决方案

鉴于这是我无法访问的数据库上的巨大查询，因此很难知道从哪里开始。但是从查询和查询计划中弹出给我的显而易见的事情如下：

  1. NOLOCK仅当您对返回的无效数据感到满意时才应使用。不仅是未提交的行，还有重复或计数不足的行。例如，如果您的状态或类型查找之一在您之下发生更改，您最终可能会加倍或删除具有这些 id 的每一行。如果您遇到锁定问题，更好的选择是使用SNAPSHOT隔离级别。
  2. 您真的需要返回所有这些列吗？减少每一次不必要的查找。您可以在客户端缓存状态和类型查找吗？
  3. 我没有您的架构，但如果任何左连接可以更改为内部连接，它可能会有所帮助，因为优化器可以更好地推理它们。此外，一些左连接依赖于先前的左连接，在这种情况下，嵌套连接可能更正确，并获得更好的结果。
  4. 在您查询的每个表上添加过滤索引Deleted = 0，并将其作为过滤器。第一个索引列应该是适用表上的 StartDate。
  5. join onPatientInsuranceVerificationHistory应该重写为OUTER APPLY.
    根据基数，最好PatientInsuranceVerificationHistory使用ROW_NUMBER()过滤器加入整体。
    OUTER APPLY
    (  
        SELECT  TOP 1 PatientInsuranceVerificationHistoryID
        FROM    PatientInsuranceVerificationHistory phv
        WHERE   phv.PatientInsuranceID = PIN.PatientInsuranceID and phv.deleted = 0
        ORDER BY phv.CreatedDate DESC
    ) phv
  1. PIN.Deleted需要可以为空吗？如果没有，您可以按上述方式对其进行索引。
  2. 包含包含的索引OfficeTaxID (OfficeID, StartDate)会很有用。
  3. 针对谓词的索引和基数估计OR总是很困难的。一种可行的选择是手动编写索引联合，例如JOIN (SELECT * FROM a WHERE condition1 UNION ALL SELECT * FROM a WHERE condition2) a....
  4. 对于 EndDate（非常典型的 Type 2 SCD），提高效率的常见解决方案是：添加一个 IsCurrent 字段作为索引，或者将 EndDate 更改为使用 max-date (9999-12-31) 而不是 NULL（你可以这样做这作为计算列）。
  5. 倒数第二行可以重写如下：
    AND (wqi.ClosedDateUtc IS NULL OR wqi.ClosedDateUtc < DATEADD(Day, 30, GETUTCDATE())
    这可能有助于基数估计和命中索引。
    DATEDIFF优化器很难推理，因为您要求它针对不等式运行代数：GETUTCDATE - ClosedDateUtc < 30。随着DATEADD您移动它说：ClosedDateUtc < GETUTCDATE + 30，优化器可以使用索引来对抗。

推荐阅读