Fastest way to insert data in Laravel

Question

I've made a method to copy branch user permissions. Permissions usually 0 to 120 items. And running this method to insert this (0 to 120) items to 7 user_ids tooks 35-55 seconds to insert to the table.

/**
 * Copy Branch User Permissions
 *
 * @param Int
 * @param Int
 * @param Int
 * @return Array
 */
public function copyBranchUserPermissions($branchIdFrom, $branchIdTo, $userId)
{
    $permissions = BranchPermissionUser::get()
        ->where('branch_id', $branchIdFrom)
        ->where('user_id', $userId);

    foreach($permissions as $permission) {
        $permission->create([
            'branch_id' => $branchIdTo,
            'permission_id' => $permission->permission_id,
            'user_id' => $userId
        ]);
    }

    return $permissions;
}

I need to optimize this process because of slow performance in the insertion of data. I'm thinking what if more than 10 users, so this process will be take too long. Is there any way to increase the performance speed in inserting data?

Thank you in advance!

https://stackoverflow.com/questions/29723865/how-to-insert-multiple-rows-from-a-single-query-using-eloquent-fluent — Roman Meyer, Nov 02 '19 at 13:41
Probably want to look at [Laravel Queues](https://laravel.com/docs/5.8/queues) — Peppermintology, Nov 02 '19 at 13:47
I think the problem is in your getting a list of permissions because this will immediately return all `BranchPermissionUser::get()`. You need to do get() as the last one. — Vladan, Nov 02 '19 at 13:50
Not an Laravel person, but I would have thought something like [Create a Insert… Select statement in Laravel](https://stackoverflow.com/questions/25533608/create-a-insert-select-statement-in-laravel) is the nest way (basically a SQL INSERT...SELECT statement equivalent). — Nigel Ren, Nov 02 '19 at 14:10
A quick and dirty optimization, and it won't get rid of all your overhead, but try running the loop inside a transaction: https://laravel.com/docs/5.8/database#database-transactions — Chuck Adams, Nov 02 '19 at 15:31

Anuj Shrestha · Accepted Answer · 2019-11-02T15:16:42.010

If you are in serious need of speed optimization it is better to stick with Query Builder rather than going in the Eloquent way.

For more information : Laravel Eloquent vs query builder - Why use eloquent to decrease performance

While you are fetching the user and branch-specific permissions

 $permissions = BranchPermissionUser::get()
    ->where('branch_id', $branchIdFrom)
    ->where('user_id', $userId);

You are first loading all the data from the database table and performing where on the collection generated of all the data.

You should perform where conditions before the get method, so it will be handle on SQL end which will be much faster than to do in the collection created from the get eloquent method.

$permissions = BranchPermissionUser::query()
                        ->where('branch_id', $branchIdFrom)
                        ->where('user_id', $userId)
                        ->get();

Also, you are looping over all the permissions and creating each one in the database.

 foreach($permissions as $permission) {
    $permission->create([
        'branch_id' => $branchIdTo,
        'permission_id' => $permission->permission_id,
        'user_id' => $userId
    ]);

This creates a single query for each create. This is very slow and not the best practice to save the multiple rows of data at the same time.

You should use insert method to insert all of the rows which creates a single SQL query and is much faster and optimized.

NOTE: Insert method will not handle the created_at and updated_at field while inserting the rows. So you will have to manually add data for both of them.

$data=[];
foreach($permissions as $permission) {
  $data[] = [
    'branch_id' => $branchIdTo,
    'user_id' => $userId,
    'permission_id' => $permission->permission_id,
    'created_at' => now(),
    'updated_at' => now(),
  ];
}
BranchPermissionUser::insert($data);

now() is a Laravel helper which creates a new Carbon instance for the current time:.

score 3 · Answer 2 · answered Nov 02 '19 at 14:08

You can use Eloquent insert() method.

public function copyBranchUserPermissions($branchIdFrom, $branchIdTo, $userId)
{
    $permissions = BranchPermissionUser::where('branch_id', $branchIdFrom)
                    ->where('user_id', $userId)
                    ->get();

    $data=[];
    foreach($permissions as $permission) {
      $data[] = [
        'branch_id' => $branchIdTo,
        'permission_id' => $permission->permission_id,
        'user_id' => $userId
      ];
    }
    BranchPermissionUser::insert($data);
    return $permissions;
}

score 0 · Answer 3 · edited Nov 05 '19 at 05:56

You can use the Eloquent fill() method.

public function copyBranchUserPermissions($branchIdFrom, $branchIdTo, $userId)
{
    $permissions = BranchPermissionUser::where('branch_id', $branchIdFrom)
                    ->where('user_id', $userId)
                    ->get();

    foreach($permissions as $permission) {
      $permission->fill([
        'branch_id' => $branchIdTo,
        'permission_id' => $permission->permission_id,
        'user_id' => $userId
      ]);
       $permission->save();
    }
    return $permissions;
}

This will help you to improve the speed of your function since what you are looping is the result that you got from the query instead on the query by itself. And inorder to use the fill() function your data type must be fillable

Fastest way to insert data in Laravel

3 Answers3