Most Efficient Query Fix

Most Efficient Query Fix

The problem when sorting by assigned date and limiting by userid was that the cross apply was creating a huge amount of IO,
running for each row in the resultset.  By using an inner join the result set is limited further up the chain so to speak,
the cross apply remains in order to 'select distinct' (but on an already reduced result set).  

See row 32 for the inner join, cross apply remains
note on row 53 we are ordering by contactuser from the join, not the cross apply



SELECT  C.ContactId ,
        C.TenantID ,
        C.WebVisitorID ,
        C.FirstName ,
        C.LastName ,
        ( SELECT TOP 1
                    Name
          FROM      LeadSource
          WHERE     Alias = WebDetail.OriginalSource
        ) AS SourceName ,
        Email.EmailAddress ,
        C.BestPhone ,
        ( SELECT TOP 1
                    PhotoLink
          FROM      ContactSocialDetails
          WHERE     ContactID = C.ContactId
                    AND PhotoLink IS NOT NULL
          ORDER BY  SocialType
        ) AS PhotoLink ,
        C.ContactOriginTypeID ,
        C.DateCreated ,
        C.DateModified
FROM    Contact C 
inner join ContactUser CU1 on CU1.ContactID = c.ContactID and CU1.UserID = 579
        OUTER APPLY ( SELECT TOP 1
                                CE.EmailAddress
                      FROM      ContactEmail CE
                      WHERE     CE.ContactId = C.ContactID
                                AND CE.IsPrimary = 1
                    ) AS Email
        OUTER APPLY ( SELECT TOP 1
                                CWD.OriginalSource
                      FROM      ContactWebDetail CWD
                      WHERE     CWD.ContactId = C.ContactID
                    ) AS WebDetail
        CROSS APPLY ( SELECT TOP 1
                                CU.UserId ,
                                CU.AssignedDate
                      FROM      ContactUser CU
                      WHERE     CU.ContactID = C.ContactID
                                AND CU.UserID = 579
                    ) AS ContactUser 
WHERE   C.TenantID = 489
ORDER BY cu1.AssignedDate DESC        
OFFSET 100 ROWS        FETCH NEXT 250 ROWS ONLY

STATISTCS
(250 row(s) affected)
Table 'ContactSocialDetails'. Scan count 350, logical reads 1494, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LeadSource'. Scan count 0, logical reads 700, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactUser'. Scan count 351, logical reads 1599, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactWebDetail'. Scan count 0, logical reads 1422, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactEmail'. Scan count 350, logical reads 1495, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 0, logical reads 1422, physical reads 0, 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 32, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 15 ms,  elapsed time = 177 ms.
   
ORIGINAL QUERY STATS : NOTE the Scan and logical reads on the three tables in the cross apply statements

(250 row(s) affected)
Table 'ContactSocialDetails'. Scan count 4079, logical reads 17685, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'LeadSource'. Scan count 0, logical reads 8158, physical reads 0, 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.
Table 'ContactUser'. Scan count 306950, logical reads 1312649, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactWebDetail'. Scan count 0, logical reads 1247052, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactEmail'. Scan count 306950, logical reads 1309711, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Contact'. Scan count 9, logical reads 7811, physical reads 0, 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(s) affected)

 SQL Server Execution Times:
   CPU time = 3939 ms,  elapsed time = 721 ms.