In any Dynamics CRM implementation, the overarching question always remains how to get data inside Dynamics CRM faster. I’ve been part of projects where the whole CRM development and UAT was completed in about 3 months’ time and the data migration took about 7 months to finish and this was On premises CRM and a Mainframe system. The situation becomes even more puzzling when Data resides in a legacy system hosted in on premises and CRM is hosted in Microsoft public cloud (CRM Online, where many more parties and components get involved). In today’s post we will look into some of the techniques we can adopt to expedite data movement to and from Dynamics CRM in general under any deployment model. I would discuss cloud specific techniques in a later post.
What are the problems?
The first step in solving this puzzle is to find out what actually causes slow data movement to and from Dynamics CRM. There could be variety of reasons contributing to this, ranging from bottlenecks at Dynamics CRM architecture to network issues. Following are some of the main reasons generally contributing to slow performance while loading data into Dynamics CRM:
- Web Services are generally slow. Data cannot be loaded straight into SQL tables (even in the case of on premises CRM deployments)
- Data volume is significantly large
- CRM process hooks (Plugins and Workflows) are running on create and update of data.
What strategy should we follow?
To solve a problem of this nature, what we need is a good strategy. Some of the key input to devise a strategy are:
- Know the infrastructure
- Latency and Bandwidth
- Server capacities
- Network standards
- Security Policies
- Network Load Balancer
- Hardware NLB
- Software NLB
- Define the acceptable throughput (how many records per sec)
- Decide on the tool (Buy vs Build)
- Define skills required to execute the task
What tool shall we use?
We can use a variety of tools for this purpose –
- Custom App
The right tool should be decided considering the following –
- Source System
- Data Volume
- One Time load vs recurring load
For one-time migration of low volume data from simple source systems like Excel or MS-Access, custom .NET applications can help. However, this is not suggested for high data volume and if complex transformations are needed before loading the data.
Here are some quick tips for building any custom app for data loading into Dynamics CRM –
CRM Diagnostic Tool for measuring Network Latency
- Check the Network Latency using CRM Diagnostic Tool
- To reduce latency issues, execute the custom app close to CRM server
Minimize the number of fields
- The less fields, the better performance. So, do not submit fields that have not changed.
- Smaller SOAP message will result in less networking time
- Some fields actually require additional SOAP service calls (with Create and Upsert messages) –
- parentsytemuserid and businessunitid (systemuser entity)
- businessunitid (team and equipment entities)
- Parentbusinessunitid (businessunit entity)
Replicate custom Business Logic into data migration app
- If there is any plugin/Workflow registered against a field on create or update message, then it’ll fire when field value is changed. This will hit performance. As a general guideline, replicate the business logic inside the custom app and turn off the plugins or workflows during data migration.
Use Bulk API
- Bulk API (ExecteMultipleRequest) is beneficial when network latency is high.
- Throttling of concurrent calls – for Microsoft Dynamics CRM Online there is a limit of 2 concurrent ExecuteMultipleRequest executions per organization
- Maximum batch size – A limit of 1000 requests is typical though this maximum amount can be set for the Microsoft Dynamics CRM deployment.
Avoid Generating Primary Keys (GUID)
- Manual Primary keys could cause performance issues. This is even not required as CRM server automatically generates GUIDs when record is created.
For On premises CRM –
- Turn on SQL Server RSCI (Read Committed Snapshot Isolation)
ALTER DATABASE [CrmOrganisationName_MSCRM]
SET ALLOW_SNAPSHOT_ISOLATION ON
ALTER DATABASE [CrmOrganisationName _MSCRM]
SET READ_COMMITTED_SNAPSHOT ON
- Add NOLOCK in FetchXml Query
<fetch mapping=”logical” no-lock=”true”>
<attribute name=”name” />
<condition attribute=”creditlimit” operator=”gt” value=”1000000″ />
- Set the Max Degree of Parallelism of SQL Server to 1.
- Recommended for CRM database but not for data warehouse