CODEWITHSUNDEEP
azure-data-factory
Patterson
Patterson

Reputation: 2757

How to increase performance of Azure Data Factory Pipeline with Integration Runtime

I would like to increated the performance of our pipelines.

The pipelines currently run from an integration runtime.

I am running a single copy activity on tables held on our Source which is a SQL Database. Tables contain just under a million rows, with about 15 columns.

Currently the time it takes to copy a table from Source to Sink(ADLS) is approximately 20mins.

Is there a way to increase the DIU to increase performance?

My current copy settings are as follows:

enter image description here

enter image description here

I'm thinking that if I made some changes to Settings, see below, I would improve performance, but I have never played around to settings before, any suggestions most welcomed.

enter image description here

The activity details for a pipeline run is as follows:

enter image description here

My link service is an Azure Synapse Link service, see below:

enter image description here

Upvotes: 0

Views: 1713

Answers (2)

Himanshu Kumar Sinha
Himanshu Kumar Sinha

Reputation: 1776

Quick check , is the Azure SQL and storage account in the same region ? Also I see that your copy activity is set as parraleism as 1 , you can play with number and see if that helps .

How to setyp parallelism please read here : https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance-features#parallel-copy

Please see the snaphot below

enter image description here enter image description here

Upvotes: 0

Chen Hirsh
Chen Hirsh

Reputation: 1390

From the output window, we can see that almost all the wait time was "Time to first byte", which means your SQL server is slow to reply. It takes ~22 minutes for less than 90K rows. So changes on the ADF side will not help. If your query is a simple "select * from table", then maybe your SQL server is low on resources. You can check that in your database portal in Azure. Try to add more resources and see if copy times improve. If this is a query from a view or other complicated query, maybe it needs some improvement (indexes, improve code). You can test that by writing the query result to a table in your SQL database, use that table as the data factory source, and see if this improves copy time.

Upvotes: 2

Related Questions