SQL Query get most recent activiy per account (efficient query)

I use MS SQL Server 2016 and try to query the MS Dynamics 2016 on-premise DB efficiently. You don’t need special knowledge about Dynamics to answer my problem because it’s rather an SQL but a CRM problem.

Suppose you have entities FilteredAccounts and FilteredActivities (that’s the name in Dynamics View)

FilteredActivites have the following columns:

accountid
name

FilteredActivites have the following columns:

activityid
actualend [DATETIME]
regardingobjectid (referring to account) 
statecode (int meaning 1 = completed)

What I am trying to achieve is a list of all Accounts which shows to each account the most recent completed activity.

I tried to get the most recent activities by:

SELECT TOP 1 * 
FROM [dbo].[FilteredActivityPointer]  As Activities
ORDER BY Activities.actualend DESC

Now how do I join FilteredAccounts and FilteredActivities efficiently so that per FilteredAccount row I just get the most recent FilteredActivity? Is the way I compute the most recent actitivity an efficient way of doing this`(i.e. > 1.000.000 rows for activities stored in the DB). Is there a better way of doing this (like triggering the script which stores the id of the most recent activityid on the account whenever an activity is added / changed. Would this harm the integrity of the CRM system? You don’t want to change the system db of your crm this way I suppose?

Answer

With the right indexes, cross apply might be the fastest approach:

select fa.*
from filteredaccounts fac cross apply
     (select top (1) fa.*
      from FilteredActivites fa
      where fa.regardingobjectid = fac.accountid and fa.statecode = 1
      order by fa.actualend desc
     ) fa;

The index you want is on FilteredActivites(regardingobjectid, accountid, actualend).