Forum Discussion

Scott_Powell's avatar
Scott_Powell
Icon for Advocate IV rankAdvocate IV
2 years ago

Gen2 Dataflow to Warehouse - really slow performance

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 thi...
Tips and Tricks
JP_Helsinki's avatar
JP_Helsinki
Frequent Visitor
2 years ago

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

  • davelukasaddpro's avatar
    davelukasaddpro
    Frequent Visitor
    2 years ago

    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.

    • Scott_Powell's avatar
      Scott_Powell
      Icon for Advocate IV rankAdvocate IV
      2 years ago

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

       

      Thanks,

      Scott

      • MysticSapphire's avatar
        MysticSapphire
        Frequent Visitor
        2 years ago

        I'm experiencing the same performance issues with Dataflow Gen 2 using an On-prem data gateway:

        • Pulling the data from the underlying SQL database is quick. I can see that in the refresh details (about 10 tables each taking less than 1 min to pull the data).

        • However, the second Dataflow Gen 2 step where it's writing the data (WriteToDataDestination) to the warehouse takes ages and eventually times out. It succeeds in writing about 3-4 tables but then fails.

        "104100 Couldn't refresh the entity because of an issue with the mashup document MashupException.Error: Microsoft SQL: A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)"

        Is there a way to see what jobs/requests are running on the warehouse? It seems that running too many requests (e.g., 1 Dataflow Gen 2 with many tables or running multiple Dataflows Gen 2 at the same time against the same Warehouse) results in locking up the Warehouse.
        Is there even a way to stop all current jobs?

        I'm inclined to look for other solutions, but notebooks and fast copy are still not supported for on-premise sources as of Jan 2024. Many new features will be coming for Dataflow Gen 2 in Q1 of 2024, though. As a next step, I will be trying Dataflow Gen 2 to store data into the lakehouse to get a properly robust ETL process set up and let you know if that resolved it.

  • cpshort7186's avatar
    cpshort7186
    Icon for Advocate I rankAdvocate I
    1 year ago

    I"m trying to do the same with Alteryx and still experiencing extreme performance issues.  Data output is just a few thousand rows.  Job runs in 9 sec without trying to write to Fabric Warehouse, and takes 35min plus to run loading to warehouse.  Sometimes it just fails.  

    • miguel's avatar
      miguel
      Icon for Community Admin rankCommunity Admin
      1 year ago

      If you're trying to perform this with a different tool other than Dataflows, and you've noticed that the bottleneck is loading the data to the warehouse, definitely try reaching out to our support team or post this question in the Data Engineering community who oversee any topics directly related to the data warehouse 

      • cpshort7186's avatar
        cpshort7186
        Icon for Advocate I rankAdvocate I
        1 year ago

        I don't seem to have the ability to submit support requests.  With all the AI tools available I with Microsoft would do a better job of summarizing issues and consolidating these problems across various forums... I shouldn't have to re-post issues multiple times, but I did it anyways: Poor ODBC insert performance in Warehouse - Microsoft Fabric Community