topic Re: Gen2 Dataflow to Warehouse - really slow performance in Dataflow

Gen2 Dataflow to Warehouse - really slow performance

Scott_Powell — Tue, 17 Oct 2023 19:55:29 GMT

Hi, I'm hitting an issue with loading data from on-prem SQL server to a Fabric Warehouse using dataflows gen2. I'm only moving 34k rows (about 30 columns), and it's taking almost 13 minutes to do this (see image at bottom). Staging is disabled for this (these jobs literally just copy the data as is, with no transformations).

If I enable staging, I can see the staging objects load in < 30 seconds, and then it takes 12+ minutes to write from the staging warehouse to the actual real warehouse.
If I write the records to a Lakehouse instead of a Warehouse, the whole thing always completes in 30 - 45 seconds.

Is this type of performance typical for loading a Warehouse??? Can't believe it is, but the jobs are too simple to have anything messed up I think. Any ideas?

Thanks,

Scott

Re: Gen2 Dataflow to Warehouse - really slow performance

miguel — Wed, 18 Oct 2023 15:44:10 GMT

Hi Scott,

It's difficult to tell what could be the reason for this performance. It could be the gateway or the computer running the gateway. The data source having some bottlenecks or perhaps something on the Fabric service end (perhaps your capacity or something different).

The best way to troubleshoot this would be to raise a support ticket and have an engineer take a closer look at this performance.

https://support.fabric.microsoft.com/support

Re: Gen2 Dataflow to Warehouse - really slow performance

Scott_Powell — Wed, 18 Oct 2023 15:57:55 GMT

Will do - thanks miguel . Just so odd that this takes seconds (which is what I expect) in a Lakehouse but then drags out so long in a warehouse. I'll get an easily reproducible case ready, will update this thread if I'm able to narrow down the issue with support.

Thanks!

Scott

Re: Gen2 Dataflow to Warehouse - really slow performance

coverturfLSH — Wed, 18 Oct 2023 17:53:50 GMT

I'm experiencing the exact same thing as you. My DFG2 takes a long time to write to the warehouse, but only takes a fraction of the time to write to the lakehouse. Hopefully support has a solution for you, but I modified my process to use a Pipeline instead:

Refresh the dataflow (destination: lakehouse)
SQL Script to delete the data from the warehouse (beneficial for incremental data loads)
Copy data from the lakehouse to the warehouse.

This process is much quicker than when I was trying to write directly to the warehouse.

Re: Gen2 Dataflow to Warehouse - really slow performance

Scott_Powell — Mon, 23 Oct 2023 19:33:44 GMT

I've spoken with support and sent them the relevant info, I'll update this post as soon as we figure out what's going on.

Thanks!

Scott

Re: Gen2 Dataflow to Warehouse - really slow performance

Joshrodgers123 — Thu, 26 Oct 2023 01:53:26 GMT

I'm seeing the same thing as well. I'd really love to start using warehouses but can't seem to get anything but really slow performance or errors.

I have a DFG2 writing 2 tables 300k and 400k records each.

With no destination: 50 seconds

With warehouse destination, staging enabled: 50 minutes

With warehouse destination, staging disabled, times out after 3.5 hour and locks up the warehouse from being accessed.

Re: Gen2 Dataflow to Warehouse - really slow performance

DataPne — Wed, 15 Nov 2023 14:53:31 GMT

Hi Scott,

Did support manage to assist you at all? Im having a call with them tomorrow for the same issue.

Thanks a lot!
Pne

Re: Gen2 Dataflow to Warehouse - really slow performance

Joshrodgers123 — Tue, 21 Nov 2023 00:42:17 GMT

Hey Miguel,

I'm experiencing the same issues as Scott - expect that 9/10 times our dataflows are failing. Support has not been able to help. The only suggestion they had so far was to downgrade to a gateway from August..

