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]

Well, I have informed my "stakeholders" that, according to Microsoft, there is nothing to worry about 🤣. They were not as understanding as Microsoft thought they would be. I'm still trying to get them to understand that if I don't have a problem with my connection and also not with my the query collecting the data there still can be a problem on their server.

I think it would be of help if more of us would raise the issue with Microsoft so they will get a sence of urgence.

[CharlesS]

@JMLR Yes, exactly! Microsoft needs to admit there is a problem with their servers, and put in place solutions to remediate the issue. This is why I created this super simple dataflow, which has no outside source (no connection to an outside date source) and only two lines of code:

let

  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLikzUtJRsrQwV4qNBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [KeyColumn = _t, DataColumn = _t]),

  #"Changed column type" = Table.TransformColumnTypes(Source, {{"KeyColumn", type text}, {"DataColumn", Int64.Type}})

in

  #"Changed column type"

The performance on such a simple dataflow should be consistent. If it isn't, the issue is surely with the servers.

My hypothesis is that queries are "queued". The queries themselves actually run very quickly, but may be waiting for processing in a queue for a very long time.

In Canada, we have universal healthcare. If you cut yourself, you can go to the hospital and get stitched up for free. It will only take the doctor a few minutes to complete the stitches... but (in Quebec, at least), you will have to wait 12+ hours in a waiting room before you get to see the doctor. The problem is not the doctor or the stiches, the problem is the wait time... 

[JMLR]

Hello all,

Since last weekend it seems Microsoft has fixed the problem (for now), my query runs in under a minute now. Can you confirm the refreshing is back to normal for you?

[DUNNJ]

I am seeing improved performance but still with random failures.

[CharlesS]

@DUNNJ  & @JMLR : I am also seeing improved performance. I think back-end server updates were deployed, in the Europe Region, on October 27. Here is some evidence (this is a V1 dataflow):

On October 27, my V2 test dataflow ran for over two hours early in the day... and then ran in 13 seconds around noon. Today, it ran in about a minute:

The Power Query Editor was also down early in the day on October 27 and I noticed subtle changes to the UI:

I may be wrong, but I don't remember seeing this information on the bottom left of the Editor screen before the 27th. Now everything is working fine.

Nonetheless, I have raised a support ticket with Microsoft, and was able to escalate it all the way to the developers. While everything is working fine now, I'm pushing for stability and dependability in the long term. I clearly stated the objective of my support ticket as such:

"Ultimately, here is the purpose of this whole ticket:

1. We would like to understand the factors that affect a dataflow’s performance (run time). I suspect the following factors may come into play (I may be wrong):
   1. Back-end server updates. If so, there should be a notice on the many dataflow list screen stating that updates are in progress and that dataflow performance may be affected, something like

      Ideally, we would also be advised of upcoming updates in a similar fashion.

   2. Server is very busy and the dataflow is “queued". If so, it would be great to use a pay-as-you-go model to allocate premium compute capacity to mission-critical dataflows (a little like Azure Data Factory, where you can select your compute type.)

2. Ultimately, the goal is for dataflow performance to be consistent (+/- 20% compute time for identical loads, such as my test dataflows) and predictable."

Despite my frustration with sporadic dataflow performance in the last year (and particularly in the last month), I remain a strong advocate for Power Query and dataflows. All that's missing is performance consistency, and I will continue pushing Microsoft towards that goal. I will keep you posted on the outcome of my support ticket!

[CharlesS]

Hello @DUNNJ  & @JMLR !

Here is an update from my service ticket with Microsoft. Firstly, Microsoft acknowledges that they had a "couple of events that hurt the dataflow’s V2 refresh" in October. They confirm that these issues have since been mitigated. I can concur that, since October 27 (PM), I have seen a signification improvement in both performance and performance consistency. 

Here are responses they provided to some of my questions:

1. Can we expect consistent performance over time going forward? Yes, Dataflow V2 refreshes duration are improved as the data volume is increasing
2. In the event that performance disruptions do occur, will users be advised (ideally, directly on the dataflow summary page of the maker portal)? You could post the request here to have it displayed in the data summary page. - I submitted the idea here : Show notification in marker portal when Dataflow /... - Power Platform Community (microsoft.com) ... please vote!
3. Some level of performance fluctuations is to be expected. Given identical data loads, what does Microsoft consider to be an "acceptable" fluctuation? (Example: +/- 20%) Yes, that's our expectation, that run times are more consistent. 
4. Are servers in some regions more reliable than others? No, unless you have let's say Salesforce in Europe and Dataverse in USA, then it could be "slower" than both services in the same region, but all regions are equally reliable.
5. What are the factors, other than the mashup code and data sources, that influence dataflow performance? I'm checking this with development. - I will keep you posted!

Keep 'em flowin'!

Charles

[arpost]

I’m in the United States, and I’m finding Power Apps Dataflow performance keeps progressively getting worse and more sporadic. 😞

Dataflow runs:

• Relatively straightforward refreshes on small test files (2 rows) take 5 minutes

Editor:

• Viewing basic steps in the Power Query Editor takes minutes, then seconds, then minutes randomly.
• The performance seems to become exceptionally bad toward the end of the day. It’s also not my internet as other operations work just fine.
• I sometimes have to wait forever (and even end up refreshing) the Dataflow editor when trying to select load destinations.

I love Power Query and Dataflows, but these performance issues really make it difficult for me to recommend my organization adopt them. I have pro devs who tell me that Power Query isn’t an enterprise-scale ETL solution, and I HATE that I can’t confidently disagree with them. Really hope Microsoft fixes this and unlocks larger data processing.

[CharlesS]

Hello @arpost ,

As discussed earlier in this thread, I opened a support ticket a few weeks ago to address the erratic and inconsistent performance of dataflows. At the beginning of November, things started working fine again... for a while. This week, some of my V2 dataflows started failing again. This seems to coincide with a backend update, as I noticed some changes in the UI at the same time as the dataflows started failing again. As such, I reopened my support ticket with Microsoft.

The same dataflow, using the same data source, now fails at every attempt, but returns different error messages:

• Error Code: Mashup Exception Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Evaluation resulted in a stack overflow and cannot continue.
• Error Code: Mashup Exception Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Evaluation ran out of memory and can't continue.
• Error Code: Mashup Exception Error, Error Details: Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: We're sorry, an error occurred during evaluation.

I would recommend that any users experiencing similar erratic performance open official support tickets with Microsoft. My hope is that Microsoft will realise the extent of these problems throughout its customer base, and allocate the necessary ressources to ensure dataflows performance is on par with other elements of the Power Platform.

Dataflows have great potential... they are a fantastic tool. But Microsoft must improve performance stability and consistency before dataflows can reach their full potential and be used for mission-critical, enterprise-scale applications.

Keep 'em flowin!

Charles

[DUNNJ]

I have had the same experience.  Service has been reliable and now I'm getting erratic performance with random failed data loads.

[CharlesS]

Hello @DUNNJ ,

I reopened my service ticket, following the problems I started experiencing last week. Microsoft has confirmed that there was an issue with Power Query starting around December 8. According to Microsoft, the issue was resolved Friday, December 10. I can confirm that my V2 flows are running smoothly once again. This certainly applies to the Europe region server; I cannot confirm if it applies to other regions as well.

Of course, I am happy that everything is back in order for me, but I hope that, going forward, performance will be consistently stable. I would encourage you to place a service ticket (or reopen an old ticket) to report your issue to Microsoft if it persists. Not only can service tickets help you solve your specific issues, if many customers raise support tickets when there are problems, this will prove that erratic dataflow performance problems are not isolated issues.

Keep 'em flowin'

Charles