Optimize Speed of Backend API Workflow in Creating New Thing

I am looping through my list of unique agents from my table ‘Agent,’ then I will calculate their earnings by retrieving values from different tables such as ‘Collection,’ ‘Program 1,’ ‘Program 2,’ ‘Program 3,’ etc.

The ‘Collection’ table contains the collected amount of all the agents, which is around 30k to 50k rows. The ‘Program’ tables contain the values I need for the calculations, with each table containing around 100 to 600 rows of data. The ‘Agent’ table contains 800 rows.

Basically, when I click the button, it will loop through the list of unique agents, retrieve the necessary data from different tables, calculate their earnings, and save it to my database. The calculations uses lots of filtering to get the values I need from each tables.

My problem is that it takes 30 minutes to complete all the calculations. I want to reduce the processing time to around 10 to 20 minutes.

I have created a simple representation of how my API Workflow works (I am not sure if this is how it really works).

This is probably a consequence of an inefficient database strucure… what’s your app, what’s it used for? An ‘Earning’ for an agent should likely be its own data type that you just need to run a search:sum for…

It’s an app for calculating commission earnings of an agent.

Yes you are right, but this is only a portion of the calculation

  1. When button is clicked I will schedule api workflow in a list

  1. This API Workflow will be triggered

  1. Then the action will call another Schedule API Workflow on a list

  1. Then it will create the commission earning

This is only 1 program and basically I have 5 more

Okay, well, I don’t think anyone’s going to be able to help you because we have no clue what each of your fields are and how commissions are calculated.

If you want to be able to do it, whenever an Agent should earn a commission, just create a new Earning that can then be searched for later. You’d want to attach the created Earning to the sale/call/whatever they’re getting a commission for.

This is already working, I just want to reduce the processing time, since it’s taking me 30 mins to processed all of it.

Yes, but almost anything like this should be calculable without many (if any) backend workflows and in a few seconds. 30 minutes is a different order of magnitude that’s almost certainly a consequence of a DB oversight

Yeah, I tried using simple looper but it crashes so I used backend workflow instead. So the problem is how I structured the database or the calculation itself since it uses lots of filtering? or maybe the way I structured the API Workflows?

Thank you for answering my question.

The answer to your question is that the database structure is wrong.

You need a table with earnings that has linked Agent and linked Collection/Program for the earning.

When you create a new signup/sale or whatever it is, just create a new earning with the agent and program linked it. This spreads the 30 minutes over indefinite time because it takes a fraction of this time to create a new earning.

When you want to calculate the earnings, just sum earnings for the given Agent which should be a few seconds.

1 Like

The simplified database structure look like this

File Table (this is list of agent collection, user will upload this via csv file)

  1. Agent - Text
  2. Collection - number
  3. Bank - text

Agent Table

  1. Agent Code - text
  2. Program - List of Program
  3. Bank - text

Program Table

  1. Title - text
  2. Bank - text
  3. Rate

Result Table

  1. Agent - text
  2. Program - Program
  3. Bank - text
  4. Earnings - number

I will loop through the Agent Table, search for their collections in the File Table, and sum all of them. Then, depending on the Program that is tagged on the agent, I will calculate the earnings with the rate. After that, I will save it to the Result Table.