We are looking to purchase a fabric capacity, but most of our data is on-prem. We've been trying for months to get things up and running with DFG2, but are running into error after error.

Any thoughts on what our next steps should be?

Re: Gen2 Dataflow to Warehouse - really slow performance

Scott_Powell — Tue, 21 Nov 2023 02:38:12 GMT

Hi all and especially miguel , just a quick update. First, having a really hard time getting support to actually pay any attention to this - even though I submitted a ticket over a month ago. Bit frustrated as I don't think they've actually done anything at all on the ticket.

Having said that, I think I've discovered at least one of the causes of really poor performance. I was trying yesterday to load about 11 million rows into a Warehouse. The load job takes 5 minutes writing to a Lakehouse, but timed out after hours on the warehouse.

Looking in to the new "query insights" schema available in the Warehouse, I could see the records were being written in batches of only 1,000 records each. And each commit seems to have been taking 3 or 4 seconds. With 11 million records, that would be 11,000 commits * 4 seconds each = 12+ hours of time.

If this is indeed the issue - MS you've GOT to change the commit size. Maybe make it 1 million, or 10 million, but not 1000 rows.

p.s. still not sure this is "all" of the issue - on my original issue above it was only writing 34,000 records which would have been 34 commits at 4 seconds each = several minutes of excess time. I was seeing way more than that. I'll try to go back and reproduce this tomorrow, looking at the query logs to see what's going on in the database.

p.p.s. Microsoft if it helps, the ticket I opened was 2310180040012820

Thanks,

Scott

Re: Gen2 Dataflow to Warehouse - really slow performance

Joshrodgers123 — Tue, 21 Nov 2023 04:00:23 GMT

I am in the same boat as you. I think this all comes down to the write speed to the warehouse. Things just seem to lock up and time out. I can't even get 100k rows to write...

I watched the query against our on-prem sql server and noticed that it had a ASYNC_NETWORK_IO wait the whole time. According to MSFT docs, these are due to the client application (the warehouse) not accepting results fast enough.

I also noticed that the warehouse becomes unresponsive while the dataflow is running.

I can't imagine we are the only ones who are experiencing these issues. There must be some solution or acknoledgment if Fabric is now GA. I'm trying to sell Fabric to my org, but it's a bit hard when we can't get our data into it.

Here's my support ticket if it helps: 2311060040014961

Re: Gen2 Dataflow to Warehouse - really slow performance

DataPne — Tue, 21 Nov 2023 13:07:02 GMT

Same boat here.

Cannot get the dataflows to write into the warehouse for the life of me.

Support ticket is this: 2311150050004676

Re: Gen2 Dataflow to Warehouse - really slow performance

v-cboorla-msft — Fri, 01 Dec 2023 18:32:08 GMT

Hi Scott_Powell

Apologise for the delay in response and for the inconvenience that you are facing here.

Just as a work around, instead of loading on prem data to Dataflow Gen2 and then to warehouse (On-Prem -> DFg2 -> Warehouse) can you please try to test by loading the data from on-prem to Dataflow Gen2 and then to Lakehouse.(On-Prem -> DFg2 -> Lakehouse) (files or tables, would test both)
Then create a Data Pipeline to move data from Lakehouse to Warehouse.

Just like the medallion architecture we see often, we want to do a quick load of the source into a Raw layer, then work our way to the Warehouse which would be Silver/Gold.

The issue which you are facing while loading the data into warehouse is just temporary. This won't be a thing once Data Pipelines can use On Prem Data Gateway which is in development.

Hope this helps. Please let me know if you have any further questions.

Re: Gen2 Dataflow to Warehouse - really slow performance

Scott_Powell — Fri, 01 Dec 2023 18:39:12 GMT

hi v-cboorla-msft , I'm actually in the process of getting this ready to test right now. I was able to successfully load about 1 billion rows of transactions into a lakehouse using Gen2 dataflows (running multiple dataflows in parallel across several gateway clusters). Now I'll try copying those across to a DWH using the copy utility. I'll update this post as soon as I have results to report (this is a side project so might be a couple of days).

