insert query data into same sql query

Question

I am trying to insert the data of a query, currently I am doing it with the eloquent ORM of laravel, but it takes a long time since there are approximately 120k of records, so I would like to know if they can help me as it can be done in the same query

This is the query:

$var = DB::select("
select selrs.*, sim.emin, sim.emax 
from (select rac.*, sup.zona, sup.sitio, sup.manejo 
from datos_rendim_actual rac
left join super sup on (sup.codigo = (rac.fundo::int8 * 1000000 + rac.rodal::int8 ))) selrs
left join sitman sim on (sim.sitio = selrs.sitio and sim.manejo = selrs.manejo)
where  selrs.edad >= sim.emin and  selrs.edad <= sim.emax
 ");

This is the dd of $var

array:123921 [▼
  0 => {#813 ▼
    +"id": 255214
    +"fundo": 101
    +"rodal": 196826
    +"codigo": null
    +"edad": 10
    +"densidad": "1019"
    +"vol_prod1": "0"
    +"vol_prod2": "113.95"
    +"created_at": null
    +"updated_at": null
    +"zona": 5
    +"sitio": 1
    +"manejo": 7
    +"emin": 10
    +"emax": 20
  }

So This is how I insert them:

foreach ($var as $lista) {
          $rendimA = new RendimActual;
          $rendimA->codigo = $lista->fundo.$lista->rodal;
          $rendimA->edad = $lista->edad;
          $rendimA->densidad = $lista->densidad;
          $rendimA->vol_prod1 = $lista->vol_prod1;
          $rendimA->vol_prod2 = $lista->vol_prod2;
          $rendimA->vol_prod3 = $lista->vol_prod3;
          $rendimA->save();  
    
    }

The fields that I have to fill are in the rendim_actual table and are the following:

• codigo = concat(fundo, rodal) from $var
• edad= from $var
• densidad = from $var
• vol_prod1 to n (actually there are 36 but as an example just leave 3) from $var

in terms of time insert by Eloquent takes about 15 minutes, I hope you can help me, ty

I am using laravel-5.8 and postgresql

Answer 1

I don't have much experience dealing with large databases, but in your solution you will be making 120k separate inserts, which will not be efficient.

If the records in your $var array are already in the correct format from your table you can try using the query builder's insert method.

    // Not certain what your table name is
    DB::table('rendim_actual')->insert($var);
    DB::commit();

If the $var array becomes soo big that you cannot insert all data in one insert, you can look into this SO Question about chunking inserts.

Answer 2

What you are currently doing is running an insert query for each RendimActual. What you should be doing is bulk insert via ::insert()

insert()

This will insert 1 query with 120K values

$insert = [];
foreach ($var as $lista) {
    $insert[] = [
        'codigo' => $lista->fundo . $lista->rodal,
        'edad' => $lista->edad,
        'densidad' => $lista->densidad,
        'vol_prod1' => $lista->vol_prod1,
        'vol_prod2' => $lista->vol_prod2,
        'vol_prod3' => $lista->vol_prod3
    ];
}
RendimActual::insert($insert);

chunk()

Since you are inserting 120K values inside 1 query, there's a chance of consuming a lot of memory. So, it better to chunk() the data if 2000 rows and insert those 2K first. Even though you will be running 120K/2K = 60 queries ... at least this way each query will not be consuming memory compared to 1 big (120K) insert query.

foreach (array_chunk($insert, 2000) as $inst) {
    RendimActual::insert($inst);
}

Keep me posted in the comments below. Cheers!