How to insert big data on the laravel?

Question

I am using laravel 5.6

My script to insert big data is like this :

...
$insert_data = [];
foreach ($json['value'] as $value) {
    $posting_date = Carbon::parse($value['Posting_Date']);
    $posting_date = $posting_date->format('Y-m-d');
    $data = [
        'item_no'                   => $value['Item_No'],
        'entry_no'                  => $value['Entry_No'], 
        'document_no'               => $value['Document_No'],
        'posting_date'              => $posting_date,
        ....
    ];
    $insert_data[] = $data;
}
\DB::table('items_details')->insert($insert_data);

I have tried to insert 100 record with the script, it works. It successfully insert data

But if I try to insert 50000 record with the script, it becomes very slow. I've waited about 10 minutes and it did not work. There exist error like this :

504 Gateway Time-out

How can I solve this problem?

You can chunk the data by using Laravel chunk functions. Maybe this post will help you [insert big data](https://laracasts.com/discuss/channels/laravel/what-is-the-fastest-way-to-insert-millions-of-records-in-laravel-51) — Dennis, Jul 23 '18 at 22:12
You could also use Laravels queue system and just creat 50,000 jobs for each insertion. But if you insist on sending large quantities, you need to increase the PHP timeout (you will want to google what your server stack is for instructions) — Rob Fonseca, Jul 24 '18 at 00:43
Yeah, chunk won't really help you in this case. `chunk` will help solve issues with memory usage, but not for your app running for too long. Like Rob suggested, look into using the queue and creating a job fo each individual item. Then you can run a heap of workers to crunch the data for you. — Dwight, Jul 24 '18 at 04:40
@Dwight I'm still confused. Try to answer this question with a detail answer(with the script) — moses toh, Jul 24 '18 at 07:36
@Rob Fonseca I'm still confused. Try to answer this question with a detail answer(with the script) — moses toh, Jul 24 '18 at 07:37
@SuccessMan try to read the docs of laravel, it will help you a lot to understand the use of queues. https://laravel.com/docs/5.6/queues — Dennis, Jul 24 '18 at 07:43
@Dwight thanks, I didn't know that, I learned something too today ;) — Dennis, Jul 24 '18 at 07:44
@haakym Please answer this question using queue. So there exist some option — moses toh, Jul 25 '18 at 06:33

score 34 · Accepted Answer · edited Jul 24 '18 at 09:29

34

As it was stated, chunks won't really help you in this case if it is a time execution problem. I think that bulk insert you are trying to use cannot handle that amount of data , so I see 2 options:

1 - Reorganise your code to properly use chunks, this will look something like this:

$insert_data = [];

foreach ($json['value'] as $value) {
    $posting_date = Carbon::parse($value['Posting_Date']);

    $posting_date = $posting_date->format('Y-m-d');

    $data = [
        'item_no'                   => $value['Item_No'],
        'entry_no'                  => $value['Entry_No'], 
        'document_no'               => $value['Document_No'],
        'posting_date'              => $posting_date,
        ....
    ];

    $insert_data[] = $data;
}

$insert_data = collect($insert_data); // Make a collection to use the chunk method

// it will chunk the dataset in smaller collections containing 500 values each. 
// Play with the value to get best result
$chunks = $insert_data->chunk(500);

foreach ($chunks as $chunk)
{
   \DB::table('items_details')->insert($chunk->toArray());
}

This way your bulk insert will contain less data, and be able to process it in a rather quick way.

2 - In case your host supports runtime overloads, you can add a directive right before the code starts to execute :

ini_set('max_execution_time', 120 ) ; // time in seconds

$insert_data = [];

foreach ($json['value'] as $value)
{
   ...
}

To read more go to the official docs

edited Jul 24 '18 at 09:29

haakym

12,050
12
70
98

answered Jul 24 '18 at 07:49

Vit Kos

5,535
5
44
58

With the second option you can still use chunks to avoid the big bulk chunk – Vit Kos Jul 24 '18 at 07:50
Thanks for your answer. I will try it. Please update your answer with chunk. So there exist some option – moses toh Jul 24 '18 at 08:38
I had try your script. There exist error like this : `Argument 1 passed to Illuminate\Database\Query\Builder::insert() must be of the type array, object given` – moses toh Jul 24 '18 at 08:47
@SuccessMan just use the ```->toArray()``` method – Vit Kos Jul 24 '18 at 09:05
UPDATED ^^. Check the chunk – Vit Kos Jul 24 '18 at 09:06
Ok. Your second option, it is the same as the answer in my question. What's different? Actually I have added this `ini_set('max_execution_time', 120 ) ;` too? – moses toh Jul 24 '18 at 09:12
@VitKos stackoverflow doesn't support markdown you have to indent your text for code blocks – haakym Jul 24 '18 at 09:30
@SuccessMan you can use it with whatever code you like, your version or chunk version - you decide. I just pointed out how to enlarge the execution time in runtime – Vit Kos Jul 24 '18 at 11:10
@Vit Kos I had try your option use chunk and it works. So I receive your answer. Thanks – moses toh Jul 25 '18 at 06:34

score 5 · Answer 2 · answered Aug 11 '19 at 19:37

It makes no sense to use an array and then convert it to a collection.

We can get rid of arrays.

$insert_data = collect();

foreach ($json['value'] as $value) {
    $posting_date = Carbon::parse($value['Posting_Date']);

    $posting_date = $posting_date->format('Y-m-d');

    $insert_data->push([
        'item_no'                   => $value['Item_No'],
        'entry_no'                  => $value['Entry_No'], 
        'document_no'               => $value['Document_No'],
        'posting_date'              => $posting_date,
        ....
    ]);
}

foreach ($insert_data->chunk(500) as $chunk)
{
   \DB::table('items_details')->insert($chunk->toArray());
}

score 2 · Answer 3 · answered Feb 20 '20 at 06:16

Here is very good and very Fast Insert data solution

$no_of_data = 1000000;
$test_data = array();
for ($i = 0; $i < $no_of_data; $i++){
    $test_data[$i]['number'] = "1234567890";
    $test_data[$i]['message'] = "Test Data";
    $test_data[$i]['status'] = "Delivered";
}
$chunk_data = array_chunk($test_data, 1000);
if (isset($chunk_data) && !empty($chunk_data)) {
   foreach ($chunk_data as $chunk_data_val) {
    DB::table('messages')->insert($chunk_data_val);
  }
}

score 2 · Answer 4 · answered Mar 26 '21 at 01:55

I used the code below to check the update or insert data of 11 thousand rows. I hope it useful for you.

$insert_data = [];
for ($i=0; $i < 11000; $i++) { 
    $data = [
        'id' =>'user_'.$i,
        'fullname' => 'Pixs Nguyen',
        'username' => 'abc@gmail.com',
        'timestamp' => '2020-03-23 08:12:00',
    ];
    $insert_data[] = $data;
}

$insert_data = collect($insert_data); // Make a collection to use the chunk method

// it will chunk the dataset in smaller collections containing 500 values each. 
// Play with the value to get best result
$accounts = $insert_data->chunk(500);
// In the case of updating or inserting you will take about 35 seconds to execute the code below
for ($i=0; $i < count($accounts); $i++) { 
    foreach ($accounts[$i] as $key => $account)
    {
        DB::table('yourTable')->updateOrInsert(['id'=>$account['id']],$account);
    }
}
// In the case of inserting you only take about 0.35 seconds to execute the code below
foreach ($accounts as $key => $account)
{
    DB::table('yourTable')->insert($account->toArray());
}

Thanks for adding time comparison too. – Hana Hasanah Oct 05 '22 at 08:40 — Hana Hasanah, Oct 05 '22 at 08:40

How to insert big data on the laravel?

4 Answers4

Linked