I've had a performance problem that i solved crossing the boundaries of PHP with other technologies.
I needed to loop over a CSV with thousands of rows, sanitize and validate its values, and then update a MySQL table if the "check in" column was after the one already present in the DB, or insert the row if it wasn't already present.
Checking each date in a loop, even with preloading all table records in advance and stacking all updates/insert queries to result in only 1 SELECT, 1 INSERT and 1 UPDATE query with multiple rows attached resulted in a script running sometimes even for 10+ minutes, which was obviously not acceptable for a HTTP request.
What i did was to update the INSERT statement with the ON DUPLICATE KEY UPDATE .... clause and create an update trigger on the MySQL table that discarded the update if the new checkin date was before the old date.
This resulted in only 1 INSERT query and my script finished in a bunch of milliseconds.
As @vasilvestre
said, sometimes you just need to use better tools to improve execution time, and that does not mean to drastically change technology, but rather to check what's at your disposal and find the perfect combination.
100% agree to this comment. Fully taking advantage of what you have available within your organization or business specifications can create some drastic improvements. Oftentimes it's striking the balance of what load we put on the client side, what load we put on the server side, what load we have done at execution time, and what can we do post-execution (like your update trigger in your MySQL DB). A creative approach to that can do wonders as well.
For further actions, you may consider blocking this person and/or reporting abuse
We're a place where coders share, stay up-to-date and grow their careers.
I've had a performance problem that i solved crossing the boundaries of PHP with other technologies.
I needed to loop over a CSV with thousands of rows, sanitize and validate its values, and then update a MySQL table if the "check in" column was after the one already present in the DB, or insert the row if it wasn't already present.
Checking each date in a loop, even with preloading all table records in advance and stacking all updates/insert queries to result in only 1 SELECT, 1 INSERT and 1 UPDATE query with multiple rows attached resulted in a script running sometimes even for 10+ minutes, which was obviously not acceptable for a HTTP request.
What i did was to update the INSERT statement with the
ON DUPLICATE KEY UPDATE ....clause and create an update trigger on the MySQL table that discarded the update if the new checkin date was before the old date.This resulted in only 1 INSERT query and my script finished in a bunch of milliseconds.
As @vasilvestre said, sometimes you just need to use better tools to improve execution time, and that does not mean to drastically change technology, but rather to check what's at your disposal and find the perfect combination.
100% agree to this comment. Fully taking advantage of what you have available within your organization or business specifications can create some drastic improvements. Oftentimes it's striking the balance of what load we put on the client side, what load we put on the server side, what load we have done at execution time, and what can we do post-execution (like your update trigger in your MySQL DB). A creative approach to that can do wonders as well.