Very large database export to csv, excel without php timeout
Posted: Mon Oct 01, 2007 2:50 am
When exporting very large datasets, lets say, to an Excel file, or a CSV file, it times out; or, importing large files to put into the database.
Increasing the php max execution time, or set_time_limit is an option, however this does not feel like it is the best practice for a few various reasons.
1. If there is a bad script somewhere within my app, then in an environment with potentially thousands of users, this would wreak havoc. It seems there is a limit there for a reason in the first place.
2. I may be exporting up to 250,000 items, or perhaps importing 100,000 items, where each item represents up to 20 INSERT statements.
3. Database memory limits (maybe mysql_unbuffered_query can help here?)
I have seen systems, such as analytics software, built in a way where you start the report generation, and then can return later when it's finished. I do not know if this is smart, nor how it is done.
One idea I had was to somehow run the script over and over, using some standard number I define (e.g. 5,000 records). Sort of a loop, or batch/queue. I would of course need to keep track of where it started and stopped between each cycle. Command line stuff, or C++ is really undesired here as well since I want to make the app as portable, and purely PHP dependent as possible.
However, there is probably already an ideal design pattern, or best practice for this sort of situation--and help here?
Increasing the php max execution time, or set_time_limit is an option, however this does not feel like it is the best practice for a few various reasons.
1. If there is a bad script somewhere within my app, then in an environment with potentially thousands of users, this would wreak havoc. It seems there is a limit there for a reason in the first place.
2. I may be exporting up to 250,000 items, or perhaps importing 100,000 items, where each item represents up to 20 INSERT statements.
3. Database memory limits (maybe mysql_unbuffered_query can help here?)
I have seen systems, such as analytics software, built in a way where you start the report generation, and then can return later when it's finished. I do not know if this is smart, nor how it is done.
One idea I had was to somehow run the script over and over, using some standard number I define (e.g. 5,000 records). Sort of a loop, or batch/queue. I would of course need to keep track of where it started and stopped between each cycle. Command line stuff, or C++ is really undesired here as well since I want to make the app as portable, and purely PHP dependent as possible.
However, there is probably already an ideal design pattern, or best practice for this sort of situation--and help here?