An old site I've inherited has a script that users can run from the CMS that basically pulls out all the products from the database table and exports them as a xls file. There aren't that many records, around 5000, and up until recently it's been working fine. As the amount of products has increased, the script execution time has also increased, and recently it's started exceeding the servers max_execution_time of 30 seconds.
When I look at the process running on the server, I notice that as soon as the TIME+ hits 60 seconds, the script dies.
If I limit the SQL results to say 4500, the script executes within 60 seconds, there are no problems.
The SQL Query seems to be fine. I checked the execution time and it's just under 40 seconds.
I also tried extending the scripts execution time by using both set_time_limit (120); and ini_set('max_execution_time', 120);, However when doing so, instead of running one time for 120 seconds, the script will run 2-3 times, each time for 60 seconds, dying, and coming back as a new process.
Any help would be greatly appreciated.
Best Answer
If the problem is on the database side, I suspect an index missing. Try to
the query that is sent to the server. It should tell you which parts are taking long. For a detailed explanation of the EXPLAIN, you could use http://explain.depesz.com/
Can you offload the xls-generation into a server-script in the background? The result could then either be linked on the site or mailed to the requesting person. This is the usual approach in the Rails-world (even though there is seldomly a timeout set).