Insert Large Number of Records into Dynamics CRM Online

13 May 2013

Thoroughly updated for the changes in Dynamics CRM Online (2013) on 07-July-2014. You can be lazy and skip to a demonstration video at the end of the post.

As more and more value is being derived from enhancing Dynamics CRM with integrated data, there has been an issue with the rate at which you can create and then maintain records with in your Dynamics CRM online environment. It is considered slow, expensive and difficult to achieve, this is more down to the implementations of the third party vendors rather than some failing of the infrastructure or Dynamics API design.

For this demonstration we are going to use a default Dynamics Online instance with no optimisation, I did not need:

  • Microsoft Server Products
  • Microsoft SQL Server Products including DTS or SSIS
  • Any type of message queuing
  • Any type of xml experience.

I am going to import 50000 simple contact records into the contact entity in a Dynamics CRM Online instance but this solution scales directly to 1,000,000s records .

I did require:

  • The 50000 contact file, which can be found here.
  • Data Synchronisation Studio v3 found here.

I connected the Source to the CSV file and selected the 50000 record contact file.

Connect

I then connected the destination to our dynamics CRM Online instance.

Connect

I mapped contact and address details to make sure the sync reflected a realistic insert.

Mapping

The compare only takes about 2 seconds and DS3 predicts 50000 inserts and previews the records:

Compare Results

We have conducted extensive research in insert and update performance and we know that the current optimum Update Batch Size and parallelism in Dynamics CRM online is 16 Threads with 8 records per batch. This may seem counter intuitive but all of the performance testing has borne out these numbers.

See the post here.

It is very easy to introduce parallelism in to DS3 just change the 'ThreadCount' value. As with the parallelism the batch size is also easily configurable using the 'UpdateBatchSize' setting.

Properties

Clicking the synchronise button synchronises the 50000 records from the Source to the Destination. Our test take in 2 minutes and four seconds giving 400 records / second.

Sync

The excellent team at PowerObjects recently did some performance testing recently using SSIS so we have some similar statistics:

http://www.powerobjects.com/blog/2014/06/18/crm-data-integration-performance-testing-dynamics-crm/

Tool Batch Size Parallel Records/Hour
SSIS with KigsWaySoft Adapter 1000 1 500k
Scribe Online 1000 1 200 - 300k
Simego DS3 10 16 1440k

These are indicative numbers and you should conduct your own testing.

This in itself is faster than all competing solutions but what is more important is the ability to transform the source system data and re-synchronise. This is where a significant amount of migration pain is felt.

For example we could decide that the email address formatting is completely incorrect and now everyone has got the own domain names. So James Burns has an email address of 'James@burns.com'.

We can achieve this by using a calculated column which is like an excel function (in this case we used the CONCATENATE function). This just creates a new column in the source.

Calculated Column

I can then just map this new field onto the email in the entity.

Comparing the 50000 locally and the 50000 on Dynamics CRM Online takes a maximum of a couple of seconds. DS3 then shows you what it will be updated within Dynamics online. It will ONLY update columns that have been changed, meaning it will not update other fields which may trigger workflows or plug-ins inadvertently. Previewing the changes reduces any expensive rework in mapping and transformations.

Compare

This update takes a nearly exact time of 2 minutes and 6 seconds again giving an update performance of over 1.4 Million updates per hour.

Sync

Whilst it is possible for other products to use parallel servers with message queues, or control of flow components, it can soon becomes, expensive, unmanageable and time consuming without matching DS3 performance.

If you would like a quick demonstration of DS3's Dynamics CRM integration (or any other Sharepoint, Active Directory integration) please feel free to drop an email to support@simego.com.

We really are always happy to show of our solutions, share some of our clients successes and discuss Dynamics CRM API performance.

| |