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…
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.
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?
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.
File Table (this is list of agent collection, user will upload this via csv file)
Agent - Text
Collection - number
Bank - text
Agent Table
Agent Code - text
Program - List of Program
Bank - text
Program Table
Title - text
Bank - text
Rate
Result Table
Agent - text
Program - Program
Bank - text
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.
