SSIS with large amount of data slow and bad Performance via Kingswaysoft

2
votes

We have lots of SSIS packages that update/create records in a CRM.

Some entities like contact, account, customer product (our custom entity to hold all account types), while we are migrating records facing with slow performance and high CPU in SQL machine (has 20 processors). But generally to other custom entities we do not have any slowness or problem.

And to do these operation we use KingswaySoft application.

Let's say I have a SSIS package called CASA, if I execute this package with 10.000 data, it takes less than 1 minute I can complete my process. But if I want to execute with 250.000 data, package should be completed in half an hour but it is taking 1.5 - 2 hours almost.

We tried the combinations at the below but none of them gave good results for CPU and all of them gave around same time.

We do not understand why with less data everything finish so quickly, but cannot handle with larger data.

And we realized for every record a query sends to businessprocessflow to check there is a identified business process on it or not and that is using lots of CPU too. But when you enabled business process flow option on entity there is no way to turn back.

We use CRM on-premise.

Thread: 64, Batch: 250, AutoAdjustBufferSize: True, Default Buffer Max Rows: 1.000.000
CPU was 90 percent

Thread: 32, Batch: 250, AutoAdjustBufferSize: True, Default Buffer Max Rows: 1.000.000
CPU was around 90 percent

Thread: 20, Batch: 250, AutoAdjustBufferSize: True, Default Buffer Max Rows: 1.000.000
CPU was around 80 percent

Thread: 40, Batch: 100, AutoAdjustBufferSize: True, Default Buffer Max Rows: 1.000.000
CPU was around 80 percent

Thread: 20, Batch: 250, AutoAdjustBufferSize: False, Default Buffer Max Rows: 400.000,
DefaultBufferSize: 50.000.000
CPU was around 80 percent

There is an option called Engine thread. As default it is set to 10, we tried to decrease that to 1,2 nothing changed.

1
performancessiscpukingswaysoft
Which CRM are you using? – J Sidhu

1 Answers

2
votes

The performance may depend on many factors like network latency, the particular instance you are working with, the data volume, plugins or workflows, etc. You could try out our different options and see if that helps, but please note that it would depend on your particular environment and the entity you are working with, as this could be different from one case to another. We do not have standard instructions as such, but we certainly do have some recommendations. You can take a look at our FAQ page to know more suggestions: https://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-365/help-manual/crm/advanced-topics

You could also enable a proxy like Fiddler, or use CRM trace logs to find out where the bottleneck is. For working with trace logs, you would need to do that in the CRM server-side.

Let us know if there is anything else we can help you with and do not hesitate to reach out to our Support team directly.