How to Optimize Import Process for very large data for Laravel?

Question

I have the functionality to import a very large amount of records around 5 million.

I have to make entries for related tables also simultaneously in the import process.

I have to make bunch insert queries for new entries and taking care of all queries and also making processes in chunks.

What are the other ways to speed up the process?

Perhaps using a separate queue server which just slowly runs through jobs? Could use redis for that. — , Jun 06 '19 at 10:28
@VivekSolanki What is your reasoning for not wanting to use redis? — thisiskelvin, Jun 07 '19 at 13:09
@thisiskelvin, because I am using simple database queries. I don't want to use external libraries. I want my process using simply optimized queries. — Vivek Solanki, Jun 08 '19 at 02:39
Understandable. The query is simple yes but not the entire operation. It may be faster to usethe DB facade as you can insert multiple records at once using the insert method. — thisiskelvin, Jun 08 '19 at 05:42
@thisiskelvin, yes, this is a better way, but I have already applied this and it gave me better performance. — Vivek Solanki, Jun 10 '19 at 05:33
For starter don't do it in sync with the API process. It may and it will take more than 10 seconds. IMO, dump the file to a temp storage (s3 or on disk). Use Laravel Job, to trigger the insertion process. On UI side, show a loader. Maintain the process related (no. processed) in DB. Your question shouldn't be "How to optimize Eloquent query" but "How to optimize the process" — Ashwani Agarwal, Jun 11 '19 at 05:24
@AshwaniAgarwal, Yes agree with the solution. I have already set command for process execution. but need to improve processing time. — Vivek Solanki, Jun 11 '19 at 05:47
You need to be more descriptive. What are exact things you’re doing. Where it’s taking more time (queries or something else) — Ashwani Agarwal, Jun 11 '19 at 05:50
Queries take more time because multiple table insertion queries and in process. — Vivek Solanki, Jun 11 '19 at 05:54
I have solved a similar issue due to time-out. Now it's working fine with Laravel scheduler, — manu, Jun 12 '19 at 14:10
You may want to increase you're max_execution_time and increase memory_limit as well to get rid of session timeout, you can also use chunks — Vipertecpro, Jun 12 '19 at 14:34
@VivekSolanki you must try chunks if you have mulitple dependancies with table. Else I suggest to convert `csv|xls` to `xml` and import that. — Vikash Pathak, Jun 17 '19 at 11:15
Also can you please add sample of the relational records that you would like to insert into multilpe tables... that could help to understand the complexity. — Vikash Pathak, Jun 17 '19 at 11:16

PtrTon · Answer 1 · 2019-06-17T22:03:36.947

5

So to summarize for people who don't bother looking through all comments separately:

Use the DB facade Laravel offers instead of Eloquent models for improved performance.
Run the import process in batches rather than importing all data at once.
Disble query logging to save memory usage.
Run the import in a background job, not during a web request.

Besides the points already made you could consider:

First run a job which reads your input xlsx/csv file in batches of 50 lines (or some other sensible number). Make sure you do not keep all lines in memory. Then for each batch of lines create a new job. So you'll have a 2-step import.
When creating separate jobs for each batch, you can run those jobs concurrently (= multiple queue workers at the same time).
If you have users waiting for the import to be finished, make sure you show a progress bar or at least an animated loader of some kind. Although this does not speed up the process, it will give them an indication work is being done.
You could also leverage database transactions for running multiple database queries at once (credit to Philippe Thiers for this one)

edited Jun 17 '19 at 22:03

answered Jun 15 '19 at 07:43

PtrTon

3,705
2
14
24

I have tried the Queue for my process. but is do not run simultaneously, It executes one after another. – Vivek Solanki Jun 15 '19 at 12:22
You'll have to run multiple commandlines at once for this to do so. In a production environment [Supervisor is advised](https://laravel.com/docs/5.8/queues#supervisor-configuration). This is a tool which keeps certain processes (in your case the queue worker) running at all times. If a job fails, errors or runs out of memory and stops it will be rebooted by supervisor. The configuration for supervisor allows you to specify `numprocs`, which is the number of simultaneous processes. – PtrTon Jun 15 '19 at 13:49
1

Add to the list, the use of transaction, `DB::transaction(closure);` or `\DB::beginTransaction; try{ /* code */ \Db::commit; } catch(\Exception $e){ \Db::rollback; } ` – Philippe Thiers Jun 17 '19 at 21:43

manu · Answer 2 · 2019-06-12T14:35:22.703

4

(copied from laracasts) This will probably help too:

DB::connection()->disableQueryLog();

"By default, Laravel keeps a log in memory of all queries that have been run for the current request. However, in some cases, such as when inserting a large number of rows, this can cause the application to use excess memory."

edited Jun 12 '19 at 14:35

answered Jun 12 '19 at 14:23

manu

351
2
15

1

This is for `Laravel <= 4.2`, on the later version it's disabled by default... so it won't impact with `laravel 5.8` as mentioned in question requirements. – Vikash Pathak Jun 17 '19 at 11:09

bhavinG · Answer 3 · 2019-06-25T10:30:26.877

0

Use:

Process data in Chunks Use Laravel queues

Use https://docs.laravel-excel.com/3.1/imports/ Example for user model binding

namespace App\Imports;

use App\User;
use Maatwebsite\Excel\Concerns\ToModel;
use Maatwebsite\Excel\Concerns\Importable;

class UsersImport implements ToModel
{
    use Importable;

  public function model(array $row)
  {
      return new User([
       'name'     => $row[0],
       'email'    => $row[1],
       'password' => Hash::make($row[2]),
    ]);
  }
}

In Controller

(new UsersImport)->import('users.xlsx', 'local', \Maatwebsite\Excel\Excel::XLSX);

edited Jun 25 '19 at 10:30

answered Jun 24 '19 at 11:42

bhavinG

19
3

1

please provide the relevant code from the link related to question. Avoid external links so that even if the link die.. we have the info in the answers – Himanshu Bansal Jun 24 '19 at 11:54
Laravel excel is not so flexible for large data. I have already tried laravel excel. – Vivek Solanki Jun 25 '19 at 04:40
I am using it for bulk processing it have many new features like transaction,queues,chunks processing, Model Binding which is most significant feature for validation and insertion also provide custom integration. i agree excel is not so flexible but laravel-excel make it easier for you – bhavinG Jun 25 '19 at 04:52
@VivekSolanki which version you have used? can you share any problem statement so i can share my feedback. – bhavinG Jun 25 '19 at 04:55
@VivekSolanki If speed is the issue than you should go for CSV instead , however model binding with help to achieve better speed. – bhavinG Jun 25 '19 at 09:57

How to Optimize Import Process for very large data for Laravel?

3 Answers3