POWEROBJECTS

718 Washington Ave. N. Suite #101
Minneapolis, MN 55401
View map and all Locations

Send us a message

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.

troubleshooting

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.

Environmentbatchsizeparallelaverage 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

Environmentbatchsizeparallelaverage 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.

ToolbatchsizeparallelRecords/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 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!

Avatar for JoeCRM

JoeCRM

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