Looking for PowerObjects? Don’t worry, you’re in the right place! We’ve been part of HCL for several years, and we’ve now taken the final step in our acquisition journey: moving our website to the HCL domain. Nothing else is changing – we are still fanatically focused on Microsoft Business Applications!

PowerObjects Blog 

for Microsoft Business Applications


CRM Data Integration Performance Testing in Dynamics CRM

Post Author: Joe D365 |

We are always looking for ways to improve our CRM integration and to keep an eye out for any possible bottle-necks that might be lurking. While we investigate, there are a few areas such as the environment, hardware, software or specific customizations that we like to focus on. To make sure everything goes as smoothly as possible, we have been testing potential bottle-neck and items that might affect performance of CRM integration to ensure our customers have the best experience possible.

Bulk Operation

The use of bulk operation is probably the easiest optimization for integration. In this example we gained 2-3 times the performance just by sending the inserts in batches of 1000. We have also discovered many best practices on optimizing batch operations, but details of those will be topic of a future blog.

Environment batchsize parallel average speed
Test 1 - 2 Core Virtual

1000

1

220k
Test 1 - 2 Core Virtual

1

1

60k

Parallelism

Most SQL operations will scale nicely to multiple CPU cores. However, on CRM web service side, each CRM call is usually isolated. We explored this originally in our blog Optimizing SSIS with MSCRM 2011 and further discussed Optimizing SSIS with CRM - Balanced Data Distributor.

Note that CRM Online restricts parallel batch operations to 2 parallel batch operations. This limit cannot be increased.

Note also that SSIS restricts parallel connections to 2 by default. This means that even if you split the CRM call to multiple threads, only 2 will start and rest will wait. This limit can be increased by adding maxconnection setting in:

  • DTSDebugHost.exe.config (when running from VS/BIDS)
  • DTSHost.exe.config (when running a deployed package)

CRM Data Integration Performance Testing in Dynamics CRM

Environment batchsize parallel average speed
Test 1 - 2 Core

1000

1

220k
Test 1 - 2 Core

1000

2

400k
Test 1 - 2 Core

1000

3

700k
Test 1 - 2 Core

1000

4

900k
Test 1 - 2 Core

1000

5

800k
Test 1 - 2 Core

1000

10

860k
Test 2 - 8 Core

1000

4

900k
Test 2 - 8 Core

1000

8

1250k
Test 2 - 8 Core

1000

10

1200k
Test 3 – Online

1000

1

200-300k
Test 3 – Online

1000

2

400-500k

Observations:

  • CRM Online actually seemed faster than on-prem CRM server with 1 and 2 parallel operations. There did seem to be more fluctuations on the performance however so keep the reservations when regarding this result.
  • Even if slow CRM server only had 2 cores, it continued getting good performance from additional parallel operations, up to 4 where it plateaued.
  • 8 core server and 2 core server were similar performance from 1-4 parallel operations, but 8 core server continued getting performance up to 8 parallel operations. The fastest integration average was about 1.2 million records in an hour.

Tools

The tools tested were:

  • SSIS with script component
  • SSIS with KingsWaySoft Adapter
  • Scribe Online

This test was done against CRM Online test organization, creating custom entity records with batch size of 1000.

Tool batchsize parallel Records/hour
SSIS with script component

1000

1

300k
SSIS with script component

1000

2

550k
SSIS with KingsWaySoft Adapter

1000

1

300k
SSIS with KingsWaySoft Adapter

1000

2

500k
Scribe Online

1000

1

200-300k

Observations:

Since the tests were not run enough times, the error margins would be fairly high. However, keeping this in mind, the observations are:

  • SSIS with script component and KingsWaySoft were identical within the error margin.
  • Scribe Online had mostly comparable performance as well, but seemed to fluctuate more.
  • Scribe Online is not trivial to run in parallel, like SSIS is. Therefore, I only included test with 1.

Conclusions

Obviously the results published here have merely scratched the surface on what happens when we optimize integration packages. But it is clear that a lot of thought has to go into optimizing the environment, the design and the use of tools in order to get the fastest possible integration.

In general, there is not a huge difference on the optimal performance between the tools that are used. Mainly the difference comes from how they can be used to handle the aspects of integrations that slow it down or speed it up, such as batch sizes, parallelism, lookups, optionsets, etc.

Also, many times improvements on certain areas will not be beneficial until the actual bottleneck is solved. Very often the performance of SQL server is not an issue for integration, generally the CRM server and the limitations of the web services will bottleneck the operations enough that improvements on SQL server will have only marginal benefit. Only at the very high end, with the 8-core physical server and 8+ parallel operations we were seeing SQL server having any considerable utilization.

Whether it is our CRM bookblogs or webinars, we are always striving to bring you the best in Dynamics CRM education. Make sure to keep checking back as our materials continue to grow. And remember... Happy CRM'ing!

Joe CRM
By Joe D365
Joe D365 is a Microsoft Dynamics 365 superhero who runs on pure Dynamics adrenaline. As the face of PowerObjects, Joe D365’s mission is to reveal innovative ways to use Dynamics 365 and bring the application to more businesses and organizations around the world.

3 comments on “CRM Data Integration Performance Testing in Dynamics CRM”

    1. Thanks for sharing!!! Would be neat to run comparisons between 64 parallel connections with batch of 10 vs 2 connections batch of 1000.

      1. Hi Joe,

        Lots of threads look like they perform better because they can be spread over many Online CRM app servers. From our testing it looks like 16 Parallel connections with 10 items per batch is the sweet spot as we get about we get about 1.44 Million records per hour running similar tests, our test with 2 connections exactly match your results at 550 k per hour. I updated our performance results here: http://www.simego.com/Blog/2013/05/How-to-insert-a-large-amount-of-records-into-Dynamics-CRM-Online

PowerObjects Recommends