This is an archived copy of the forum for reference purposes

Massive update of a recordset, what best practice?

posted by aurelien 8 years ago

Hi !

Does anybody know a way to update a lot of records? Lets say I have to move a bunch of records from one database to another. This is great to do this using flourishlib because it can connect to multiple databases. I cannot do it manually because this operation is part of an application, is done from time to time with different criterions and sets each time.

What is in your opinion, the best way to do this using flourishlib?

I dont thik there is a method such as fRecordSet::store() that would issue one big UPDATE command.

And I experienced pretty slow performance when making a store() on each element of the set.

Any ideas ?

Thanks !

I don't know of any easy way or best practice of doing thing. The API is centered around single records. But a ticket has been opened for the implementation of a unit of work that allows you to store changes in one transaction. ( doing something like $unit_of_work->store(); )

http://flourishlib.com/ticket/539

I'm unaware of the state of this particular project.

posted by mblarsen 8 years ago

I would say the best practice for doing performance-sensitive queries is to do execute SQL statements, but just be sure to include the SQL inside of a method on your fActiveRecord model. This way you get the performance you need, but keep your code centralized.

You can always manually start a transaction in your database, perform a bunch of fActiveRecord actions and then commit. Otherwise, each fActiveRecord operation will start its own transaction, which will definitely slow things down.

$db = fORMDatabase::retrieve();
$db->query('BEGIN');
foreach ($users as $user) {
    $user->setStatus('Disabled');
    $user->store();
}
$db->commit();

The downside of this pattern is that it does full validation on each record before saving it, in the effort of creating user-friendly validation messages. Such extra steps to make output user friendly is why the ORM will be slower and I would recommend just using a SQL UPDATE statement instead.

posted by wbond 8 years ago