Reducing Logical Reads on Join Query

Question

Hope someone could help me. Our client has an existing database and this specific view is problematic and runs really slow. This has been raised to us as consultants and I saw the execution plan with clustered index scan everywhere. I managed to make it Index Seek (except for small tables with like less than 100 rows) by adding Non-Clustered Index

How ever, my problem is that the logical reads are way too far from desirable. Here is the link to the query plan : https://www.brentozar.com/pastetheplan/?id=H1yKuqdnv

Here's the IO statistics

 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

With the Patient and WorkQueueItem, I added these indexes

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])

Here is the complete query.

    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

Upload the actual execution plan. The estimated plan estimates 600K rows. — Dan Guzman, Dec 17 '20 at 10:50
The most obvious problem is that it’s returning a few thousand rows because there is no real filter in the query. What’s the application doing with so many rows? No user is going to read that much. The lack of filters means scans and hash joins are usually the way to go, if you want to read less then you’ll want to include some meaningful filter. If this isn’t missing a filter then you probably want to change those loops into hash joins somehow (They are probably caused by the join conditions). The use of `nolock` means that the data could be returned in a very corrupt state, is that intended? — Andrew Sayer, Dec 17 '20 at 10:56
A couple of notes. First, views are often designed to be used this way: `SELECT something, something FROM view WHERE some conditions`. It you try to optimize the view without the WHERE, you won't optimize for the right query, because WHERE conditions choose indexes. Second, actual execution plans in SSMS make suggestions about indexes. In my experience those suggestions are very often very useful. — O. Jones, Dec 17 '20 at 11:52
There's no filtering condition on the query. This is probably not for an online transaction, but it's probably a reporting query or a batch query. You shouldn't expect high performance on any of those, specially if you are reading 1 million rows. — The Impaler, Dec 17 '20 at 11:56
It looks like the plan was just missing the complete SQL, please share it. Additionally, make sure those index hints are actually helping things - they've been actioned in rather undesirable ways (ie read every row of the table in index order 1 by 1 rather than scan the table nice and quickly) — Andrew Sayer, Dec 17 '20 at 13:02
Hi @AndrewSayer the entire SQL is on the link in brentozar.com, hints are unfortunately not allowed in a view — Json T, Dec 17 '20 at 14:48
Hi @TheImpaler thanks for the insight. Filters are applied in the view, — Json T, Dec 17 '20 at 14:49
Hi @O.Jones I already followed the suggestions about indexes, and I also added other indexes on other tables. I was able to remove clustered index scans on huge tables. — Json T, Dec 17 '20 at 14:50
@BobJasonTiamsic , the reported SQL in the plan is only 4,000 characters and ends `(1,2,4) AND PS.Delet` I doubt that is how it's supposed to be. You've said that hints aren't allowed in the view, but they are in the SQL you've shared eg `WITH(NOLOCK, INDEX(IX_WorkQueueItem_WorkQueueTypeID_ClosedDateUTC))`. And as I pointed out, they are probably more than likely hindering the ability of the DB rather than helping. — Andrew Sayer, Dec 17 '20 at 14:55
HI @AndrewSayer, thanks for pointing that out. Did not notice it. I included the entire query in the question — Json T, Dec 18 '20 at 08:59

score 0 · Answer 1 · answered Dec 26 '20 at 20:45

It's difficult to know where to start given that it's a giant query on a DB that I cannot access. But the obvious things that pop out to me from the query and query plan are as follows:

NOLOCK should only be used if you're happy with invalid data being returned. Not just uncommitted rows, but double or under-counting as well. E.g. if one of your Status or Type lookups get changed under you, you may end up doubling or removing every single row with those ids. If you are experiencing locking issues, a much better option is to use SNAPSHOT isolation level.
Do you really need all those columns returned? Cut back on every single unnecessary lookup. Can you cache the status and type lookups client-side?
I don't have your schema, but if any of the left joins can be changed to inner it may help, as the optimizer can reason about them better. Furthermore, some left joins depend on previous left joins, in which case a nested join may be more correct, and get better results.
Add filtered indexes on every table that you query Deleted = 0, with that as the filter. First index column should be StartDate on applicable tables.
The join on PatientInsuranceVerificationHistory should be rewritten as an OUTER APPLY.
Depending on cardinality, it may be better to join against the whole PatientInsuranceVerificationHistory with a ROW_NUMBER() filter.

    OUTER APPLY
    (  
        SELECT  TOP 1 PatientInsuranceVerificationHistoryID
        FROM    PatientInsuranceVerificationHistory phv
        WHERE   phv.PatientInsuranceID = PIN.PatientInsuranceID and phv.deleted = 0
        ORDER BY phv.CreatedDate DESC
    ) phv

Does PIN.Deleted need to be nullable? If not, you could index it as above.
An index on OfficeTaxID (OfficeID, StartDate) with includes would be useful.
Indexing and cardinality estimation against OR predicates is always difficult. One option which may work is to manually write an index union e.g. JOIN (SELECT * FROM a WHERE condition1 UNION ALL SELECT * FROM a WHERE condition2) a....
In the case of EndDate (very typical of Type 2 SCD), common solutions for efficiency are: adding an IsCurrent field to index against, or changing EndDate to use max-date (9999-12-31) instead of NULL (you can do this as a computed column).
The second last line can be rewritten as follows:
AND (wqi.ClosedDateUtc IS NULL OR wqi.ClosedDateUtc < DATEADD(Day, 30, GETUTCDATE())
which may help for cardinality estimation and to hit an index.
DATEDIFF is very difficult for the optimizer to reason against as you're asking it to run algebra against the inequality: GETUTCDATE - ClosedDateUtc < 30. With DATEADD you move it around to say: ClosedDateUtc < GETUTCDATE + 30, which the optimizer can use indexes against.

These points are very useful. I will try my best to follow and check for any possible change. Thanks! I will update this thread once I'm done. Really appreciate it! — Json T, Jan 04 '21 at 09:19

Reducing Logical Reads on Join Query

1 Answers1