In this webinar, our experts showcase a variety of demo use cases of how different components of the...
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.
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 |
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:
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:
The tools tested were:
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:
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!
Excellent write-up. CRM Online API is limited to 2 parallel connections but ONLY if your batch size is above 10 records. You can configure your integration to run 64 parallel connections with a batch size of 10 giving you a very large performance boost against Dynamics Online. More details and sources here :http://www.simego.com/Blog/2013/10/Dynamics-CRM-Online-2011-to-2013-API-Performance.
Thanks for sharing!!! Would be neat to run comparisons between 64 parallel connections with batch of 10 vs 2 connections batch of 1000.
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