Importing data via dataflow into Dataverse is very slow

[JMLR]

Hello,

I use dataflows to import data from an onpremise Business Central database into Dataverse, however the performance is terrible.  I need to refresh my data every 5 minutes but the refreshing itself takes at least this long and can spike at more than 1 hour. Today refreshing takes over 2 hours. The amount of data is minimal and when I check the underlying Power Query, I get results in seconds.

Is there any way to speed this up or tho check why this takes so much time.

[JMLR]

Hello @CharlesS ,

Performance is still too slow, but more irradical. I generally have runs of more than 30 minutest or even 1 hour but sometimes it reloads in 8 minutes (which is still too slow for the amount of data I use).

Microsoft is still looking into my logs but no answers yet.

[CharlesS]

@JMLR - I agree with you completely. The problem is the erratic performance.

The two ultra-simple test dataflows (one V1 and one V2 that are essentially the same), have a HUGE variance in performance:

The V2 dataflow loaded today in 50 minutes (33 minutes yesterday). The V1 version of the dataflow loaded today and yesterday in 7 seconds!

If anything, the newer V2 dataflows should be faster than the original V1 dataflows. 

I will be raising a support ticket with Microsoft for this, as it makes no sense.

[JMLR]

@CharlesS 

I have got a (final?) answer from Microsoft, it is not what I had hoped for. Their refresh rate was slightly faster than mine but still up to 26 minutes for little data.

I think it would be a good idea to raise the issue with Microsoft, perhaps when they see I'm not the only one having a problem will trigger them to reinvestigate the issue.

I included their answer below, especially the 1 but last sentence is very reassuring

Thank you for your feedback; it was much appreciated

The entire team has been really interested in this ticket, and we have performed a bunch of investigations regarding it.

We looked at it from different angles. Initially, we thought the refresh time was due to the fact that it coincided with the time updates were been done to improve the Dataflow ETL (Extract Transform Load) process.

However, despite this initial thought, we wanted to be a 100% sure, so we took a deeper dive, again, looking at it from a different angle.

This time around, we queried the backend and investigated using different telemetry details, including but not limited to, the event logs, traces, refresh history and other telemetry details, and everything appeared fine.

We took one more step, deciding that the best way to troubleshoot this issue is to reproduce it from our end – as we already have all the required details (thanks again for providing them).

The team was able to successfully reproduce this issue using an identical data and an environment same as what you have, and the results were the same; even after comparing the backend details of the reproduction we did, the results were still the same.

Based on this, on all the investigations done, we can safely confirm without any doubt that the performance of your dataflow is perfectly fine; although the execution may have taken a longer time, there is nothing unusual going on.

We have made this email to be as detailed as possible highlighting most of the steps we took, because we want you to know that you can show this to your project manager, clients, or any other stakeholder in the project, informing them that Microsoft Support has reviewed this issue multiple times and in great detail, and confirmed that the performance is perfectly fine. There is nothing unusual about the dataflow nor is there any thing that needs to be changed

If you are queried about the performance of the data flow with id – (), please show them this email from Microsoft Support – there is nothing to worry about.

We hope the above has clarified all your inquiries and that of any other stakeholder in the project; please could you confirm there are no further questions?

[DUNNJ]

Am I missing something?  From that reply, it would appear the performance of data loads can vary wildly, and that is perfectly fine?

[CharlesS]

@DUNNJ : I was thinking the same exact thing.

I am coordinating with our team to raise a ticket as well. I believe my quasi-identical V1 and V2 super simple test dataflows, where the V1 loads in 7 seconds and the V2 loads in 50+ minutes, will clearly showcase the problem. In theory, the V2 is supposed to be better than V1... yet that is demonstrably not the case.

From my standpoint, the performance of dataflows must be consistent. On time, every time. Anything else is not OK. My dream is to be able to receive a file, process it within seconds (or at least always roughly the same number of minutes) with PowerQuery (and I would be willing to pay for premium compute), load it into Dataverse, and launch Power Automate Flows downstream based on that data.

We can do this! Let's raise tickets in bulk so that Microsoft realizes the importance of this issue.

[miriki66]

Hi, Charles!

Sorry, no screenshot yet. I'm still trying to upload data on various methods. But what I can say: The PowerQuery displays the data in the (imported / linked) Excel worksheets from a OneDrive subfolder. I can verify that when re-editing after finishing the import wizard, too. As said: Only 10 tables with 2 rows of data (plus header row) each.

(btw: Is it correct that my numerical ID field is switched to text when declaring it as primary field?)

So the data seems to climb 3/4 up the hill, but it doesn't reach the hilltop.

Michael

[miriki66]

Hi, Charles!

Finally I got a first result as an even more simple Excel workbook with one worksheet and 2 rows of data (plus header row) was uploaded to the PowerApps Dataverse. After roughly 2 hours... 😞

If that thing needs 1 hour per line of data I'm very, very afraid of starting an upload with a dataset from production. Those roughly 38.5k lines would need about 4.5 years then. 😉

Michael

[DUNNJ]

It is great to have this functionality, and when the the performance and reliability is there I will be a happy Microsoft user and spread the word.  But, when 700 rows of data fail to load?  Happened @ 2am NZDT / 3pm CEST.   

[CharlesS]

Hello @miriki66 !

I am happy to learn that it ended up working out for you... but 2 hours in an unacceptable amount of time. However, I would not extrapolate that experience to your 38K lines. In my experience, more lines does not always mean more time. Look at the screenshot below, where I import a single row using a V2 dataflow... the performance is highly erratic.

I have been using a different dataflow (V1) for about a year to import 200K+ lines, with lots of fields, every Sunday. Usually, when things are running smoothly, it takes about 4 hours to load.

As far as I'm concerned, a 2 line dataflow that successfully loads in 2 hours is not a success, it represents a failure of the system... especially if it loads in 2 minutes on the next day.

Imagine that driving to work sometimes took 2 minutes, and sometimes took two hours. In both cases, someone could say that what matters is that you ended up getting to work in the end. How could you organize your work or life that way? You couldn't and you would end up finding another solution, perhaps moving somewhere else.

I have been discussing the issue of dataflow performance on many forums lately. The erratic performance we are experiencing is widespread. Unfortunately, some people are moving away from dataflows, and implementing much more complex ETL solutions (sometimes non-Microsoft solutions)... more complex, but with consistent performance.

We are developers (citizen or pro), not dataflow babysitters. I hope Microsoft will explain what is going on, there is a lack of transparency here. Dataflows are awesome, they have so much potential! But they need to work on time, every time.

[CharlesS]

@DUNNJ , I fully agree. At the beginning of 2021, when all dataflows were V1 dataflows, performance was consistent and I was really excited about dataflows. Everything changed when V2 dataflows were introduced. The stability of V1 dataflows started to falter (unexpected failures with "try again later" error messages) and V2 dataflows proved to be slower and even less reliable than V1 dataflows. 

While things were going great in Q1 2021, I started spreading the word amongst colleagues. Now, those people are (understandably) upset with me as I recommended an unreliable technology. Some managers in my organization are considering pulling the plug on any further development of data-based Power Apps specifically because of the performance issues of dataflows. 

Nevertheless, I remain optimistic that Microsoft will eventually realize that reliability and consistency is of capital importance in business processes and will address this issue transparently and resolutely. If you are a Microsoft employee reading this, do not hesitate to reach out. I will gladly do anything you want to help resolve this matter because I firmly believe in the product. Dataflows should simply be as reliable as any other Power Platform component.