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.

[CharlesS]

Hello!

I am also having similar issues. Very simple V2 dataflows that historically took 1 to 5 minutes are now taking hours. Problems started on September 28 and have been getting progressively worse every since. Yesterday, it took over 6 hours for my Dataflow to refresh. 

I have seen minor changes to the UI in the last few days and I'm thinking Microsoft is pushing updates into production. Perhaps these updates are having an effect on performance?

In any case, in what region is your environment? Mine is in Europe, and I have experienced several stability and performance issues with this server in the past.

Keep 'em flowin'!

Charles

[DUNNJ]

Also experiencing declining performance.  400 row table taking hours!!  Region is Europe.

[CharlesS]

Hello!

I suspect the Europe server may be the problem...

Today, it is worse : dataflows that have been consistently working for months (albeit with much longer delays recently) are failing outright... 

I hope this issue gets solved soon...

Best Regards,

Charles

[JMLR]

Hello CharlesS and Dunnj,

I'm also in the Europe region, as far as I can see my problems also started at september 28th.

I also made a service call to Microsoft, yesterday morning I had a teams meeting with them and some time after that the problem suddenly disappeared. However around 12 pm last night (amsterdam time I think) my refresh time is getting longer again. Can you check if your dataflows were much faster yesterday afternoon and getting slower since midnight? I have seen the same this morning on a clients environment so if this happened to all of us maybe it can help them to solve the problem.

Best Regards,

Jan Willem

[miriki66]

Hello,

I'm experiencing similar problems and am not sure if I made something wrong: (Very new to PowerApps and Dataverse, although experienced in VB.Net, C#, SQL and the like)

(sorry for german menu items)

I opened "Power Apps - Dataverse - Tabellen" and selected from top menu "Daten - Daten abrufen - Daten abrufen". I finished the whole wizard which has to import an Excel workbook with 10 worksheets as tables. It's only a test dataset where each table contains only 2 datarows. The tables have "xxxId" columns for primary keys and "xxxRef" columns to reference (1:n) details data (SQL join).

After quite a while the tables are listed as "benutzerdefiniert" in the "Tabellen" list. I can select one by clicking on its name and see the columns under "Spalten" in the top menu. But I can not see any data under the rightmost "Daten" menu item.

When I select "Dataverse - Dataflows" I see the (in the meantime renamed) dataflow pointing to my Excel import. It was not published yet and the "Entwurfsstatus" states "Die Veröffentlichung wird durchgeführt" - since roughly 9:30 this morning and it's 12:30 right now - the Excel workbook is 24 kb in size...

Am I missing something or is this a Microsoft / Dataverse problem as mentioned in the other posts?

Greetings

Michael

[CharlesS]

@JM 

Yes, I did get a window of time yesterday when things were running fine. The performance is still very spotty, even this morning. See my refresh history below (times shown are for Montreal, so +6 for Western Europe),

The warning I had this morning saw about half of my 5000 records imported, with this cryptic error message:

Import failed due to an intermittent issue. Please retry the operation.

I'm sure they will get things up and running in the next few hours/days, like they always do. But the problem is that these outages of several hours (I count a 5 minute flow running for 5 hours as an outage) happen way too often. I can't go on vacation without worrying about my flows crashing (not due to my code or data sources, the same dataflows running fine for a long time, and 100% Microsoft cloud-based sources). What we need is consistent performance we can count on.

Question: Does anybody know if Dataflow outages are covered by the Microsoft SLA? If so, I can start lining up claims...

[CharlesS]

Hello @miriki66 !

Could you provide a screen shot? It would be easier to understand what is going on.

When starting out with dataflows, the most common problems are related to mismatched data types (including stuff like strings being than the maximums specified in Dataverse), lookup values and choices. This Community contains many articles about these subjects that really helped me out!

However, concurrently, there are also server problems, particularly in Europe. Sometimes, it may be hard to tell if the problem is with our code or if the servers are the cause. 

I have been discussing these problems on other forums as well and many users are reporting performance and/or stability issues, especially in Europe, over the past week.

I wish there was a tool like Azure Service Health for Power Query to validate if the servers are having problems, so that could be accounted for in troubleshooting.

[CharlesS]

To follow-up on this issue, I am still experiencing performance issues with V2 dataflows.

I have developed crude troubleshooting tools to monitor the performance of my dataflows:

1. I created a new Dataflow Test table, with two custom columns, for the specific purpose of testing dataflows.

2. I repurposed an old V1 dataflow as a test dataflow, and made it as simple as possible, using a simple blank table as a source:

let

  Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKkktLlHSUTI0MlaKjQUA", 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"

3. I created a new similar V2 test dataflow:

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 purpose of these test scenarios is to eliminate any hypothesis that the data source or dataflow code could be at issue.

This morning, my V1 test dataflow succeeded within 7 seconds:

However, it took a whole 33 minutes for my test V2 dataflow to succeed:

33 minutes for a dataflow with a blank table source and a single row, importing to a quasi-empty Dataverse table!

Unless proven otherwise, this clearly indicates to me that Microsoft is still having server performance problems with Power Query / dataflows, at least in the Europe region.

@JMLR , @DUNNJ  : Are you still experiencing performance issues?

[DUNNJ]

I have reorganised the times my data loads are run to mitigate performance issues, to make sure they completed before my users are affected and have not experienced the performance issue, that is a failure, or not completed before users are aware.  However, the solution I have adopted is only viable because of the very simple data model used, and is not the best approach.  I sacrificed design and simplicity for successful data loads.