循环时UPDATE查询表单，代码很慢

Question

i have in my Laravel App this code: i read every line in a .csv file and a want to update a value. But the multiple's update query are very slow with a .csv of 8k rows. How i can speed this code ? Thanks

DB::beginTransaction();

        try {

            $delimiter = ",";
            $firstLine = true;

            if ($handle !== FALSE) {
                $position = 1;
                while (($csv_line = fgetcsv($handle, 1000, $delimiter)) !== FALSE) {

                    if ($firstLine == true) {
                        $firstLine = false;
                        continue;
                    }

                    $player_uid = $csv_line[0];

                    DB::table('scores')
                        ->where('season_uid', $season_uid)
                        ->where('day', $day)
                        ->where('player_uid', $player_uid)
                        ->update(['position' => $position]);

                    $position++;

                }
                fclose($handle);
            }

            DB::commit();
            return true;

        } catch (\Exception $e) {
            Log::error($e);
            DB::rollBack();
            return false;
        }

Answer 1

MySQL doesn't support mass updates, but there is a neat trick to replace updates with inserts using ON DUPLICATE KEY UPDATE clause. That way you can actually update your records in bulks. Check out this answer for some examples.

To my knowledge though Laravel doesn't support this clause in it's query builder, so you will have to generate query manually and issue it via DB::statement(). Make sure to chunk your incoming rows (by 100, for example) and you will see noticeable boost in speed.

But do realize that updating 8k rows is not a cheap operation. The best practice would be to delegate it to a separate job and set up queues in your application so that those updates can be processed in the background by your workers separately. You can read more about jobs and queues in the official documentation.