Azure Data Factory - ForEach loop slow performance towards end of loop

Question

I'm working with Azure Data Factory v2, using a Lookup task against an Azure SQL DB which returns a set of results with two columns from a table (table1 in this example)

In this case the lookup returns these two columns:
OrderKey - This is the unique key reference of a particular order
JSON - This is a JSON payload that has been created by a previous task in another pipeline.

In testing, I'm only using ~800 rows but in production it will likely be over 100k rows.

Both of these columns are then used in the ForEach loop.

Inside the ForEach loop we use the JSON to send to a determined web endpoint, return the result and update table1 with the response for that OrderKey.

The web request should take no more than 5-6 seconds to return which is the case in the first few loops, the update to the table should also be quick and takes 5-6 seconds as well. In reality this ADF being slow, with TSQL we can perform this action quicker but we need ADF to get the result of the web request.

The process works, and is very fast in the first few loops.
After that, it seems to take an incredibly long time to actually perform the actions inside the foreach loop; particularly towards the end of the loop when there are less than 20 records left to process. When this happens each loop takes upwards of 3 minutes so the end result is the total pipeline takes far longer than it should..

Some actions I've tried to work around or improve the performance:

• Change the integration runtime to use a higher number of cores (up to 256), this results in the same performance problem
• Change the integration runtime compute to be memory optimized instead of general purpose, same performance problem (notably there is not a lot of documentation about what this compute change actually does other than "try it and see if its faster...")
• Change the SQL DB to a higher tier, I've gone up to Business Critical v5 gen 12, this results in the same performance problem
• Change the ForEach to Sequential, this results in far worse performance as looping through more rows one by one is slower
• Change the batch size of the ForEach to 50, this results in the same performance problem
• Put the ForEach into its own pipeline and put that pipeline inside an Until, setting the lookup to only bring back the first 50 and the Until to continue until we've updated all rows. This is slower again as we're batching through 50 at a time.
• Changing the timeout of the lookup tasks performing the SQL DB table update to 10 seconds and the Query Timeout to 1 minute (as low as it will go) with retries enabled. This has proven some strange behaviour - often I'll see the the lookup doing the update task sit there for several minutes without doing anything.
• Change the isolation level on the lookup task to ReadUncommitted and using table hints WITH(UPDLOCK, READPAST, ROWLOCK), same performance problem.

Looking at the SQL DB, the CPU Core % used is not even reaching 1%, if I use an S12 instead of a BC_Gen15_12 then I can see the DTU % used is also not reaching 1%.

I'm running out of options here and have no idea why the foreach inside the pipeline is so slow when it gets towards the end. It performs incredibly well leading up to the end, but then takes a nosedive in performance.
The best performance I've had so far is 800 records being updated in about 10 minutes, I'd expect that to be significantly quicker - more like 2-3 minutes.
Based on tests I'm pretty sure the bottleneck is ADF, not the SQL DB or the Integration Runtime.

What else can I try to improve performance here?

Some screenshots to help anyone who has any thoughts. Note that for further testing I removed the web request, the same problem still exists even if we're just trying to update the SQL DB table without the web request.

Answer 1

Hi @CW ,
Welcome to Microsoft Q&A platform and thanks for posting your query here.
Summarizing your query here - It looks like you are facing performance issue in your ADF workflow. You tried various things to figure it out like , upgrading IR and SQL DB to higher available versions , trying different pipeline structure to make it faster. But still you didn't see any improvement in the performance.

As I went through the details, it seemed that the issue is not with IR or SQL DB versions. You are trying to run multiple DML queries- in this case Update queries at the same time as the lookup which is trying to update the table is within foreach. With the increase in the number of concurrent queries running in the database , it might have blocked the resource class and in turn one query is blocking other queries. Below is the image showing max concurrent queries allowed based on service tier.

The approach I would like to suggest here is instead of lookup activity which you are using to update the table for each item at the same time , it's better to use

• copy activity to copy the output content of Web request (the one which you are trying to update in the table) into a dummy/stage table. Keep it a truncate and reload table for every transaction. It will bring the required data all at once in your database.
• Then, use stored procedure activity or Script activity to join this dummy table with your original table to update the needed fields based on the OrderKey.

This will replace the massive parallel update queries with a one-time update and will enhance the performance.

Hope this will help. Please let us know if any further queries.

------------------------------

• Please don't forget to click on or upvote button whenever the information provided helps you.
  Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators