Data flow gen 1 extremely slow

[jochendecraene]

Hi,

Does anyone else also experience that dataflow genv 1 is extremely slow and therefore unworkable. Problems started last week.

At this moment I have been waiting for 15 minutes to read data from a sharepoint folder. Until last week everything went very smoothly.

[v-veshwara-msft · Accepted solution]

Hi @jochendecraene ,

We just wanted to check in again regarding your issue. If you've found a solution, marking the reply as the solution and leaving a kudos would be greatly appreciated, it helps the community and others with similar questions.

Additionally, we'd like to share that the known issue related to the slowness of connecting to SharePoint folders in Dataflow Gen1 and Gen2 has been fixed.

If you're still facing challenges or have further questions, please let us know.

Thank you.

[v-veshwara-msft]

Hi @jochendecraene ,
We haven't heard from you since last response and just wanted to check whether the solutions provided met your needs. If yes, please accept as solution to help others benefit. If not, please reach out.
Thanks.

[v-veshwara-msft]

Hi @jochendecraene ,
Thank you for reaching out. The issue you're facing with Dataflow Gen 1's performance is a known issue, and Microsoft is actively working on a resolution.
Meanwhile please try the Workarounds and troubleshooting steps provided by @Jai-Rathinavel , @rohit1991  and @Akash_Varuna as they are useful in these kind of scenarios.

Additionally, you can try the following:

1. Filter data at the source: Use filters in the SharePoint folder connector to load only relevant files or data.
2. Reduce file size: Ensure files in the folder are optimized by removing unnecessary columns and reducing data volume.
3. Minimize query transformations: Perform lightweight transformations to reduce processing complexity in the dataflow.
4. Batch or parallel processing: If possible, divide data into smaller subsets for parallel processing.
   
   
   

This resource provides best practices and strategies to enhance the efficiency of your dataflows
dataflows-best-practices 

We encourage you to consider raising a support ticket with Microsoft to highlight the urgency of the issue and to ensure it receives appropriate attention and tracking.
How to raise a support ticket 

We appreciate your patience while this issue is resolved.

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

Best regards,
Vinay Kumar.

[Akash_Varuna]

If you'r data is large consider limiting the data and pulling only the necessary data try incrimental refresh in power bi instead of pulling data each time 

[Jai-Rathinavel]

@jochendecraene  If you have any reference queries try Table.Buffer() function to significantly increase your dataflow performance. This function loads an entire table into memory, isolating it from external changes during evaluation. By buffering a table, you can prevent multiple executions of expensive operations and improve performance, especially when working with small lookup tables used multiple times. Use the Buffer function at the last step  and Give it a try !

Sample:

let
    Source = Sql.Database("SomeSQLServer", "MyDb"),
    MyTable = Source{[Item="MyTable"]}[Data],
    BufferedTable = Table.Buffer(MyTable) //Buffered the table at the last step
in
    BufferedTable

Thanks,

Jai

[rohit1991]

hi @jochendecraene  seems you're facing performance issues with Dataflow Gen 1. Here are a few steps to troubleshoot and improve performance:

1. Check SharePoint Performance: Test SharePoint folder access speed independently to ensure it's not a SharePoint issue.
2. Network Latency: Ensure a stable and fast connection, as network delays can slow dataflows.
3. Data Volume: If the folder contains many files or large files, consider optimizing your query to limit data being processed.
4. Diagnostics: Enable Power Query diagnostics to monitor query performance.
5. Switch to Gen 2 (if available): Dataflow Gen 2 offers better performance and scalability compared to Gen 1.

If the issue persists, contact Microsoft Support as it may be related to platform-level changes.

[CHJ]

Doesn't Gen 2 only work on Fabric?  I need to stay on Gen 1 as we can't move to Fabric at the moment - but I can't see the Gen 1 version anywhere to be able to revert if needed