Gen 2 dataflows extremely slow vs. Gen 1 dataflow

[slittle4782]

I'm finding Gen 2 dataflows in Fabric to be much slower than Gen 1 dataflows.  Notably when pulling from an Excel file.

I created a Gen 1 and Gen 2 dataflow with identical M-code in the same Fabric workspace.  The M-code extracts data from two very small tables from an Excel file on SharePoint.

The Gen 1 dataflow refreshes in about 1 second.

The Gen 2 dataflow refreshes in 17-23 seconds.

Anyone else seeing this?  Any advice?

FWIW - here's the M-code for one of the Excel tables.  (The other table is different only in table name

let

  Source = Excel.Workbook(Web.Contents("https://direct.sharepoint.com/sites/DirectHub/PowerBi/DMI%20Holidays.xlsx"), null, true),

  #"Navigation 1" = Source{[Item = "DanishHolidays", Kind = "Table"]}[Data],

  #"Changed column type" = Table.TransformColumnTypes(#"Navigation 1", {{"Danish Holidays", type date}})

in

  #"Changed column type"

)

Appreciate it.

[mike_honey]

Just struck this same issue in an existing semantic model where I needed to convert some of the inputs from Gen1 to Gen2 dataflow.  Previously it would refresh in around 2 minutes in the web service (Premium capacity), but after that change, it did not complete after 90 minutes.  I also noticed the Query Editor became very sluggish, would not refresh for some more complex queries built on the Gen2 data.  Note no issues previously with Gen1.

Following a hunch I added Table.Buffer steps to the end of each Gen2 query. I keep these brief so the steps are just:
Source
Navigation
Table.Buffer

With those in place, Query Editor performance was good again.  A web service refresh completed in 2 minutes.  This seems a useful workaround (YMMV).

[hackcrr]

Hi, @slittle4782 

The significant difference in refresh times between Gen 1 and Gen 2 dataflows in Fabric, especially when dealing with identical M-code, could be due to several factors related to how each generation handles data processing and connections.

Possible Reasons: 

Performance Settings: Ensure that the performance settings for your Gen 2 dataflows are optimized. Check if there are any configurations specific to Gen 2 that might be affecting performance.
Connection Throttling: Gen 2 might handle connections differently, potentially throttling them in a way that impacts performance.
Power Query Optimization:

Query Folding: Ensure that query folding is occurring, which allows operations to be performed at the source rather than locally. This can significantly speed up processing times.
Data Types and Transformations: Simplify data types and transformations if possible. Although your M-code looks straightforward, any unnecessary complexity can slow down processing.
Network and Data Source:

Network Latency: Check if there is any difference in network latency between Gen 1 and Gen 2 environments. This can affect the time taken to access the Excel file from SharePoint.
Data Source Configuration: Ensure that the SharePoint data source is configured optimally in Gen 2. There could be differences in how data source connections are handled.
Service and Capacity Settings:

Service Tier: Verify that the service tier for Gen 2 dataflows provides sufficient capacity and resources. Lower tiers might have performance limitations.
Capacity and Scaling: Consider scaling up the capacity if you’re on a shared or lower-capacity environment.
Parallelism and Resource Allocation:

Resource Allocation: Gen 2 might allocate resources differently, affecting the refresh times. Investigate if there's a way to adjust resource allocation or parallelism for your dataflows.

You can solve the current problem by:

Use diagnostics tools to compare the detailed refresh steps and times between Gen 1 and Gen 2 dataflows. This can help pinpoint where the delay is occurring.

Simplify the M-code to its bare minimum and test the refresh times. Gradually add complexity to see if a specific step is causing the slowdown.

let
  Source = Excel.Workbook(Web.Contents("your_excel_file_url"), null, true),
  DanishHolidaysTable = Source{[Item = "DanishHolidays", Kind = "Table"]}[Data],
  ChangedType = Table.TransformColumnTypes(DanishHolidaysTable, {{"Danish Holidays", type date}})
in
  ChangedType

hackcrr

If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly

[slittle4782]

Hi Hackcrr - thanks much for the detailed reply. Several of your suggestions I can rule out for this scenario, but I'm wondering if you can provide any more info on a few points (#1, #6 & #9).

Possible Reasons: 

