Gen 2 dataflows extremely slow vs. Gen 1 dataflow

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,

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

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