Why is the index not seeking?

kudz 2016-08-16 16:10:13

My idx_lastTransaction is a filtered index created on applicationID, transactionDate, feeTypeID (filtered by ([feetypeID]=(1))).

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
Aaron Bertrand 2016-08-24 03:43:19
A filtered index is most beneficial when it eliminates a lot of rows; in this case you've only eliminated around 30% of the table by the filter alone. Between the complexity of the query and the number of tables involved in the underlying view, it seems SQL Server still has to consider the remaining rows (70K+) and a seek would be prohibitively expensive in this case. This is quite far from any sort of bottleneck in this query, however; I would focus on the sort on the two date columns in the lowest branch, perhaps there is a better index you can employ on those two tables to change that from a hash join to a merge join…
TiagoPalhota 2016-08-24 10:36:32
Thanks Aaron,

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.

Aaron Bertrand 2016-08-25 01:13:57
Well I see a scan 9 times for 28,000+ rows each, not 5 rows – which node ID shows an average of 5 rows? SQL Server doesn't know there will only be 9 rows returned out of the original 814,000+ until after the sort. Which it can't perform first because of the hash joins.
Vlady Oselsky 2016-09-01 13:01:39
Stored procedures that just select from a view always make me nervous. Best thing to fix this would be to remove the view from the equation and rewrite your stored procedure to select from base tables and having the proper filter on corresponding tables. If I was to rewrite it, it would look something like the code below. Additionally, if you don't need some columns in return set, you should consider removing them and tables that they come from completely from the code. Try to run the following code and compare output to original output of SP.

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
SQLkiwi 2016-09-06 16:03:49
There's no fundamental reason, it's just an optimizer limitation. It can do all the things you want: matching the filtered index, pushing predicates past window functions and so on, but asking it to also transform the outer join to an outer apply is a step too far.

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:

alt text