1)Performance Settings: Ensure that the performance settings for your Gen 2 dataflows are optimized. Check if there are any configurations specific to Gen 2 that might be affecting performance.

• Do you have any suggestions on how/where to configure these settings – or where I can find info on this? It’s not in the dataflow itself.  I’ve reduced the dataflow to its bare minimum and the issue still persists.  Modified M-code...

 let

  Source = Excel.Workbook(Web.Contents("https://xxx/sites/xxx/PowerBi/DMI%20Holidays.xlsx"), null, false),  Navigation = Source{[Item = "USHolidays", Kind = "Table"]}[Data]

in

 Navigation

2) Connection Throttling: Gen 2 might handle connections differently, potentially throttling them in a way that impacts performance.
- Maybe?  I can’t find any info on how to identify this.

Power Query Optimization:

3) Query Folding: Ensure that query folding is occurring, which allows operations to be performed at the source rather than locally. This can significantly speed up processing times.

• Not the problem on this one. There’s no query folding as the source is an Excel file.

4) Data Types and Transformations: Simplify data types and transformations if possible. Although your M-code looks straightforward, any unnecessary complexity can slow down processing.

• Not the problem on this one.  The M-code pulls in a single TINY table (2x10) from Excel and converts one column to a date format.  Converting a column with a handful of values to a date format should take milliseconds.
  
  

Network and Data Source:

5) Network Latency: Check if there is any difference in network latency between Gen 1 and Gen 2 environments. This can affect the time taken to access the Excel file from SharePoint.
- Not the problem on this one.  Both dataflows are on the same environment & extracting from the same Excel file.

6) Data Source Configuration: Ensure that the SharePoint data source is configured optimally in Gen 2. There could be differences in how data source connections are handled.

• Do you have any suggestions here?  It's an Excel file. I haven’t been able to find any info on this.

Service and Capacity Settings:

7) Service Tier: Verify that the service tier for Gen 2 dataflows provides sufficient capacity and resources. Lower tiers might have performance limitations.
😎 Capacity and Scaling: Consider scaling up the capacity if you’re on a shared or lower-capacity environment.

• Its an F2 license, so very small, but the file is tiny (a few kb) and the capacity isn't nearly full or being used for other things– so I don’t think that’s the issue.

Parallelism and Resource Allocation:

9) Resource Allocation: Gen 2 might allocate resources differently, affecting the refresh times. Investigate if there's a way to adjust resource allocation or parallelism for your dataflows. You can solve the current problem by:Use diagnostics tools to compare the detailed refresh steps and times between Gen 1 and Gen 2 dataflows. This can help pinpoint where the delay is occurring.
- Do you have any suggestions on diagnostic tools for this? I examined and compared the refresh history logs for the Gen1 & Gen 2 data, but there wasn't much useful data.

10) Simplify the M-code to its bare minimum and test the refresh times. Gradually add complexity to see if a specific step is causing the slowdown.
- In this scenario I'm extracting data from Excel.  I’ve reduced the dataflow to a single step – which is to extract a 2x10 table from a very small Excel file.  This takes less than a second with a Gen 1 dataflow, but 17-19 seconds with the Gen 2 dataflow.

Appreciate it!

[Anonymous]

Thank you @hackcrr and @ibarrau 

Hi, @slittle4782 

I've read your problem description and conversations with other users, and many of your configurations are already correct. You can try to optimize your Gen2 dataflow according to the methods provided by official blogs

Dataflows Gen2 data destinations and managed settings | Microsoft Fabric Blog | Microsoft Fabric

Best Regards

Jianpeng Li

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ibarrau]

Hi. Let's consider two things here.

From one hand you should consider storage that gen2 has the possibility of a destination. That might mean converting to different formats before finishing to a storage of your capacity. The gen1 stores in black box at a microsft capacity. Gen2 also have a staging feature. If you have it turned on, it is storing two times. You can read about that here: https://blog.ladataweb.com.ar/post/738525446383288320/fabric-que-es-el-stagign-de-dataflows-gen2

On the other hand it's capacity. Gen2 only runs over capacity, it depends on the overload CUs and size of the capacity if it can run in a good shape. Gen1 can run over shared capacity of microsft that let the best of it.

I hope that helps,