I have a script that I"ve written in PHP.
I wrote a script for a company, but it's taking extremely long. All table columns are indexed properly, but sometimes the script can take up to 20 minutes to run.
I can provide code examples if needed, but first allow me to explain how the script runs to see if it can be optimized or organized a little better.
A user will select a start date, end date, and location from a form. Upon submission, the form submits to an ajax call that passes the POST variables to the main script called run_audit.php
The script begins by connecting to an external API and returns XML data. From there, it loops through the XML and inserts records into the database. I then make another call to the API to return different data, for the specified location. I need to update the table I just wrote to with the correct type for each bus record I've inserted. Unfortunately these are two separate API calls and actions.
I notice that the update script takes a little while to run, I've tried pulling the bus numbers out, looping through there and updating, and I've also tried looping through the returned API results and updating. Both take about the same time.
The program then makes a call to an MSSQL server and runs a query that returns the employee name, employee id, and job title. This part takes a little while as well, but I'm assuming that the MSSQL tables I'm pulling from are not indexed properly [this part is not my issue, as I'm just connecting to another person's table].
With the employee query I just ran, I put the results in a while loop. Within this while loop, I have a foreach loop that loops through each day of the week.
Now within each day, I have to run queries to get the data initially inserted from the API call.
For each person, for each day of the week, I run queries for the bus number that they drove OUT of the lot time and back IN the lot, for each day segment.
Day Segments are broken up like this: AM, Mid, PM, and Late PM.
The loop now is looping for each employee [~200] per day [5 days] per segment [4 per day] with both and IN and OUT query.
By my calculations, my script is running about 8,000 queries, which could be the reason the script is extremely slow.
I'm wondering if there is a better way to structure the process so this script doesn't take 20 minutes.
I have attached an image below of the JSON array that is being returned from the script.
Again, I know without code it's almost impossible to visualize, and will be happy to provide if needed.
Thank you in advance.