Laravel/Mysql - Manipulate large data

Question

Dears,

Actually, I would like to know the best solution to manipulate BIG DATA in LARAVEL/MYSQL.

In my system, am uploading daily excel file (5K rows) into my DB, in case I find the same row in my table so I don't insert it, and if I find the same row, I changed the uploaded date in my DB.

Every time I upload the excel, am checking each row if exist in my table (table contain > 50K) with a normal array like the below

           $res = policies::where('phone', '=', $row['phone'])
                      ->where('draft_no', '=', $row['draftno'])
                      ->where('due_date', '=', $duedate)
                      ->where('status', '=', $stat)
                      ->where('bord_date', '=', $borddate)
                      ->where('amount', '=', $row['amnt'])
                      ->where('remarks', '=', $row['remarks'])
                      ->exists();

                    if(!$res) { 
                   // insert row ($table->save())
                    }
                    else {
                      //update uploaded date to this row.
                    }

this process takes lots of time bcz each time is checking the table. I tried to use array_chunk in order to insert, but still, the load is big (15 min to 20 min) to finish

Your advice are highly appreciated.

Thanks

Probably storing a hash (as Leonardo suggested) somewhere is a way out. Maybe Redis will be an option — Tarasovych, May 30 '19 at 07:34

score 2 · Accepted Answer · answered May 30 '19 at 07:10

2

You can create an hash of each row and store along with the row. Then check only the row with given hash.

For instance try this stub of code

foreach ($rows as $row) {
    $hash = md5($row['phone'] . $row['draft_no'] . $row['due_date'] ...);
    $res = Policiess::where('hash', $hash);
    if (!$res) {
         // create a new row and store the `$hash` in `hash` column
    } else {
         //update uploaded date to this row
    }
}

answered May 30 '19 at 07:10

Leonardo Rossi

2,922
2
20
28

1

Worked like CHARM !! Thank you for this beautiful solution. – Collect KASSAB May 30 '19 at 08:18
Just out of curiosity, how long does it take with this approach to process the entire file? – Leonardo Rossi May 30 '19 at 09:27
it was taking about 13 minutes, now it took up to 2.5 minutes :) – Collect KASSAB May 30 '19 at 10:44
Try also to add an index on that coulmn, it should speed up : https://stackoverflow.com/a/3002635/562097 – Leonardo Rossi May 30 '19 at 11:05

score 0 · Answer 2 · answered May 30 '19 at 07:10

0

if you not add new record same excel then not need check in database. but you add same excel file new record then insert all record after update this excel file

answered May 30 '19 at 07:10

Ganesh Khartode

71
5

score 0 · Answer 3 · answered May 30 '19 at 07:13

Why don't use laravel default eloquent method updateOrCreate. Hope you already read about it if you don't you can read it from documentation other-creation-methods.

Let me explain what actually it does.

It Accepts an array of values and checks value is already in the database if it's already in the database it will update these values and also update column updated_at or if it's not already in the database, it will create a new entry in the table.

See eg below :-

policies::updateOrCreate(['value'=>1,'value'=>2,'so on...']);

and dont forget to add protected $fillable = [your column] , Because it's use $fillable for this.

Laravel/Mysql - Manipulate large data

3 Answers3