Recommendations for faster writing to a sink in an Azure Data Factory Data Flow?

Question

Recommendations for faster writing to a sink in an Azure Data Factory Data Flow?

Sam Burns 6

What are some of the best ways to speed up writing to an Azure SQL DB sink from an Azure Data Factory Data Flow? I understand that it's important to use partitioning (the Use current partitioning option should suffice in most cases). And I've read through Microsoft's recommendations here, which basically say to either disable indexes or scale up the SQL DB.

But I'm wondering what other options are out there. For example, is it better to select Run in parallel? Should I use a staging service? What's a relatively large or a relatively small batch size (under Settings for the sink) when writing to a sink?

I haven't found many resources that address this question so it'd be awesome to get feedback and suggestions from the community here.

1 answer

Your answer

Answer 1

HimanshuSinha-msft 19,491 Microsoft Employee Moderator

Hello @Sam Burns , Thanks for the question and using MS Q&A platform.

At this time I think you can get started with the document here .

https://learn.microsoft.com/en-us/azure/data-factory/copy-activity-performance

I understand that there you have further questions, please do not hesitate to ask here.

I would also request you to please log the ask on the page, by scrolling down and click on Feedback -> This page that way we will update the document for future users .

User's image

Thanks
Himanshu

Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.

Sam Burns 6 Reputation points

2023-03-15T21:39:36.8866667+00:00

@HimanshuSinha-msft thanks for getting back to me! Unfortunately, the link you provided doesn't apply directly to my question since the info in that link is focused on copy activities in Data Factory - not Data Flows.

We are trying to improve performance of a Data Flow in our Data Factory environment. We would like to know how to more effectively use Data Flow options like batch size, parallel runs, and staging services. Can you provide some insight there?
Sam Burns 6 Reputation points

2023-03-21T18:56:10.3433333+00:00

Hi @HimanshuSinha-msft ! I wanted to ask, did you see my last message? Do you have any thoughts or resources related, specifically, to Data Flows in Azure Data Factory?
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2023-03-24T17:43:19.3233333+00:00

Hi @Sam Burns ,

My apologizes for replying late on this. The mapping data flow uses Spark under the hood and so when we do have any operations ( INSERT / UPDATE ) on the sink side (in this case Az SQL) we will have to mindful as how the sink handles multiple writes on the sink side. If you are using S0 tier, a higher batching will make the sink go slow. At this time, I do not think we have clear document for this. I could suggest you try out from smaller on batch number and look as to how the sink performs, if it looks fine you can increase the batch number. On the staging ask, are you using Schema drift ? ( I doubt as you are using SQL on the sink side where the schema is well defined ( I am assuming )

Thanks
Himanshu
Sam Burns 6 Reputation points

2023-03-24T19:29:05.4133333+00:00

@HimanshuSinha-msft Thanks for getting back to me! I'm not sure if we're using S0 tier. How can I find that out?

I will try greater batching and see if that helps.

We are not using schema drift since our schema are very static.
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2023-03-29T20:16:12.3266667+00:00

Hello @Sam Burns ,

My apoloziges for the late reply on this .

You can check that from the portal .

Thanks
Himanshu

Please accept as "Yes" if the answer provided is useful , so that you can help others in the community looking for remediation for similar issues.
Sam Burns 6 Reputation points

2023-03-29T20:44:23.0333333+00:00

Thanks. I just checked and we're using the General Purpose service tier. I don't see an option for S0. Would you recommend a different service tier besides General Purpose?

Also wondering if we could/should scale our DB up during this particular update job and then scale it back down. Any thoughts there?

Might be easier to explain all of this during a phone call. Could we schedule something?
HimanshuSinha-msft 19,491 Reputation points Microsoft Employee Moderator

2023-03-30T19:15:56.41+00:00

Hi @Sam Burns ,

I highly suggest this.
Also wondering if we could/should scale our DB up during this particular update job and then scale it back down. Any thoughts there?
Sam Burns 6 Reputation points

2023-03-30T19:37:44.66+00:00

Sounds good. Could we also set up a time for a phone call to discuss more?

Share via

Recommendations for faster writing to a sink in an Azure Data Factory Data Flow?

1 answer

Your answer