use batch API

temporarily change the timeout to something longer than 30 seconds
At first looks, I don't think this is a clean / reasonable fix
- Some hosting configurations won't allow this
- I'm not sure php timeout setting is there for the code itself to decide when it should fiddle with it.
- What value should we use anyways ? 30s ? why ? any value will eventually prove to insufficient at some point...

I think operations on large datasets are a generally a delicate issue within cck or even drupal itself.
Any ideas welcome...

the only solution we have for this is what update.php does - spread processing over multiple requests.

also note that doing many node_load() operations in one request is very memory consuming - use the syntax at http://drupal.org/node/123705. not sure if that applies here.

yes, this was also raised in this issue : http://drupal.org/node/97861

I started some work a while ago on a general 'à la update.php progressive ops' API, but did not really got it polished, and I did not have too much time lately to get back to it...

yched, why don't you post what you have in case someone else has time to work on this and move it forward. There are a number of places where this will be needed as we have more and more installations using thousands of CCK nodes.

Yes, I should probably do that - this does require some level of minimal polishing / cleaning / explanations, though.
Will try do do that ASAP.

I uploaded my code in my sandbox :
http://cvs.drupal.org/viewcvs/drupal/contributions/sandbox/yched/batch/

Work in progress submitted at http://drupal.org/node/127539

This might well be a different scenario, but I was able to simply extend the time limit via PHP and Drupal was grinding away happily for however long it was set (in seconds).

set_time_limit(500);

This might not be allowed on a controlled environment, I guess.

That's right, after i wrote I'm not sure php timeout setting is there for the code itself to decide when it should fiddle with it, I realized that drupal core actually uses set_time_limit(240) in some places.

The code is wrapped in if (!ini_get('safe_mode')) {, and won't run on most shared environments.

I don’t know what has been discussed in the passed about things like this, and I don’t know what the current implementation is, and I think many of you know about this, but here is some of my experiences.

I ran into this same problem a while back with a bulk import feature. A very large amount of data was being imported form CSV files. And the imports were taking very long, up to 5min some times. So I started looking at ways to improve this, and what I found was that if you reduce the amount of queries, the time it takes is decreased dramatically. In the end I got it down to under 20 seconds.

You do this by not executing the queries one at a time.

Example:

INSERT INTO x (a,b)
VALUES
 ('1', 'one'),
 ('2', 'two'),
 ('3', 'three');

This worked very well, I did run into a small problem and that was the bulk_insert_buffer_size

The way I got around this was by building many bulk queries.

Example:

$loop_count = 0;
$sql = 'INSERT INTO {x} (a,b) VALUES ';
While () {
  
  //......
  
  if ($loop_count == 0) {
    $sql .= "($a,$b)";
  }
  else {
    $sql .= ", ($a,$b)";
  }
  
  //......

  // must build a query smaller than the bulk_insert_buffer_size
  if (strlen($sql) / 1024 >= 32) {
    $sql .= ';';
    db_query($sql);
    $sql = 'INSERT INTO {x} (a,b) VALUES ';
    $loop_count = 0;
  }

  //......

  $loop_count++;

}

Now I don’t know if this part of the SQL standard or if it is even possible in other databases but it worked fine on a mysql 4 installation.

The other option that I have used when moving large amounts of data from one table to another is the INSERT ... SELECT. Again I don’t know about other databases but it also works really well.
Here are the docs for it. http://dev.mysql.com/doc/refman/5.0/en/insert-select.html

Example:

INSERT INTO x (a,b)
SELECT c, d FROM z;

Hope that helps.

The batch API that is now in D6 should let us implement some workaround for these situations - turning this thread into a task.

This is now in HEAD. It still needs tweaking and debugging, but it's there.