Azure Data Factory Until loop slow to end/terminate

Geezer 26

We have an until loop in a ADFv2 pipeline.

The time it takes to stop/terminate once the expression condition is met seems to corrolate between the length of time the until loop takes to completes its activities.

This particular until loop performs alot of activites and can take anywhere between 90-120 mins to complete. So it takes almost as long to end/terminate (break out of the loop).

If I "hack" it so that it only performs a handful of activities it will quickly end and break once it's finished and the expression to terminate is met.

It's like a spinning wheel that keeps spinning even after the power is turned off. The momentum that was built up while connected takes a while to slow down and eventually stop.

Is this a known issue, how can I troubleshoot the exact cause here or fix it?

ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2022-10-11T13:55:44.123+00:00

Hi @Geezer ,

Thank you for posting query in Microsoft Q&A Platform.

Looks strange, Are we sure when until condition is becoming true there is not activity under in progress or queue?

I am suspecting there may be some internal activity of loop still running or yet to complete, hence its taking time to break the loop after condition meet. Kindly recheck and confirm, if possible, with screenshots and details. So that it will be more understandable to get idea about what exact causing this. Thank you.
Geezer 26 Reputation points

2022-10-12T09:43:46.607+00:00

Hi @ShaikMaheer-MSFT

There is no activity in a queue or in progress state.

These are screenshots from today.

This shows the pipeline in which the UNTIL loop resides in.
Started at 2022.11.10 11.01.56pm last night.

This is the UNTIL loop.

The expression is

Expanded out

The Action variable is set by the Determine Action activity.

Here’s a screenshot of the most recent rows from the Activity run log. There are typically 4k plus rows here.

The Determine Action step at 07.06am set the Action variable to “Quit”.

The last step shown within the UNTIL activity was executed at 07.07am this morning. It is now 09.40am. The UNTIL has still not broken out.
ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2022-10-12T10:17:00.07+00:00

Hi @Geezer , Thank you for sharing details with screenshots. Everything looks good to me. Only one thing I am suspecting is Stored procedure activity. Its inside loop and not connected with any other activities. That means it runs in parallel for every iteration. Can we try to add that Stored procedure activity with green line and then see how it behaves? Since its not connected with green lines I suspect that activity may be running in background. I may be wrong too. But to make sure we can try to connect that activity also with green line and see the execution.
Geezer 26 Reputation points

2022-10-12T11:44:10.027+00:00

Hi @ShaikMaheer-MSFT

That stored procedure activity is on it's own as it's set to Poll/Retry every 30 seconds, independant of what else is going on in the until loop. Adding a green line between the Take Action switch and the stored procedure activity will negate the polling/retry property. That isn't an option.

Since I took the previous snapshots the until loop has finished.
There is a gap of over 3 hours (from 07.07.52am to 10.16.34am) between the final activity in the until loop finishing and the start of the first activity outside of it.

Why did it take over 3 hours?

Like I mentioned in the initial post, if this same until loop only executed a handful of activies instead of the 4k plus, it breaks quickly.
ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2022-10-12T11:52:00.57+00:00

Hi @Geezer , Looks very strange. let me check with some internal team if they have any insights on this. Also, you can consider having a support case too for deeper investigation and understand what is happening exactly here.
Geezer 26 Reputation points

2022-10-13T14:50:31.133+00:00

Hi @ShaikMaheer-MSFT

Have you heard back from the internal team at all?
ShaikMaheer-MSFT 38,456 Reputation points Microsoft Employee

2022-10-14T04:47:31.19+00:00

Hi @Geezer , I tried to reach some internal teams if any insights on this. Not heard back anything yet. I will be updating thread once I get some insights. You can also consider raising support case too for deeper investigation and resolution on this. Below is the link with steps have support case. Thank you.
https://learn.microsoft.com/en-us/azure/azure-portal/supportability/how-to-create-azure-support-request
Ravindra Chippada 1 Reputation point

2022-11-16T13:13:13.887+00:00

@ShaikMaheer-MSFT
We're facing similar issue. Until loop taking extra time to terminate even after condition met. Our activities inside Until are Web activity which polls for a status, then it'll update a variable called 'status'. Then there is a if condition to validate status variable, if its 'failed' or 'completed' nothing happens else there is a wait time of 30 secs.
I could see variable 'status' is getting updated to 'Completed' and eventually the condition defined in Until is met, but still its running for 2-3 mins more.
Florent Pousserot 6 Reputation points

2022-12-23T15:06:07.98+00:00

i open this case : 2212230050000981

the kudo query

ADFActivityRun
| where PipelineRunId == 'xxxxxxxxxxx'
| where _ResourceId == "/subscriptions/xxxxx/resourcegroups/rxxxx/providers/microsoft.datafactory/factories/xxxxxx"
| where Status == 'Succeeded'
| sort by TimeGenerated desc
| project TimeGenerated, Start, End, TimeTotal_sec = datetime_diff('Second', End, Start), Delay_sec = datetime_diff('Second', prev(End), End ), OperationName, Status
| extend rn=row_number()
| limit 11
Henk Standhardt 1 Reputation point

2023-01-02T13:59:40.603+00:00

We experience the same behavior. Should we file a support ticket?
Viguro 1 Reputation point

2023-01-12T05:03:49.3066667+00:00

@Ravindra Chippada We are also facing the same issue :

A web activity polls for a status, then update a variable. If this variable is failed or completed nothing happens, else it wait for 10s.

