Possibly (best guess, by the PHP code I've seen) there is a great amount of storage in memory, either by dumping huge amounts of data in an array them sifting through them to output. Alternatively, it may be an intensive set of mysql queries. In the first case, there may not be much you can do short of revising the script to output as it runs:

while )(some process) echo (data); }

The downside of this is you get a page that partially loads, then loads more, then may still time out with half the page loaded.

In the second case, the script will need to be revised for more efficient queries.

Many would suggest "just increase your script timeout" but if you're on shared hosting this is just selfish (and lazy.) :-) Even if you're not, it's a sign you're using too many resources.

What I would do is first fix and optimize as above, then if it's still timing out, either cron job it or experiment with fork [php.net]. What fork does is it spawns a child process in which you do intensive procedures that would otherwise timeout the browser. The parent process immediately returns a response and has the child's processid available ($pid in that example.) Then you listen for the $pid and when it dies, the process is done and can view any results.

An example might be
- Page with link/form to start process, grab $pid
- pass $pid to another page with a frameset. The framed document has an auto-refresh in the document to check the existence of the $pid (say, every 5 seconds)
- If the $pid exists, return # of records processed and "working" message
- if the $pid doesn't exist any more, return "done" message and display results.

Could you start the long PHP process from an AJAX request?

No, not easily.

I just think I'm missing something obvious and simple. Let me take it back to basics, please. This is roughly what my script is doing....

$query = "SELECT (whatever)"
$result = mysql_query($query);
$records = mysql_num_rows($result);
for ($i=0; $i < $records; $i++ ) {
$row = mysql_fetch_array($result);
// run update procedures
echo "updated record blah blah<br>";
}

Simple, but in big files, it doesn't display any of the echos before the script finishes or the browser times out.

OK, so you're doing print-as-you-run. First thing I would do is eliminate that counter (and I will never understand why it's often used in that way - don't feel bad, I see this one a lot. :-) )

while ($row = mysql_fetch_array($result)) {
// run update procedures
echo "updated record blah blah<br>";
}

Why?

- You gather query results and store them in $result.
- For every member in the $result object, you go into the $result array and execute mysql_fetch_array. Thousands of times?
- Also eliminates the need for mysql_num_rows. Personally, the only time mysql_num_rows is needed is if I'm doing pagination or need a reference to the total number of rows. Usually you don't need it (the savings on that query itself would be trivial though.)

The above executes mysql_fetch_array once and the while loop iterates through the $result object. That alone might do it. From the manual [php.net],

result

The result resource that is being evaluated.

mysql_fetch_array needs only to evaluate $result once.
(A caveat, using mysql_fetch_row might save you nanoseconds, but it's not nanoseconds you're having a problem with.)

If it's really that simple and it's still slow, then it is probably down to poor DB setup. Do you have indices? Adding the proper index on fields increases seek performance, but slows down updates. If at all possible, in your first query do

select id,field1,only-fields-you-need from table

instead of

select * from table

For the inner loop updates, perhaps the indexing is working in the other direction. Are you updating fields that don't need to be indexed and will never be searched?

Thanks rocknbil - that's really useful and has given me a lot of food for thought. Brilliant! Thank you.

The above executes mysql_fetch_array once and the while loop iterates through the $result object.

The above actually executes mysql_fetch_array on every iteration of the while loop and is what iterates through the $result object.

I second what penders has said. The condition in a loop gets evaluated and checked on each iteration. The difference between the two versions is that one calls mysql_num_rows and holds a single additional integer counter and that is it; these differences will not cause a significant change in execution time.

How many rows are you selecting initially?

What does the update procedure do? Is a db update on each result from the original query?

>> increases seek performance
Actually, it depends on what is usually being sought for. If it turns out that the query is going to return a significant part of the table's data, an index can hurt seek performance.

>> What am I doing wrong and/or how can I 'force' the echo command to print the comment immediately?

PHP does not work like this. It's called a pre-processor for a reason. First it processes data on the server side, and then outputs something to the client. The "output" it sends is as one complete response, not individual responses. Therefore if the script is not finished "processing" it will not send a response for the client (web browser) to display.

What am I doing wrong and/or how can I 'force' the echo command to print the comment immediately?

PHP does not work like this.

Um, wrong, it will work that that and it's no that hard to do.

Therefore if the script is not finished "processing" it will not send a response for the client (web browser) to display.

It's not displaying because it buffers the output on the server side until the buffer fills and it sends the buffer all at once.

To make it send data constantly use flush(); after each echo which makes Apache flush it's buffer before it's completely full, which is used by quite a few programs that need to keep the status being refreshed on the screen.

I use it in a couple of programs myself so I know it works when it's configured right ;)

Try this:

<?php

for( $x=1; $x<=100; $x=$x+1 ) {
echo $x . '<br>';
flush();
usleep(300000);
}

?>

If the above is working properly, it will display a number, pause, display another number, pause, etc. If it's not working properly, it will pause a really long time and then display all the numbers at once.

Some servers have some additional buffer controls set in the configuration files that might need to be changed before flush(); will work. If I'm not mistaken, you may need to add the following line to your .htaccess file "php_value output_buffering Off" before flush(); will work. If it still doesn't work, it may need to be disabled in the master PHP.INI on the server, assuming the host will do that.

FYI, this setting is also required to be changed for any PHP-based streaming media applications as well

[edited by: incrediBILL at 2:02 am (utc) on Jan 7, 2012]

What Bill^ said about flush() (and ob_flush()).

Also have a look at mysql_unbuffered_query [php.net] which saves lots of memory and will be way faster.

heh learn something new every day. I can't even remember the last time I've written an actual php script. thanks guys.

just a caveat to my own post, I hadn't saw that there was a 2nd query in the loop, read the manual regarding this behaviour.

I try to use unbuffered wherever possible. In the case of updates... if the update values are limited, id push the update values into an array and then update all records with the same value, or use a temporary table and file upload.