cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Termigez1
Frequent Visitor

Most efficient way to save result of CDS query into SQL database

Hello, 

 

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. 

 

 

 

 

 

 

6 REPLIES 6
EricRegnier
Super User
Super User

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

Cheers

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?

Hi @Termigez1,

 

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. 

 

https://markcarrington.dev/2020/12/04/improving-insert-update-delete-performance-in-d365-dataverse/

 

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

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Users online (1,006)