topic Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2 in Dataflow

Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

jlakshminaraya — Fri, 01 Mar 2024 16:05:39 GMT

Hello -

I noticed that, data read from On-prem SQL server to Fabric Datawarehouse is slower than SSIS approach.

Here are more details for better understanding about the issue:

With SSIS approach:

Source : SQL server database

Destination : SQL server

Count of tables : 11

Volume of all 11 tables is around 200,000

ETL : SSIS dataflows (1 dataflow for each table)

Total time taken to read and load data in on-premise for all 11 tables is 50 seconds

With Fabric DF GEN2 approach :

Source : SQL server database

Destination : Synapse Warehouse

Count of tables : 11

Volume of all 11 tables is around 200,000

Pipeline : DF GEN2 (11 tables under single DF GEN2)

Total time taken to read and load data in Synapse Warehouse is 17 minutes.

Looking forward for a solution to improve performance of DF GEN2.

Thanks,

Lakshmi

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

Element115 — Fri, 01 Mar 2024 20:39:44 GMT

jlakshminaraya To get more traction for your idea, also post it here please: Fabric suggestions and ideas

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

Element115 — Fri, 01 Mar 2024 20:43:49 GMT

jlakshminaraya Also, I got a question. Do you use one or two chained dataflows to do your ingestion from the on-prem DB to the synapse warehouse?

I ask because I am unable to ingest anything from an on-prem DB via the Power BI data gateway to a Fabric lakehouse or warehouse if I only use one dataflow. I have to use the workaround Microsoft recommends. This workaround consist of using 2 dataflows. The first one connects to the on-prem DB and ingest the data. And the second dataflow uses the first dataflow as a source and loads the data to a Fabric destination.

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

jlakshminaraya — Mon, 04 Mar 2024 12:36:49 GMT

I have one dataflow only. Which is working firn for me

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

Element115 — Mon, 04 Mar 2024 15:16:36 GMT

That's amazing! Why do I need to use 2 DFs??? I don't get it. Did you also set your firewall rules to this or do you have different endpoints?

Protocol: TCP
Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com
Port: 1433

That's straight from the documentation... but if the docs are not in sync with some new changes, then that might explmain perhaps I am missing an endpoint?

source: On-premises data gateway considerations for data destinations in Dataflow Gen2 - Microsoft Fabric | Microsoft Learn

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

pqian_MSFT — Sat, 09 Mar 2024 22:02:26 GMT

jlakshminaraya - can you post the request ID for the 17mins/11 table DFG2 refresh. You can obtain it from the refresh history page.

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

v-cboorla-msft — Tue, 12 Mar 2024 13:23:34 GMT

Hi jlakshminaraya

Thanks.

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

Element115 — Tue, 12 Mar 2024 16:14:33 GMT

jlakshminaraya Could you please let me know what your endpoints are in your firewall rules?

This?

Endpoints: *.datawarehouse.pbidedicated.windows.net, *.datawarehouse.fabric.microsoft.com, *.dfs.fabric.microsoft.com

Or something else? like an additional endpoint perhaps that's not listed in the docs?

Re: Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2

v-cboorla-msft — Wed, 13 Mar 2024 11:19:36 GMT

Hi jlakshminaraya

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others.
If you have any question relating to the current thread, please do let us know and we will try out best to help you.
In case if you have any other question on a different issue, we request you to open a new thread.

Thanks.