Throttling Parallel For Loop

Help
#1

Good Day,

I am trying to build a throttle for a parallel execution activity.

The detailed Business requirements are:

  • To execute a given number of SQL Stored procedures and wait for the result of the call.
  • As the results of each procedure is returned, the result should be updated in the corresponding excel file line containing the parameters which were utilised to execute the query.
  • The execution time of the queries vary between a minimum of a few seconds & a maximum of n Hours (~6) after which the query connection should be force closed.
  • Business requires the queries to be executed in parallel.
  • Business requires a throttle to be placed on the parallel execution i.e. No more than n (~10 for QA & 40 for prod) queries should be running at any given time.
    • Based on the wording of this requirement one should not assume that the throttle will be fixed. (Try this limit and we can play with it if does not work)
  • (At this point) It is a nice to have the maximum number of queries always be executing as this will result in faster completion of the process.
  • Fastest possible completion of the process is a business requirement.

Thus far, I have built 3 solutions which work to varying degrees, a possible native support for the functionality we are trying to achieve.

Solution 1: Execute Parallel Loops in batches using parallel for each.

  • I had a hard time fulfilling the rest of the requirements (i.e. updating the spreadsheet, if we split the DataTable) .
    • However, my team has found some alternatives, which I have not discussed with business but involves relaxing business requirements.
  • Requires waiting for all items in the batch to be complete in order to begin the new batch.

Solution 2: Utilise invoke Code and execute VB.net Parallel Activity using ParallelOptions.MaxDegreeOfParallelism Property to achieve throttle.

  • Requires advanced knowledge to support, may not be easily supported by the rest of my team.
  • Requires executing UiPath Activities using the InvokeCode block (which I have been able to do)

Solution 3: Use a parallel activity ‘n’ times.

  • Requires re-development every time the throttle/number of parallel activities change.
  • Requires waiting for all items in the batch to be complete in order to begin the new batch.
  • Code becomes long, causes UiPath Studio to glitch…

My Question is are there any other ways for me to fulfill the business requirements?

Best Regards,
Ahmed

#2

Hello @Ahmed_Vadia!

It seems that you have trouble getting an answer to your question in the first 24 hours.
Let us give you a few hints and helpful links.

First, make sure you browsed through our Forum FAQ Beginner’s Guide. It will teach you what should be included in your topic.

You can check out some of our resources directly, see below:

  1. Always search first. It is the best way to quickly find your answer. Check out the image icon for that.
    Clicking the options button will let you set more specific topic search filters, i.e. only the ones with a solution.

  2. Topic that contains most common solutions with example project files can be found here.

  3. Read our official documentation where you can find a lot of information and instructions about each of our products:

  4. Watch the videos on our official YouTube channel for more visual tutorials.

  5. Meet us and our users on our Community Slack and ask your question there.

Hopefully this will let you easily find the solution/information you need. Once you have it, we would be happy if you could share your findings here and mark it as a solution. This will help other users find it in the future.

Thank you for helping us build our UiPath Community!

Cheers from your friendly
Forum_Staff

#3

Hi @Ahmed_Vadia,
Nicely said. Maybe someone from our @UiPath-MVP have a experience with this and could add something?

#4

That’s a great question and would be really useful. I can think of multiple use cases where this could be used. I hope someone on here has an idea of how to achieve this

#5

Hi, I can add a little thought.

My highest degree is parallelism used has been with Queues. Essentially, each query would be added as a transaction, then multiple Robots would pick up the available transaction and perform them in parallel.

But, I suppose using your ideas such as Parallel Loops could work similarly. However, if an error occurs, the entire process breaks probably.

In either case, it sounds like you are not sure how to output the query results in parallel to Excel. To do this, rather than trying to split the data in different files you have a few options:

  • If the parallelism is performed in the same workflow process, then use Merge Data Table and then after the parallelism, use Write Range with the Data Table at the end. However, this won’t show the results’ progress during execution.
  • Or, with Queues parallelism or if it’s in the same workflow process, you can use the following logic:
Retry Scope
    IF File.Exists(filePath)
        Append Range
    Else
        Write Range

The idea here is that if the file is in use, it needs to error and perform a retry for a period of time (you don’t want an unlimited loop). So, if you have multiple attempts to output the data at the same time, it will let each one into the file at a time. I believe this can be done with the Workbook, Excel, CSV, or Text File activities (comma-delimited string).

Of course there are alternative ideas such as using the Assets to store a timestamp at the start of each parallel thread, and only allow each thread access to a specific entity one at a time based on the timestamp that goes with that thread, whether it be a set of credentials (like I have done in one process) or it be a file you want to update, and reset the asset back to 0 when it’s ‘open’ for thread use. I feel this complicates it though.

I think the future of parallelism will be Queues and multiple Robots performing the queue items.

Regards.

1 Like
#6

Hi Ahmed_Vadia
Just share my opinion on your Option 1.
For your concerns on updating the spreadsheet, you can consider to use activity Append Line to append comma separated text to a CSV file. The result will be updated to CSV file when parallel process is completed.
Another point is regarding Parallel process in UiPath. Parallel process in UiPath will not end until all the sub processes is completed. You don’t need to explicitly wait for all items to be completed in order to begin the new batch. UiPath will control it automatically.
Hope these ideas can help you.

1 Like