SBX - Finance Heading

CRM Data Integration Performance Testing in Dynamics CRM

Options

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.

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)

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.

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 book, blogs 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!