When loading data from Ingest to Prepare, each small table gets its own copy data activity in Azure Data Factory. These activities each come with of a lot of per-activity overhead that adds up to a lot of wasted time. Bundling together many smaller tables in the same copy data activity would probably save a lot of time.
We therefore suggest to support an option to bundle multiple tables into the same ADF copy data activities.
In our environment (TimeXtender SaaS + ADF + self-hosted IR + ADLS Gen2 + Azure SQL), Source → Storage account data transfer is quite fast, but Storage account → Azure SQL Server transfer in ADF is slow because of the high number of copy data activities that need to be created and started up.
For example, during a busy workload, we observed a copy of 1 row / 136 bytes from ADLS to Azure SQL taking about 27 seconds in Copy activity (around 40 seconds end-to-end), while the actual transfer time reported by ADF was only about 6 seconds. This is a typical example for small tables, not an exceptional one.
An optional grouping mode for small/medium ingests would likely reduce:
- per-activity orchestration overhead
- self-hosted IR CPU/process churn
- total elapsed ingest time
We believe this would be useful not only for Azure SQL/ADF scenarios, but more generally for environments where many small loads are executed separately against cloud platforms.
