I am dealing with issue that I need to save many records (>20k) into Dataverse tables and it takes too much time. Currently I run an Azure Function that connects to my Dataverse, query data as I need it, save the result of a query to the list and then records from the list are saved to Dataverse table. The query run fast (max 5 seconds), however save of all the records into Dataverse is extremely slow. What is also important, those tables are kind of "technical", the user does not work with them, I just store there data and use it on Canvas App for calculation purpose. To give you further insight, imagine that we have tables A, B and C that user works with, adds there new records, edits them etc. Once he pushes the button, I use data from tables A, B, C to create completely new records and save them to table D.
So I am thinking about creating those technical tables (D) in SQL database and saving records there cause I believe it would be much faster operation.
So I am looking the most efficient way to:
1. query data from Dataverse using SQL (https://docs.microsoft.com/en-us/powerapps/developer/data-platform/dataverse-sql-query)
2. save the result of the query to SQL database table
I already have Azure Functions that query data from Dataverse and save them to a list. Is there a way to insert this list into SQL tables by for example running stored procedure using C#? I wouldn't like to save those records one by one.
Hi @Termigez1 ,
How are you saving the records? One by one? Have a look at ExecuteTransactionRequest or ExecuteMultipleRequest to submit your create requests at once. It should be quite fast. Also, check that you don't have unnecessary custom logic (ie plugins or workflows) running on create.
Hope this helps!
Hi, thank you for your suggestion. Yes I run ExecuteMultipleRequest but it is still too slow. I mean when one user triggers the Azure Function it works fine. But once 3-4 users work with same application and want to create and save records into Dataverse tables it just slows down significantly. I am afraid I need to find a way to store those records in SQL database without changing whole architecture.
That's definitely not normal, I worked on environments where there were 5000+ concurrent users and it worked fine. Are you sure it's Dataverse and not some Azure Function related? If you try to import records in bulk with Excel Import as an example does it work fine? It's hard to tell without a lot of info about your implementation and all...
Have a look at this performance benchmark whitepaper, it has stats on 17k concurrent users: https://download.microsoft.com/download/D/6/6/D66E61BA-3D18-49E8-B042-8434E64FAFCA/PerformanceBenchm...
I assumed that there must be a bottleneck once Azure Functions save new records into Dataverse table. We were testing with different App Plans but even with most expensive/efficient the process was still too slow. So assuming that there is a limitation with how many records can be saved at once into Dataverse I consider changing it to SQL database where you can create thousands of records in a few seconds.
Bulk import of Excel with 10000 records will take a few minutes too. I imported records this way many times and usually when I refresh import tracker it adds 10-20 records each second. I consider it a normal thing for Dataverse or is it not?
For large batching, using ExecuteMultiple may not be enough as that will just use a single thread. You should consider splitting up the batches into multiple threads to maximize your throughout.
Note that each scenario is unique so you'll need to test a number of times to find the optimal batch size and thread count based on your hardware available to your app service plan, the complexity of your records, etc.
Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.
Learn how to create your own user groups today!
Please join us on Wednesday, January 19th, at 8a PDT. Come and learn from our amazing speakers!
Check out the new Power Platform Community Connections gallery!