How to speed up writing belongsToMany relationships to database?

Question

Laravel

I parse an excel file, I write down categories and products from it in batches. After that, I need to record the relationships for them in the category_product table.
Now I’m writing it like this, but the script is running for a very long time, how to speed up this process?

class ProductImport implements ToCollection, WithChunkReading
{
    private const NO_CONST = 'Нет';
    private const AVAILABLE = 'есть в наличие';
    private string $productsTable = 'products';

    private function isHeadingRow(int $rowNumber)
    {
        return $rowNumber === 0;
    }

    public function collection(Collection $rows)
    {
        $productsCollection = [];
        $categoriesCollection = collect();
        $categoriesProductMap = [];
        foreach ($rows as $rowNumber => $row) {
            if (!$this->isHeadingRow($rowNumber)) {
                if (isset($row[10])) {
                    $row->shift();
                }
                $row = $row->values();

                if ($row[4]) {
                    $productsCollection[] = [
                        'producer' => $row[3],
                        'name' => $row[4],
                        'vendor_code' => $row[5],
                        'description' => $row[6],
                        'price' => $row[7],
                        'warranty' => $row[8] !== self::NO_CONST ? $row[8] : 0,
                        'available' => $row[9] === self::AVAILABLE,
                    ];
                    $categoriesCollection->add($row[0]);
                    $categoriesCollection->add($row[1]);
                    $categoriesCollection->add($row[2]);
                    $categoriesProductMap[$row[5]] = [
                        $row[0],
                        $row[1],
                        $row[2],
                    ];
                }
            }
        }
        $categoriesCollection = collect($categoriesCollection)->filter()->unique();
        DB::table('categories')
            ->insertOrIgnore(
            $categoriesCollection
                ->map(fn($category) => [
            'name' => $category
        ])->toArray());

        DB::table($this->productsTable)
            ->insertOrIgnore(
                collect($productsCollection)->map(fn($product)=> [
            'producer' => $product['producer'],
            'name' => $product['name'],
            'vendor_code' => $product['vendor_code'],
            'description' => $product['description'],
            'price' => $product['price'],
            'warranty' => $product['warranty'],
            'available' => $product['available'],
        ])
                    ->toArray());

          $categoriesProductMap = collect($categoriesProductMap)
            ->map(fn($categories) => collect($categories)
                ->unique()
                ->filter())
            ->toArray();
        foreach (Product::all() as $product) {
            if (isset($categoriesProductMap[$product->vendor_code])) {
                $productCategories = $categoriesProductMap[$product->vendor_code];
                $categoryIds = [];
                foreach ($productCategories as $productCategory) {
                    $catId = Category::where('name', '=', $productCategory)->first()->id;
                    if (!in_array($catId, $product->categories->map(fn($cat) => $cat->id)->toArray())) {
                        $categoryIds[] = $catId;
                    }
                }
                $product->categories()->attach($categoryIds);
            }
        }
    }

    public function chunkSize(): int
    {
        return 1500;
    }
}

Answer 1

Try to rewrite the part with category entries in products to:

Product::all()->each(function($product) {
    $productCategories = $categoriesProductMap[$product->vendor_code];
    $categoryIds = Category::whereIn('name', $productCategories)->select('id')->get()->pluck('id');
    $product->categories()->syncWithoutDetaching($categoryIds);
})

If you want to use your method with category checking, then use Eager Loading
An analogue of your Product::all(), only without the problem of N + 1 requests.