Why is the index not seeking?

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


tmp52B7.pesession (12.3 kB)
avatar image By kudz 0 asked Aug 16, 2016 at 04:10 PM
more ▼
(comments are locked)
avatar image Aaron Bertrand ♦ Aug 24, 2016 at 03:43 AM

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...

avatar image TiagoPalhota Aug 24, 2016 at 10:36 AM

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.

avatar image Aaron Bertrand ♦ Aug 25, 2016 at 01:13 AM

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.

10|10000 characters needed characters left

2 answers: sort voted first

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
avatar image By Vlady Oselsky 156 answered Sep 01, 2016 at 01:01 PM
more ▼
(comments are locked)
10|10000 characters needed characters left

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

sp.png (25.2 kB)
avatar image By SQLkiwi ♦ 6.6k answered Sep 06, 2016 at 04:03 PM
more ▼
(comments are locked)
10|10000 characters needed characters left
Your answer
toggle preview:

Up to 50 attachments (including images) can be used with a maximum of 209.7 MB each and 209.7 MB total.

Follow this question

Topics:

x641
x455
x27
x1
x1

asked: Aug 16, 2016 at 04:10 PM

Seen: 1124 times

Last Updated: Sep 06, 2016 at 04:03 PM