CODEWITHSUNDEEP
phpdatabaselaravel
Kaizokupuffball
Kaizokupuffball

Reputation: 2833

Laravel from database table to another table 1mill rows, slow

So basically, I got 2 tables. The first table contains 1 million rows or something, with an empty field called 'telefon'.

Now, I got a second table, which has the field values for 'telefon' in the other table.

I came up with this solution, but this takes forever. It has been an hour, and when inspecting the database table, only 1600 rows are done. Is there any faster ways of doing this? Thanks in advance.

DB::table('phones') -> orderBy('id') -> chunk(100, function($old) {
        foreach ($old as $x) {
            DB::table('companies')
                -> where('organisasjonsnummer', $x -> businessCode)
                -> update([
                    'telefon' => $x -> contact
                ]);
        }
    });

Upvotes: 0

Views: 104

Answers (2)

Felippe Duarte
Felippe Duarte

Reputation: 15131

Always remember, when you use Eloquent/SQL inside a loop, you will run a command for each round. The same applies for lazy loading.

In this case you should use \DB::statement("put your sql here"); or in this case \DB::update("put your update here");, let the database do the service for you and be happy!.

Upvotes: 1

Oliver Maksimovic
Oliver Maksimovic

Reputation: 3262

Huh, foreach + queries is almost always bad. If I am not mistaken, you would like to do this:

UPDATE companies, phones SET companies.telefon = phones.contact WHERE companies.organisasjonsnummer = phones.businessCode

It may be very slow if there's no index on companies.organisasjonsnummer and phones.businessCode columns, but it can take a lot of time to index them now as well, so I'm not sure if there's any benefit to index them now if they won't be used later. Anyway, using a single query should be faster at least to some extent.

Upvotes: 1

Related Questions