morfin's avatar

Mode::each() with 100,000 records, issues on updating

Hi,

I have a query that fetches around 100,000 records. I need o update certain records based on certain criteria:

MyModel::where('is_paid', false)->count();
// Result: 100,000

// This should loop trough all the 100,000 records
MyModel::where('is_paid', false)->each(function($model){
    if($model->total === 0) { // Total is an accesor property that triggers a query itself.
        $model->is_paid = true;
        $model->save();
    }
});

MyModel::where('is_paid', true)->count();
// Result: 45,000
// Expected: 70,000

You would expect that all records that meet the condition "total === 0", would be updated, right? Unfortunately not. For some weird reason, probably memory related, some models are skipped. The reason might be that the missed records are simply not fetched at all, or they hit the loop but the save() is not doing the job.

If I run the same query a second time, and then check the affected models, the result would be something like: 46,000 (a thousand more). That means that the second run found models that weren't affected on the first run.

If I keep running the same query over and over again, the affected records keep increasing, till finally all records are updated as expected (70, 000 records).

I'd like to know if there are some obvious issues here, if it's related to the number of records I'm fetching, and what would be the safest way to do it without having to run multiple times the same query.

By the way, I already tried with chunks of 1000 records, but the problem still around.

Using MySQL 8.

Thanks.

0 likes
11 replies
Tray2's avatar

Not sure why you do this in a loop, you should just .

	UPDATE my_models SET is_paid = 1
	WHERE total = 0;

Anyway you need to fetch those records first, so try

MyModel::where('is_paid', false)->->each(function($model){
    if($model->total === 0) { // Total is an accesor property that triggers a query itself.
        $model->is_paid = true;
        $model->save();
    }
});
morfin's avatar

There might be different ways to solve this problem, but my main concern is why is this happening when using each on a huge amount of records ? Why Eloquent behaves like this when handling huge amount of records.

jlrdw's avatar

Normally I wouldn't load that many records, I'd chunk them. Just suggestion. And yes memory is probably the issue.

kokoshneta's avatar

How non-trivial is it to calculate the total? You say the accessor triggers a query of its own – what query is that?

Rather than fetching 100,000 records (which is very memory-intensive), it would probably be worth it to just do the whole thing in the database in one call so you don’t have to load any models into memory at all.

Tray2's avatar

@kokoshneta Or just add a computed virtual column to the table, then he can even search for it.

kokoshneta's avatar

@Tray2 If that’s feasible, certainly. A total column does sound like something that ought to be columnisable.

kokoshneta's avatar

@Tray2 By ‘columnisable’, I meant ‘having a value that can be calculated within the database’. If total – for whatever reason – requires input not available in the database (say, data from an external API call or from a user cookie), then storing it in a column, whether generated or not, may not be practical. But a total column would not normally fall into that category, so a priori you’d expect that storing it in the database should be feasible.

Tray2's avatar

@kokoshneta Agreed, it only works if the fields the value is based on is in the same table.

Please or to participate in this conversation.