another more efficient way

another more efficient way

This one drops the cross apply and uses to LEFT JOIN to accomplish the same thing
This does not seem to be as efficient as the combination of one join and one cross apply
But is still MUCH more efficient than just using the cross apply

Rows 30 and 31 have the two joins

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 
LEFT JOIN (SELECT top 1 contactid FROM ContactUser GROUP BY ContactID) CU ON CU.ContactID = c.ContactID
LEFT JOIN ContactUser CU2 ON CU2.ContactID = CU.ContactID AND CU2.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
WHERE   C.TenantID = 489
ORDER BY cu2.AssignedDate DESC        
OFFSET 100 ROWS        FETCH NEXT 250 ROWS ONLY


STATISTICS

(250 row(s) affected)
Table 'ContactUser'. Scan count 10, logical reads 1449, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactSocialDetails'. Scan count 4899, logical reads 21096, 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 9798, 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 19971, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ContactEmail'. Scan count 4899, logical reads 21170, 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 '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 '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 = 499 ms,  elapsed time = 203 ms.