Speed Up the Newsfeed Business Productivity Accelerator Query

Posted by Jim Steger on April 15, 2009  |  Comments (2)

I have been recently playing with the Newsfeed Business Productivity accelerator for Microsoft Dynamics CRM on our production system. The implementation is pretty interesting and much simpler than I anticipated. Basically the newsfeed accelerator consists of a few workflow rules and an SRS report. The report brings back all closed activities, active announcements, and any notes that are prefixed with the word 'Newsfeed:'. The workflow rules are used to create these 'newsworthy' notes and can be configured against any workflow supported entity that has a relationship to Notes.

I had no trouble getting everything installed, but found that the report took around 35 seconds to run in my production environment and sometimes timed out on me. I traced the bulk of the time to the activity portion of the query which was surprising since we only have around 200,000 activities and 800,000 activity party records (i.e. not that much).

One of my colleagues, Brian, suggested I change the order by from modifiedon to modifiedonutc. Sure enough, the query was now coming back in a few seconds. The final report query now looks like:

SELECT     TOP (25) newsfeedid, createdby, createdbyname, modifiedbyname, modifiedon, modifiedonutc, description, regardingobjectid, subject, activitytypecode, 
                      activitytypecodename, regardingobjectidname
FROM
    (
  SELECT activityid AS newsfeedid, createdby, createdbyname, modifiedbyname, modifiedon, modifiedonutc, description, regardingobjectid, subject, activitytypecode, 
         activitytypecodename, regardingobjectidname
  FROM          FilteredActivityPointer
  WHERE      (activitytypecode IN (@Activities)) AND (statecode = 1) 
  UNION
  SELECT     annotationid AS newsfeedid, createdby, createdbyname, modifiedbyname, modifiedon, modifiedonutc, notetext as description, 
             objectid as regardingobjectid, subject, objecttypecode as activitytypecode, objecttypecodename as activitytypecodename, 
             filename as regardingobjectidname
  FROM         FilteredAnnotation
  WHERE     (subject LIKE 'newsfeed:%') AND (objecttypecode <> 4703)
    ) AS derivedtbl_1
ORDER BY modifiedonutc DESC

I am sure other optimizations could be made, but the changing the sort to use the UTC date value is a quick and easy one to improve your report performance immediately.