PHP Script - Efficient use of execution time

**Dave Patton** · Jul 17 '05, 11:46 AM

Re: PHP Script - Efficient use of execution time

dohertywa@hotma il.com (Adam) wrote in
news:f160a010.0 502271943.4427b b59@posting.goo gle.com:
[color=blue]
> Hi,
>
> I am working on a project that requires me to parse 52,005,167 rows in
> a MySQL table. I am using a series of PHP scripts for each test I
> would like to run on the data. For example I have one test that is to
> look for fields with consecutive values. If a row is found to have
> more than 4 fields containing consecutive values I would like to
> remove the row from the table.
>
> I understand that this will take a good deal of time to process, which
> is not a problem. What I am wondering is, should I disable the
> maximum script execution time?
>
> Currently I am running these tests in blocks of 10,000 rows at a time.
> I would like to just run a test and let it run from start to finish.
>
> Any comments?[/color]

If I was doing it I'd make sure the rows have a unique key,
and I'd have my 'processing script' accept query string
parameters such as "start" and "count". For example:

Example Domain

http://www.example.com/myscript.php?start=1&count=10000

Then I could run multiple instances in multiple browser
windows, perhaps at staggered times, and be able to
have partial results while processing continues on
other parts of the table.

--
Dave Patton
Canadian Coordinator, Degree Confluence Project

the Degree Confluence Project

http://www.confluence.org/

The Degree Confluence Project contains photographs of the intersections of integer latitude and longitude degree lines.

My website: http://members.shaw.ca/davepatton/

**sharma** · Jul 17 '05, 11:46 AM

Re: PHP Script - Efficient use of execution time

I hope this will help you...

This function increses the time set for execution..
set_time_limit( )

max_execution_t ime integer
This sets the maximum time in seconds a script is allowed to run before
it is terminated by the parser. This helps prevent poorly written
scripts from tying up the server. The default setting is 30.

The maximum execution time is not affected by system calls, stream
operations etc. Please see the set_time_limit( ) function for more
details.

You can not change this setting with ini_set() when running in safe
mode. The only workaround is to turn off safe mode or by changing the
time limit in the php.ini.

**montgomery.matt@gmail.com** · Jul 17 '05, 11:46 AM

Re: PHP Script - Efficient use of execution time

I'd use set_time_limit( N) -- it's never a good idea to simply disable
execution time, and because you're looping through these rows, you can
use this function to increase the current time limit by N seconds,
where N is a reasonably safe estimate of the time needed for each
iteration of the loop. This will only work if you're not running in
safe mode, so hopefully this will be of some help.

**Chung Leong** · Jul 17 '05, 11:46 AM

Re: PHP Script - Efficient use of execution time

"Adam" <dohertywa@hotm ail.com> wrote in message
news:f160a010.0 502271943.4427b b59@posting.goo gle.com...[color=blue]
> Hi,
>
> I am working on a project that requires me to parse 52,005,167 rows in
> a MySQL table. I am using a series of PHP scripts for each test I
> would like to run on the data. For example I have one test that is to
> look for fields with consecutive values. If a row is found to have
> more than 4 fields containing consecutive values I would like to
> remove the row from the table.
>
> I understand that this will take a good deal of time to process, which
> is not a problem. What I am wondering is, should I disable the
> maximum script execution time?
>
> Currently I am running these tests in blocks of 10,000 rows at a time.
> I would like to just run a test and let it run from start to finish.
>
> Any comments?[/color]

You might want to consider upgrading to PHP 5. PHP 4's garbage collection is
weak. It might not be able to get through that much data processing before
it runs out of memory.

And yes, you should disable time limit.

**Peter Fox** · Jul 17 '05, 11:47 AM

Re: PHP Script - Efficient use of execution time

Following on from Adam's message. . .[color=blue]
>Hi,
>
>I am working on a project that requires me to parse 52,005,167 rows in
>a MySQL table. I am using a series of PHP scripts for each test I
>would like to run on the data. For example I have one test that is to
>look for fields with consecutive values. If a row is found to have
>more than 4 fields containing consecutive values I would like to
>remove the row from the table.
>
>I understand that this will take a good deal of time to process, which
>is not a problem. What I am wondering is, should I disable the
>maximum script execution time?
>
>Currently I am running these tests in blocks of 10,000 rows at a time.
> I would like to just run a test and let it run from start to finish.
>
>Any comments?
>
>TIA to for any help.
>Adam[/color]

* It /might/ not be a good idea to DELETE in the middle of processing.
You may want to mark the record ready for deletion or make a separate
note and then use MySQL to delete in bulk.

* you /might/ find it worthwhile to export to a text file only possible
candidates (for example you might be able to put useful filtering in SQL
to weed out definite OKs or select certain likely candidates) and then
process the text file sequentially to get your record IDs for deleting.

* What alternative to disabling the max execution time did you have in
mind?

* You might also consider pre-processing your data in other ways
SELECT IF(COL2-COL1=1,1,0),IF( COL4-COL3=1,1,0),IF( COL6-COL5=1,1,0)
.....

Having processed lots of data with steam computers it really is worth
while optimising your method 'cos you can get order of magnitude
improvements.

--
PETER FOX Not the same since the bottom fell out of the bucket business
peterfox@eminen t.demon.co.uk.n ot.this.bit.no. html
2 Tees Close, Witham, Essex.
Gravity beer in Essex <http://www.eminent.dem on.co.uk>

**Tony23** · Jul 17 '05, 11:47 AM

Re: PHP Script - Efficient use of execution time

"Peter Fox" <peterfox@emine nt.demon.co.uk. not.this.bit.no .html> wrote in
message news:NDUmzKAnT1 ICFwDy@eminent. demon.co.uk...[color=blue]
> Following on from Adam's message. . .[color=green]
> >Hi,
> >
> >I am working on a project that requires me to parse 52,005,167 rows in
> >a MySQL table. I am using a series of PHP scripts for each test I
> >would like to run on the data. For example I have one test that is to
> >look for fields with consecutive values. If a row is found to have
> >more than 4 fields containing consecutive values I would like to
> >remove the row from the table.
> >
> >I understand that this will take a good deal of time to process, which
> >is not a problem. What I am wondering is, should I disable the
> >maximum script execution time?
> >
> >Currently I am running these tests in blocks of 10,000 rows at a time.
> > I would like to just run a test and let it run from start to finish.
> >
> >Any comments?[/color][/color]

You could have the script that runs the 10,000 rows call itself again,
passing an index pointer so that it restarts at the new pointer.

If you want, I have a script that does this - you could look over the code
(if you want, email me "tony" at "naturesflavors " dot "com"). My solution
uses JavaScript to call the PHP script again, passing the pointers in the
URL.

**Adam** · Jul 17 '05, 12:02 PM

Re: PHP Script - Efficient use of execution time

Good morning all,

mysql_unbuffere d_query is a freaking amazing tool. As originally
written this script was estimated to take 7 days 19 hours to complete;
mysql_unbuffere d_query and turning off script execution limits knocked
that down to 1 hour and 11 minutes.

Many thanks to all of you. You helped to point me in the right
direction and look outside of what I was used to using. I'll admit
this is my first time using mysql_unbuffere d_query and it has
literally saved this project. Thanks again.

Adam

PHP Script - Efficient use of execution time

PHP Script - Efficient use of execution time

Comment

Comment

Comment

Comment

Comment

Comment

Comment