Copy Activity is very slow

[christianunr]

I have a data pipeline that has a Copy Activity action. The data that is being pulled from our on-prem SQL Server is tiny. The biggest table is about 1,800. However, despite the small size, Copy Activity is taking a long time to finish and the throughput is abysmal.

For an example, below is screenshot of 1.446 MB being read at 1.621 KB/s and it's still running over 15 minutes:

Within the settings of the Copy Activity, I have set the Intelligent throughput optimization to different options with all performing the same. Setting the Intelligent throughput optimization to Auto, Balanced, or Maximum does not make difference. The screenshot above is set to Maximum. I have another pipeline with a Copy Activity action that takes less than a minute with way more data.

It also seems like the Copy Activity I am having an issue with takes a long time to even start reading data. 15 minutes to copy over ~1,800 is pretty bad. I don't understand what is happening.

EDIT: it seems like if the tables do not exist in the destination (a Lakehouse in this case), then the data loads very quickly (less than 30 seconds) and each load after loads in quickly. Only when the table exists does it take several minutes to load.

[v-pbandela-msft]

Hi @christianunr,

Thank you for reaching out in Microsoft Community Forum.

Please follow below steps to resolve the error;

1. Please Optimize the Delta Table
OPTIMIZE delta.`/lakehouse_path/table_name`

2. Please Run VACUUM periodically to clean up old files and Use Append instead of Upsert if updates aren’t needed.

3. Try running the pipeline during off-peak hours or check SKU limitations.

4. Please make sure the Data Pipeline is running on a suitable Fabric SKU (higher tiers have better performance).

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.

[christianunr]

Hi @v-pbandela-msft - for option #1, is the OPTIMIZE a SQL command?

[v-pbandela-msft · Accepted solution]

Hi @christianunr,

Thank you for reaching out in Microsoft Community Forum.

Yes, OPTIMIZE is a SQL command designed for Delta tables in Fabric Lakehouse. It helps improve query and write performance by compacting small files into larger ones, reducing metadata overhead
.

You can run it in a SQL notebook or a SQL query execution environment within Fabric:
OPTIMIZE delta.`/lakehouse_path/table_name`

Please continue using Microsoft community forum.

If you found this post helpful, please consider marking it as "Accept as Solution" and give it a 'Kudos'. if it was helpful. help other members find it more easily.

Regards,
Pavan.