Forum Discussion

devaj123's avatar
devaj123
Frequent Visitor
1 year ago
Solved

On Premises SQL Historical table taking long time to load into Fabric Lakehouse.

Hi Team,

  I have a sql server historical table with milloins of records and table has nearly 150 columns i need to bring the entire data into lake house. I tried Dataflow Gen2, Copy job, pipeline but all of them are taking long time even tried with partationing also.

 

Could any one suggest the best approach how to load the data with good performance. currently trying using Notebook. if any one worked using notebook Please let me know how that works.

 

Thanks

Vasu

workspaces
  • v-prasare's avatar
    v-prasare
    1 year ago

    Hi devaj123,

    Using Notebooks with PySpark provides the best performance and flexibility, especially for large tables. If this is a historical table that changes over time, implement watermarking and load only new or updated rows using a timestamp or identity column.

     

    Repartition the data to spread it across multiple parallel tasks, especially helpful for performance when writing large volumes. Save the data in Delta format, which supports efficient queries, updates, and time travel versioning.

     

     

     


    Thanks,

    Prashanth Are

    MS Fabric community support


    If our resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

6 Replies

    • devaj123's avatar
      devaj123
      Frequent Visitor
      1 year ago

      Yes Mirroring also the same it has lot of limitations. didnt work out. Pipeline is bit faster than Mirroring.

       

      Thanks

      Vasu

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi devaj123,

    Using Notebooks with PySpark provides the best performance and flexibility, especially for large tables. If this is a historical table that changes over time, implement watermarking and load only new or updated rows using a timestamp or identity column.

     

    Repartition the data to spread it across multiple parallel tasks, especially helpful for performance when writing large volumes. Save the data in Delta format, which supports efficient queries, updates, and time travel versioning.

     

     

     


    Thanks,

    Prashanth Are

    MS Fabric community support


    If our resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi devaj123,

    As we haven’t heard back from you, we wanted to kindly follow up to check if there is any progress on above mentioned issue. if you got any updates i requet you to please post here and accept as solution. This will help other community members with similar issues find answers easily.

     

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi devaj123,

    As we haven’t heard back from you, we wanted to kindly follow up to check if there is any progress on above mentioned issue. let me know if you still need any further help here.

     

     

     

    Thanks,

    Prashanth Are

    MS Fabric community support

  • v-prasare's avatar
    v-prasare
    Icon for Community Support rankCommunity Support
    1 year ago

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.


    If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum we’ll be happy to assist.
    Thank you for being part of the Microsoft Fabric Community.