Azure Synapse Analytics
An Azure analytics service that brings together data integration, enterprise data warehousing, and big data analytics. Previously known as Azure SQL Data Warehouse.
2,868 questions
Azure Synapse: Copy data between ADLS & Aszure SQL DB very slow
Vivek D
81
Reputation points
I was trying a very basic test of loading a pipe delimited flat file with about 20K rows into a Azure SQL table but it took close to 15 mins to load it. I expected it to load in about 15 secs. Why would it take so long in Synapse/ADF?
{count} votes
-
Vivek D 81 Reputation points
2020-12-23T00:00:06.59+00:00 Looks like I had enabled "Skip Incompatible Rows" option while testing it out as the load was failing for some reason (perhaps some bad data in the file) and that was causing it to become that slow. After I disabled that and also just reduced the rows down to about 200, the pipeline ran in about 12 secs.
Should that setting have such a major difference in performance?
Hello @Vivek D , welcome to Microsoft Q&A , and thank you for that insight.
You said the time went from 15 minutes to 12 seconds. Was that after turning off "Skip Incompatible Rows" or after reducing rows from 20k to 200, or after both?
There are several possible things going on. Smaller data would mean smaller time, true. I also would expect faster than 15 min for 20k rows (unless very very wide rows).
1 Failed row takes longer than 1 Successful row. If all those rows were failing, then 15 minutes does make sense.
It was both i.e. turn off skip incompatible rows and also reduced the rows from 20k to 200 as I just wanted to somehow get a fully working copy pipeline.
When it failed with the skip incompatible rows turned on, it did manage to load some rows.
The same file loaded fine in my local DB using import so not sure why it was failing in Azure.
Anyway, the main thing I wanted to understand is whether the skip incompatible rows option adds a significant overhead to the processing of the file or if the slowness was just due to potentially high number of failed rows only?
@Vivek D I am reaching out internally to get an opinion on how much impact the logging of rows ought to have.
Sign in to comment