Forum Discussion

slittle4782's avatar
slittle4782
Advocate I
2 years ago

Gen 2 dataflows extremely slow vs. Gen 1 dataflow

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 Fabr...
Dataflow
Need Help
hackcrr's avatar
hackcrr
Memorable Member
2 years ago

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's avatar
slittle4782
Advocate I
2 years ago

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!