Forum Discussion

sjrrkb123's avatar
sjrrkb123
Helper III
2 years ago
Solved

Combining Multiple Parquet files into a table in Dataflows Very Slow

I have two dataflows pointing to the same set of data. Dataflow A is on SQL server (on prem) and dataflow B is in multiple .parquet files that need to be combined into a single table.

 

When I run the dataflow A, it takes about 1minute and 30seconds to do a select * from the table. Below is a screenshot of the refresh time

 

 

When I run dataflow B, it takes 9-10minutes to get the same amount of data. Below is the screenshot of the power query:

    Below is the refresh time (the top is using [HierarchicalNavigation=true] and the bottom query is without it)

 

A couple of things:

  1. The parquet files are stored in an ALDS gen 2 datalake
  2. They are the output of processing from a spark notebook in Azure Synapse
  3. The sql server table has 3.3million rows
  4. This is using an On Prem gateway to manage the datasources 

 

Why is my ALDS gen 2 load so much slower than on prem?

  • Turns out our best solution will be to spin up a vm, install a gateway on it, and use it for the dataflow refresh from alds gen2 datalakes.

3 Replies