Why is the index not seeking?
If you look at the plan 9 Rows are arriving at the Merge Join and it decides to do a scan of the accountTransaction table by using the correct index (scanning).
My question is why is not seeking by applicationiID? We are talking about 9 rows so I probably was expecting a Nested Loop with an Index Seek.
The access to accountTransaction is performing a row_number partitioned by applicationID and transactionDate but wouldn't it be easier to seek by applicationID first and then do the rest? The index is a covering index but it still prefers to scan the entire table returning 114660 rows when it could have returned around 50/60 rows.
I tried to reduce the number of reads by adding a filter on transactionDate but the plan changes completely to a nested loop that will execute 9 times and scanning the accountTransaction (800K reads..) so an extra filter doesn't help.
-----------------------View Code---------------------------------- ALTER VIEW [dbo].[vw_applicantSearch_by_Interviewer4] AS SELECT ul.UserID, app.ApplicationID, ul.FirstName, ul.LastName, ul.Email, ul.FirstName + ' ' + ul.LastName as 'fullname', a.LastContact as 'lastlogin', usm.StateName as 'userstate', app.Programme, app.InterviewerID, app.Season, ac.interviewerSelectedDate, tpi.interviewDateTime, tpi.interviewstatus, ac.firstPayment, ac.applicationComplete, ref.No_of_references, notes.No_Of_UnreviewedNotes, ISNULL(ac.interviewReportComplete, 0) As 'int_report_complete', at.transactionDate, com.CommentBy, com.Comment, ac.interviewerViewed, app.Returner, app.rejected, ul.createdOn, app.requiresInterview, CASE ISNULL(afm.applicationID,0) WHEN 0 THEN 0 ELSE 1 END As 'IsInDemand', ul.CountryCode, ul.RegionId, upload.URI, app.applicationRating, ul.DateOfBirth, app.earliestAvailabilityDate, app.fullTimeStudent, id.first_name + ' ' + id.last_name as 'interviewerName', orgType.OrgType, orgType.Status, orgType.DBSstatusDescription FROM tbl_UserLogin ul INNER JOIN tbl_applicant a ON ul.ApplicantID = a.ApplicantID INNER JOIN tbl_application app ON a.ApplicantID = app.ApplicantID INNER JOIN tbl_season s ON s.Season = app.Season AND s.Is_Current = 1 INNER JOIN tbl_user_state_message usm ON app.UserState = usm.UserState LEFT JOIN tbl_interviewer_details id ON id.interviewer_id = app.InterviewerID LEFT JOIN (SELECT appi.applicationID, appiu.URI FROM tbl_application_images appi INNER JOIN tbl_application_image_uploads appiu ON appi.imageID = appiu.imageID AND (appi.[Type] = 1 OR (appi.[Type] IS NULL))) upload ON app.ApplicationID = upload.applicationID LEFT JOIN tbl_applicant_checklist ac ON app.ApplicantID = ac.applicantID LEFT JOIN tbl_pending_interviews tpi ON app.ApplicationID = tpi.applicationid LEFT JOIN (SELECT COUNT(*) As 'No_of_references', applicationID FROM tbl_application_images tai WHERE tai.[Type] = 3 AND tai.[Status] <> 0 GROUP BY tai.applicationID ) ref ON ref.applicationID = app.ApplicationID LEFT JOIN (SELECT COUNT(*) As 'No_Of_UnreviewedNotes', applicationID FROM tbl_application_document_notes adn WHERE adn.reviewed = 0 GROUP BY adn.applicationID) notes ON notes.applicationID = app.ApplicationID LEFT JOIN ( SELECT cat.transactionDate, cat.applicationID, ROW_NUMBER() OVER (PARTITION BY cat.applicationID ORDER BY cat.transactionDate DESC) AS GroupRowNumber FROM COLAFinance.dbo.accountTransaction cat WHERE cat.feeTypeID = 1-- AND cat.transactionDate >= DATEADD(YEAR,-1,GETDATE()) ) at ON at.applicationID = app.ApplicationID AND at.GroupRowNumber = 1 LEFT JOIN (SELECT sub.Comment, sub.CommentBy, sub.ApplicationID FROM ( SELECT ac.Comment, ac.CommentBy, ac.ApplicationID, ROW_NUMBER() OVER (PARTITION BY ac.ApplicationID ORDER BY ac.CommentDateTime DESC) AS GroupRowNumber FROM tbl_application_comments ac WHERE ac.CommentByType = 'INT' AND ac.CommentDateTime >= DATEADD(YEAR,-1,GETDATE()) ) AS sub WHERE GroupRowNumber = 1) com ON com.applicationID = app.ApplicationID LEFT JOIN (SELECT DISTINCT(applicationID) FROM tbl_application_filter_match) afm ON afm.applicationID = app.ApplicationID LEFT JOIN (SELECT TOP 1 so.OrgType, ch.Status, rcs.DBSstatusDescription, cid.ApplicationId FROM tbl_application_crb_initialData cid INNER JOIN tbl_season_organisationId so ON cid.OrganisationId = so.OrgId LEFT JOIN tbl_crbHistory ch ON cid.ClientReference = ch.ClientReference LEFT JOIN ref_crbStatus rcs ON ch.Status = rcs.statusId ORDER BY cid.DateAdded DESC, ch.DateAdded DESC) orgType ON app.ApplicationID = orgType.ApplicationId; GO
I saw 9 rows arriving to the nested loop and I was wondering why didn't it go for a seek ( each one returning on average 5 rows) and instead deciding to do a massive scan 9 times.
Thanks a lot for the reply.
One last thing to consider. Parameter sniffing could be killing your performances especially since you are selecting from a view and a plan was created based on "bad" parameter, try RECOMPILE option to check if it gives you better plan that runs much faster.
SELECT UserId , ApplicationId , fullname , userstate , interviewDateTime , interviewerSelectedDate , lastlogin , ISNULL(firstPayment, 0) AS 'firstpaymentstatus' , transactionDate , ISNULL(applicationComplete, 0) AS 'applicationcomplete' , ISNULL(No_of_references, 0) AS 'no_of_references' , ISNULL(No_Of_UnreviewedNotes, 0) AS 'no_of_unreviewednotes' , ISNULL(int_report_complete, 0) AS 'int_report_complete' , Comment , CommentBy , ISNULL(interviewerViewed, 0) AS 'interviewerviewed' , IsInDemand , URI , DateOfBirth , earliestAvailabilityDate , Programme , fullTimeStudent , OrgType , [Status] , DBSstatusDescription FROM ( SELECT ul.UserID , app.ApplicationID , ul.FirstName , ul.LastName , ul.Email , ul.FirstName + ' ' + ul.LastName AS 'fullname' , a.LastContact AS 'lastlogin' , usm.StateName AS 'userstate' , app.Programme , app.InterviewerID , app.Season , ac.interviewerSelectedDate , tpi.interviewDateTime , tpi.interviewstatus , ac.firstPayment , ac.applicationComplete , ref.No_of_references , notes.No_Of_UnreviewedNotes , ISNULL(ac.interviewReportComplete, 0) AS 'int_report_complete' , at.transactionDate , com.CommentBy , com.Comment , ac.interviewerViewed , app.Returner , app.rejected , ul.createdOn , app.requiresInterview , CASE ISNULL(afm.applicationID, 0) WHEN 0 THEN 0 ELSE 1 END AS 'IsInDemand' , ul.CountryCode , ul.RegionId , upload.URI , app.applicationRating , ul.DateOfBirth , app.earliestAvailabilityDate , app.fullTimeStudent , id.first_name + ' ' + id.last_name AS 'interviewerName' , orgType.OrgType , orgType.Status , orgType.DBSstatusDescription FROM tbl_UserLogin ul INNER JOIN tbl_applicant a ON ul.ApplicantID = a.ApplicantID INNER JOIN tbl_application app ON a.ApplicantID = app.ApplicantID INNER JOIN tbl_season s ON s.Season = app.Season AND s.Is_Current = 1 INNER JOIN tbl_user_state_message usm ON app.UserState = usm.UserState LEFT JOIN tbl_interviewer_details id --Consider INNER JOIN if possible ON id.interviewer_id = app.InterviewerID LEFT JOIN ( SELECT appi.applicationID , appiu.URI FROM tbl_application_images appi INNER JOIN tbl_application_image_uploads appiu ON appi.imageID = appiu.imageID AND ( appi.[Type] = 1 OR ( appi.[Type] IS NULL ) ) ) upload ON app.ApplicationID = upload.applicationID LEFT JOIN tbl_applicant_checklist ac ON app.ApplicantID = ac.applicantID LEFT JOIN tbl_pending_interviews tpi ON app.ApplicationID = tpi.applicationid LEFT JOIN ( SELECT COUNT() AS 'No_of_references' , applicationID FROM tbl_application_images tai WHERE tai.[Type] = 3 AND tai.[Status] <> 0 GROUP BY tai.applicationID ) ref ON ref.applicationID = app.ApplicationID LEFT JOIN ( SELECT COUNT() AS 'No_Of_UnreviewedNotes' , applicationID FROM tbl_application_document_notes adn WHERE adn.reviewed = 0 GROUP BY adn.applicationID ) notes ON notes.applicationID = app.ApplicationID LEFT JOIN ( SELECT cat.transactionDate , cat.applicationID , ROW_NUMBER() OVER ( PARTITION BY cat.applicationID ORDER BY cat.transactionDate DESC ) AS GroupRowNumber FROM COLAFinance.dbo.accountTransaction cat WHERE cat.feeTypeID = 1 -- AND cat.transactionDate >= DATEADD(YEAR,-1,GETDATE()) ) at ON at.applicationID = app.ApplicationID AND at.GroupRowNumber = 1 LEFT JOIN ( SELECT sub.Comment , sub.CommentBy , sub.ApplicationID FROM ( SELECT ac.Comment , ac.CommentBy , ac.ApplicationID , ROW_NUMBER() OVER ( PARTITION BY ac.ApplicationID ORDER BY ac.CommentDateTime DESC ) AS GroupRowNumber FROM tbl_application_comments ac WHERE ac.CommentByType = 'INT' AND ac.CommentDateTime >= DATEADD(YEAR, -1, GETDATE()) ) AS sub WHERE GroupRowNumber = 1 ) com ON com.applicationID = app.ApplicationID LEFT JOIN ( SELECT DISTINCT ( applicationID ) FROM tbl_application_filter_match ) afm ON afm.applicationID = app.ApplicationID LEFT JOIN ( SELECT TOP 1 so.OrgType , ch.Status , rcs.DBSstatusDescription , cid.ApplicationId FROM tbl_application_crb_initialData cid INNER JOIN tbl_season_organisationId so ON cid.OrganisationId = so.OrgId LEFT JOIN tbl_crbHistory ch ON cid.ClientReference = ch.ClientReference LEFT JOIN ref_crbStatus rcs ON ch.Status = rcs.statusId ORDER BY cid.DateAdded DESC , ch.DateAdded DESC ) orgType ON app.ApplicationID = orgType.ApplicationId WHERE app.InterviewerID = @interviewerId AND ( ( interviewDateTime IS NULL ) OR ( interviewstatus = 'REJD' ) ) ) AS vw
Not all possible transformations have been coded into the optimizer for good reason. The most commonly needed and beneficial ones are mostly there, but when you come across a missing one like this, you need to perform the transformation yourself. Simply write the OUTER JOIN as an OUTER APPLY, for example:
CREATE TABLE dbo.App ( ApplicationID integer NOT NULL ); CREATE TABLE dbo.accountTransaction ( applicationID integer NOT NULL, transactionDate date NOT NULL, feetypeID integer NOT NULL ); CREATE INDEX fi ON dbo.accountTransaction (applicationID, transactionDate, feetypeID) WHERE feetypeID = 1; SELECT * FROM dbo.App AS A OUTER APPLY ( SELECT AT.transactionDate, AT.applicationID, GroupRowNumber = ROW_NUMBER() OVER ( PARTITION BY AT.applicationID ORDER BY AT.transactionDate DESC) FROM dbo.accountTransaction AS AT WHERE AT.feetypeID = 1 AND AT.applicationID = A.ApplicationID ) AS OA;
Execution plan: