Low speed of saving to the MySQL (PHP - Yii2)

Question

I am trying to import data into MySQL from a JSON file.

public function importProductFile($file, $return = true)
    {    
        $products = json_decode($file);
        $dubTableName = Product::tableName() . "_dub";
        $start = time();
        if ($this->db->createDuplicateTable(Product::tableName(), $dubTableName)) {
    
            $i = 0;
    
            foreach ($products as $product) {
                $i++;
                $item = new Product_dub();
                $item->id_1c_product = $product->id;
                $category = Category_dub::findOne(['id_1c_category' => $product->category_id]);
    
                if (!$category) {
                    Answer::failure("В этом товаре отсутствует категория или такой категории не существует: " . $product->title);
                }
    
                $item->category_id = $category->id;
                $item->title = $product->title;
                $brand = Brands_dub::findOne(['id_1c_brand' => $product->brand_id]);
    
                if (!$brand) {
                    Answer::failure("В этом товаре отсутствует бренд/изготовитель: " . $product->title);
                }
    
                $item->brand_id = $brand->id;
                // $item->shortdesc = $product->shortdesc;
                $item->content1 = $product->content1;
                $item->content2 = $product->content2;
                $item->content3 = $product->content3;
                $item->link_order = $product->link_order;
                $item->img = $product->img;
                $item->in_stock = $product->in_stock ? 1 : 0;
                $item->is_popular = $product->is_popular ? 1 : 0;
    
                if (!$item->save()) {
                    Answer::failure("Не удалось импортировать: Проверьте данные в " . $product->title);
                }
    
                if ($i == 200) {
                    break;
                }
            }
        }
    
        $finish = time();
        $res = $finish - $start . "sec. ";
    
        if ($return) {
            echo $res;
            Answer::success();
        }
    }

There are about 1100 objects in my JSON file. It takes 7 seconds to add 100 rows to the database. Adding 200 lines - 15 seconds. 300 = 33 sec, 400 = 58 sec. Why does it slow down over time and how to speed up this process?

I do everything on the local OpenServer server. PHP 7.2 version, Xeon 2620v3 processor, 16 GB DDR4, HDD.

UPD 1.

"Can you try not importing and just determine the speed of reading" - I comment $item->save() and get 1-2 sec for all of JSON files. "In each iteration of your cycle you are running 2 DB queries to load category and brand." - I tried to delete these lines for test - but the result was 1-2 seconds faster than with 2 DB queries.

UPD 2.

I changed save() to insert() - the speed has increased. Now all JSON (1107 lines) is imported in 40 seconds.

Are there faster ways to load ready-made data from JSON into the database? What if there are 100 thousand lines or a million? Is it normal practice to wait a few hours?

public function importProductFile($file, $return = true)
    {    
        $products = json_decode($file);
        $dubTableName = Product::tableName() . "_dub";
        $start = time();

        if ($this->db->createDuplicateTable(Product::tableName(), $dubTableName)) {
            $start = time();
            $categoryMap = Category_dub::find()->select(['id', 'id_1c_category'])->indexBy('id_1c_category')->column();
            $brandMap = Brands_dub::find()->select(['id', 'id_1c_brand'])->indexBy('id_1c_brand')->column();

            foreach ($products as $product) {
                Yii::$app->db->createCommand()->insert('product_dub', [
                    'id_1c_product' => $product->id,
                    'category_id' => $categoryMap[$product->category_id] ?? '0',
                    'title' => $product->title,
                    'brand_id' => $brandMap[$product->brand_id] ?? 'No brand',
                    'content1' => $product->content1,
                    'content2' => $product->content2,
                    'content3' => $product->content3,
                    'link_order' => $product->link_order,
                    'img' => $product->img ?? 'no-image.png',
                    'in_stock' => $product->in_stock ? 1 : 0,
                    'is_popular' => $product->is_popular ? 1 : 0,
                ])->execute();
            }
        }
        }
    
        $finish = time();
        $res = $finish - $start . "sec. ";
    
        if ($return) {
            echo $res;
            Answer::success();
        }
    }

Answer 1

I changed save() to insert() - the speed has increased. Now all JSON (1107 lines) is imported in 40 seconds. Are there faster ways to load ready-made data from JSON into the database? What if there are 100 thousand lines or a million? Is it normal practice to wait a few hours?

public function importProductFile($file, $return = true)
    {    
        $products = json_decode($file);
        $dubTableName = Product::tableName() . "_dub";
        $start = time();

        if ($this->db->createDuplicateTable(Product::tableName(), $dubTableName)) {
            $start = time();
            $categoryMap = Category_dub::find()->select(['id', 'id_1c_category'])->indexBy('id_1c_category')->column();
            $brandMap = Brands_dub::find()->select(['id', 'id_1c_brand'])->indexBy('id_1c_brand')->column();

            foreach ($products as $product) {
                Yii::$app->db->createCommand()->insert('product_dub', [
                    'id_1c_product' => $product->id,
                    'category_id' => $categoryMap[$product->category_id] ?? '0',
                    'title' => $product->title,
                    'brand_id' => $brandMap[$product->brand_id] ?? 'No brand',
                    'content1' => $product->content1,
                    'content2' => $product->content2,
                    'content3' => $product->content3,
                    'link_order' => $product->link_order,
                    'img' => $product->img ?? 'no-image.png',
                    'in_stock' => $product->in_stock ? 1 : 0,
                    'is_popular' => $product->is_popular ? 1 : 0,
                ])->execute();
            }
        }
        }
    
        $finish = time();
        $res = $finish - $start . "sec. ";
    
        if ($return) {
            echo $res;
            Answer::success();
        }
    }

Answer 2

You can use the bulk insert as mentioned in this answer and Yii2 docs. Using this bulk insert, you need to remember that the event will not be triggered.

Yii::$app->db->createCommand()->batchInsert('product_dub', array_keys(reset($products)), $products)->execute();