One question / idea - if really feels to my like the underlying Dataflow Gen2 "engine" should be able to tap into the spark compute just like notebooks do. Similar to how it "pushes down" SQL queries to a database. Wish it could "push down" some of this type of work to spark - seems like it would be much faster.

Thanks!

Scott

Re: Gen2 Dataflow to Warehouse - really slow performance

v-cboorla-msft — Fri, 01 Dec 2023 19:00:13 GMT

Hi Scott_Powell

Thanks for the feedback.

If you want to enable spark compute please upvote this idea: Microsoft Idea

Thanks

Re: Gen2 Dataflow to Warehouse - really slow performance

davelukasaddpro — Wed, 27 Dec 2023 12:16:19 GMT

I'm having the exact same expirience. But I fetch my data from an api endpoint. The timeout is set to a few seconds. If the endpoint fails. Well, then the next dataflow is executed. My pipeline consists of 21 dataflows. Each dataflow fetches minimal amounts of data from an endpoint, we are talking 2-3kb of json each run. There is just something utterly wrong, if the monitor shows some dataflows run 1minute. But some run 4hours... and they seem to eat all my F2 capacity. As a consequence every other dataflow executed after that one flow that executes 4h, fails.... I mean, Dataflow Gen2 are really powerful and I see them as the main selling point for companies looking for great visual ETL. So, Microsoft, please put some serious attention to these dataflow issues.

Re: Gen2 Dataflow to Warehouse - really slow performance

Joshrodgers123 — Wed, 27 Dec 2023 20:37:18 GMT

Same thing on our end. I can't even get tables with a few thousand rows to write to the warehouse. I've had a support ticket open for 2 months now and they have been stringing me along.

I wish MSFT would acknowledge these issues. I have high hopes for DFG2 and Fabric as a whole, but it seems like things just don't work now.

Re: Gen2 Dataflow to Warehouse - really slow performance

davelukasaddpro — Fri, 29 Dec 2023 12:29:48 GMT

After the christmas/new year holidays I have plan to try something else. Currently the data is stored to a Datawarehouse for practical "SQL" reasons. The json is basically structured as a table row and easy to insert into a table. But from what I'm hearing and reading the Lakehouse seems to work better with Gen2. It's a minor modification to get the dataflows write the fetched endpoint data to a Lakehouse instead of the current warehouse. Also, I changed the scheduling for the main pipeline to 1.5hours from 50minutes. I counted approx 2-3minutes seems to go on "startup" of the dataflow, the rest is the execution of the dataflow, approx 2minutes. So any dataflow should execute approx within 4-5min. Now my pipeline with it's 21 dataflows is stable. But 90minutes to get a few kb of data is way too slow.

Re: Gen2 Dataflow to Warehouse - really slow performance

JP_Helsinki — Mon, 22 Jan 2024 12:20:02 GMT

Any update on this issue? Im trying to load data with Knime ETL tool to the Fabric warehouse but it is incredebly slow..

Re: Gen2 Dataflow to Warehouse - really slow performance

davelukasaddpro — Mon, 22 Jan 2024 12:56:04 GMT

I basically gave up on dataflows gen2 because there was a speed requirement it couldn't satisfy. However doing the ETL in Pyspark and storing the data to the lake works really good and is fast enough. But you need to include 1-2minutes for the spark pool to find you a session. Besides you have an extreme library of python packages allowing you to basically do anything. BTW. I'm not a true Python coder, but Bing Copilot and chatgpt were enough to get me started.

Re: Gen2 Dataflow to Warehouse - really slow performance

Scott_Powell — Mon, 22 Jan 2024 23:34:28 GMT

hi davelukasaddpro , how do you get the PySpark Notebook to hit on-prem data sources?

Thanks,

Scott