Forum Discussion
Data read from On-prem SQL server to Fabric Satawarehouse is slower through Dataflow GEN2
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
8 Replies
- Element115
Memorable Member
jlakshminaraya To get more traction for your idea, also post it here please: Fabric suggestions and ideas
- Element115
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.
I have one dataflow only. Which is working firn for me
- Element115
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?
pqian_MSFTMicrosoft Employee
jlakshminaraya - can you post the request ID for the 17mins/11 table DFG2 refresh. You can obtain it from the refresh history page.
v-cboorla-msftWe 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 .
Otherwise, will respond back with the more details and we will try to help.
Thanks.- v-cboorla-msft
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.
