CDS is slow during data load

[WillboBaggins]

Hello,

I have created a Flow to import nine data files, each containing over 115,000 records in XML format. The Flow consumes each file sequentially and the loop that loads each record into the CDS has Concurrency Control ON and the Degree of Parallelism set to 50.

Testing this on a small data set is fast. However at my max load of ~115,000 records per file, and nine files in total, the CDS appears to get slower as more files are loaded.

For example,
the first file took 17 minutes to load,
the second at 18 minutes,
the third at 35 minutes,
the fourth at 35 minutes,
the fifth at 1 hour
the sixth file is still loading and its has been over 14 hours...

Is there a throttling limit that I am hitting on the CDS?

[HSheild]

Hi @WillboBaggins 

Sounds like you are most likely exceeding the API limits of the CDS service.  Take a look at the article below and the supporting articles that it links to.

https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/api-limits-overview

This article also has some good strategies for working within these limits

https://community.dynamics.com/crm/b/magnetismsolutionscrmblog/posts/tackling-concurrency-issues-usi...

Is this import for a one off data migration or a regular process?  If it is a regular process you may want to look at an alternative approach or tools for updating the data into CDS

[jlindstrom · Accepted solution]

Yes--the more data you are loading in the slower it will get while data is loading. Do it after hours for best results.

Yes there is throttling. also Flow is not the fastest way to load data. Get kingswaysoft (kingswaysoft.com) and set up SSIS on your computer. You can use SQL express. Much faster as it supports multi-threading. You can use it free if you aren't scheduing data loads.

[WillboBaggins]

Thanks for the response. This would be a regular activity.

I wasn't sure of the full capabilities of the Power Platform, so the information you provided helps me to conclude that the Power Platform may not be the proper tool for my application. 

[jlindstrom]

But how much data are you loading and how are you doing it? Before concluding it isn’t a good fit be sure you are optimizing your load and only importing records that are new or have changed since the last load, use tooling that lets you do batch loads, don’t overuse upserts, flatten your data and include record ids so you don’t have to do a lot of round trip lookups, and one of the most important, be sure your loads aren’t triggering automated workflows or flows or plugins. With that I’ve seen very big loads work with no noticeable performance degradation. If you are seeing it slow down it is likely one of the things I mentioned Above