Magento 2 Product import/sync Speed performance 1M records per minute.

One Fortune 500 client asked to build a fast import to magento 2. Default import is too slow for them. They need to import 5 000 000 changes every 15 minutes by corn from CVS from the internal ERP. We used LAragento with this project, but for this use case, we decided not to use Eloquent ORM and run RAW SQLs. There is our performance test on the local environment (on prod, it is much faster)

LAraGento is a collection of Model classes that allows you to get data directly from a Magento 2 database using modern Eloquent ORM.

Laragento is built on top of Eloquent ORM (from the Laravel framework), which provides a fluent interface to connect and get data directly from a Magento database.

How to up speed for insert?

We have a simple array from CSV/XLS for insertion into the Magento DB. It adds we batched data by 50,000 entries at a time. In one table, more than 2 million records must be entered in a database.

The question is how to speed up the introduction of this data.

I made tests for that.

When I use the next construction, I get 23 400 records per minute.

foreach ($records as $record)
{
DB::table($table)->insert($record);
}

When I use the next construction 50 000 records at a time, I get 40 records per min. I tried to reduce the number of records at a time, but the result is approximately the same

DB::table($table)->insert([$records]);

When I use the next construction, I get 230 000 records per min.

DB::insert('INSERT INTO '.$prefix.$table.' ('.$columns.') VALUES '.$values);

//or 

DB::collection('collection_name')->insert($array);

So this one is the best way to insert. It allows us to improve 1M records per minute on a faster production DB server.

Make sure your SQL Log is disabled

DB::disableQueryLog();  
// or
DB::connection('connection-name')->disableQueryLog();

It could be optimized if many workers process and insert the queue in parallel.