Will the last activity if the Until loop ends at 10:20:29, the until loop ends 20 minutes later. The total execution time of our pipeline is 2h17.
Bäcker 21 Reputation points

2024-09-20T15:22:40.4366667+00:00

Same issue, create an until loop, set a variable to exit. Works fine.

Now add a Wait inside it, it will never exit.

How can this still be a bug? Any fix for it?
Wouter Vermeulen 6 Reputation points

2024-09-23T13:33:52.9766667+00:00

A workaround that we implemented was to call the ADF API to cancel a pipeline when the condition is met to stop the loop. API info: https://learn.microsoft.com/en-us/rest/api/datafactory/pipeline-runs/cancel?view=rest-datafactory-2018-06-01&tabs=HTTP. Know that cancel triggers the "on fail" route, so if you have a parent pipeline, make sure to use the "on completion" route to next activities.
Bäcker 21 Reputation points

2024-09-26T07:36:00.2166667+00:00

Our workaround is to use a forach loop with an array [0,0,0,,0,0,.....] that is really long,

do the things you want inside the if statement and use a wait . so if you actually waiting for something it can run for a long time. and if/else we do nothing, it just needs to loop thru the rest of the array.

Maybe Microsoft can fix their bug so we don't need workarounds years after?

6 answers

Wouter Vermeulen 6 Reputation points

2023-02-09T21:38:21.12+00:00

Similar behavior in my project. The activities in an until job took a total of around 15 minutes to reach a state where the until condition was met, then it took another 10-15 minutes after that for the until loop to break and return success.
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Ali Soleyman 5 Reputation points

2023-07-21T21:52:03.79+00:00

I have same issue with Until loop in my project. The activities in an until job took a random time between 5 to 20 minutes to stop while there is no process available after the until condition was met, unfortunately I can't use Foreach loop too as it has another issue when an iteration failed will not stop and continue to process next iteration when I set sequentially runs. This is completely blocked my project. And it's seems nobody cares in Microsoft around this major issue!!!! Can someone from Microsoft help me how can I resolve my issue?
Please sign in to rate this answer.

1 person found this answer helpful.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
dvGlenn 0 Reputation points

2023-02-03T13:39:43.2233333+00:00

This is a VERY real issue and needs some attention from the product team ASAP. It is threatening DataFactory as an enterprise tool .

I work with about 6 organizations using Data Factory to load AzureSQL/Managed Instance. All of my clients are observing similar behavior since about Q3 or 2022. MSFT has not been responsive to the concerns. Managed Instance clients seem to be the most impacted. Managed VNETs may be the culprit here.

In addition to UNTIL control structures, we are experiencing the issue with a FOR loop that also runs for a bit and includes stored procedure execution. I think the actual culprit is the VNET and SQL connector not bubbling "end-of-batch" events up the stack. I currently suspect that the bug is in the SQL connector/stored procedure activity and having several of those active in a loop takes a while to resolve all the statuses.

Data Factory performance is erratic and gradually getting slower. I assume there are scalability issues or other limits inherent in the platform design that are now showing up as adoption improves and utilization % increases on the supporting infrastructure.

Other significant performance concerns have emerged in the past few months as well:

We have recently observed (and documented with MSFT support tickets), where the same SP runs in 1-2 min using a direct connection to the DB through SSMS/data studio however runs for hours via DataFactory (likely the same issue).

I have been and continue to be a believer in the Data Factory tool. It's architecture is (from outward appearance) ideal for the Azure environment. It needs to be supported and work correctly. It is a CRITICAL part of the Azure stack.
Please sign in to rate this answer.
Alan Artenstein 0 Reputation points

2023-05-02T21:24:43.28+00:00

Echoing this. "This is a very real issue". Breaking out of loops does take a long time after the internal activities are completed. ADF looping is definitely slow in all regards. I believe there is a lot of non-cleanup happening here as well (cache-clearing/memory-freeing routines).
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Christo Greeff 6 Reputation points

2023-08-02T08:28:10.86+00:00

We're seeing similar behavior. The UNTIL activity runs for 20-25 minutes and then we're seeing a delay of 15 mins before the next activity continues. 15min gap between last activity inside the UNTIL, and the start of the IFCONDITION activity after OnSuccess.

What are the cost implications here? Do we need to escalate this @ShaikMaheer-MSFT ?
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Viguro 1 Reputation point

2023-08-02T08:50:16.4233333+00:00

We (partially) solved the problem as follows: instead of retrieving the job status every 10 seconds, we retrieve it every 5 minutes. In this way, we drastically limit the number of loops and have reduced the "dead time" from 30 minutes to less than 2 minutes for an 8-hour process.
Please sign in to rate this answer.
Ali Soleyman 5 Reputation points

2023-08-03T15:27:54.5333333+00:00

Hi @Viguro Is this means we need a wait object in Until loop with 5 Minutes delay? Can you explain your solution, Thanks

Viguro 1 Reputation point

2023-08-04T07:17:24.54+00:00

Our pipeline consists of launching an external job with a web activity.

Then, in the until loop, we wait for a defined time and then check the status of this job, until termination.

Previously, the wait time was set to 10s, causing to lot of dead time at the end of the unitl loop.

Now, this wait time is set to 5mn. Those, the dead time was drasticly reduced
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Azure Data Factory Until loop slow to end/terminate

6 answers

Your answer