0

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?

HHeckner
  • 4,722
  • 4
  • 23
  • 33

1 Answers1

2

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

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • That was amazingly FAST !!!! Thanks. Just for others who are not as experienced ... I needed to add a * after select top (1). I gave the wrong entity names, it should be FilteredAccount and FilteredActivityPointer but that way my fault. GREAT SOLUTION! – HHeckner May 14 '20 at 17:24