Microsoft Dynamics CRM sharing performance – Workflow challenge Part 2

A few weeks ago I wrote about a recent challenge I faced when I was tasked with creating a workflow and triggering it via a many to many relationship. Please read this blog here.  In part 2 of the blog, I will discuss another challenge I faced with that workflow related to performance of sharing multiple child Account records to system users.

The challenge was creating a workflow that needed to run multiple times throughout each day to ensure the appropriate shared rights were maintained on the dynamically changing child Accounts records. In order to do this, I needed to unshare those Accounts with the incorrect users and then share the Accounts with the appropriate users.  However, when doing this against thousands of records in a short period of time, will likely cause overall performance issues within the system.  To read more on the potential performance issues, you can read more about it in the article “Scalable Security Modeling with Microsoft Dynamics CRM 2013” by Roger Gilchrist, which can be downloaded here:  http://www.microsoft.com/en-us/download/details.aspx?id=40861

After reading the scalable security model article linked above, you will discover that several background queries are executed in order to determine privileges that a user has to a given object (record, view, etc.).  Because of this, there is a cache of security rights held on the application server to help optimize access for end users. However, with each change to the security rights of an end user, including sharing, the cache needs to be flushed and recreated.  As you can imagine, doing this frequently will impact performance.  In order to minimize this performance impact, I used fetchxml queries to check if the correct users were NOT already shared to the Account and only then, perform appropriate unshare/share requests on the record.  This essentially reduced the # of times the cache on the server needed to be recreated within a small amount of time, greatly reducing the system performance impact.

Here is an example of the fetchXML query I used to determine if the correct users were already shared to the Accounts.  It queries specific Accounts that need to be shared and the left join within the related PrincipalObjectAccess entity is determining if the correct users are not currently linked to each Account.  For more information on left joins in fetchXML queries, please refer to my colleagues blog.

String manyToManyAccountsNotShared = @”

<fetch version=’1.0′ output-format=’xml-platform’ mapping=’logical’ distinct=’true’>

<entity name=’account’>

<attribute name=’name’ />

<attribute name=’accountid’ />

<filter type=’and’>

<condition attribute=’accountid’ operator=’in’>”

+ FetchXMLaccounts +

@”</condition>

</filter>

<link-entity name=’principalobjectaccess’ to=’accountid’ from=’objectid’

link-type=’outer’ >

<filter type=’or’>

<filter type=’and’>

<condition attribute=’principalid’ operator=’not-in’>”

+ FetchXMLusers +

@”</condition>

</filter>

<condition attribute=’principalid’ operator=’null’/>

</filter>

</link-entity>

</entity>

</fetch>”;

The above example works well for on premise Microsoft Dynamics CRM organizations.  In a CRM Online organization, you will run into some limitations such as workflow timeouts, in which case you would need to break it out into smaller chunks and/or use other methods.

I hope this experience will help others out there. For more information on this, please contact Beringer .