Bulk insert into 2 tables

Hi, i’m trying to implement a bulk insert in my application and don’t know what’s the proper way to do it.

I want to insert multiple records in two tables at once (1-10.000 aproximately), so i decided to use Yii::$app->db->createCommand()->insert() to do it (seems like this is better suited for this kind of task than ActiveRecord, but not really sure). So at the moment i’m doing something like this:




if(!empty($ids)){

            date_default_timezone_set("America/Santiago");


            $flag = false;

            $transaction = $conexion->beginTransaction();

            try{

                foreach($ids as $id){

                    $comando_vinculo = $conexion->createCommand()->insert('vinculo_actividad', [

                        'act_id' => $evento,

                        'cargo_id' => $id,

                        'detfact_id' => 0,

                        'conins_id' => 0,

                        'vincact_fechaing' => date('Y-m-d'),

                        'vincact_horaing' => date('H:i:s'),

                        'vincact_observaciones' => 'Asistente invitado intranet.',

                        'vincact_vigencia' => 1]);


                    $total = $comando_vinculo->execute();                


                    if($total == 1){

                        $ultimoId = $conexion->getLastInsertID();


                        $comando_historico = $conexion->createCommand()->insert('historico_vinculoact', [

                            'tipvin_id' => 1,

                            'vincact_id' => $ultimoId,

                            'estvin_id' => 3,

                            'taract_id' => 0,

                            'histvin_fecha' => date('Y-m-d'),

                            'histvin_orden' => 0,

                            'histvin_precio' => 0,

                            'histvin_vigencia' => 1]);


                        $total_hist = $comando_historico->execute();


                        if($total_hist == 1){

                            $flag = true;

                        }

                        else{

                            $flag = false;

                            $transaction->rollback();

                            break;

                        }

                    } 

                }


                if($flag){

                    $transaction->commit();

                }

            }catch(\Exception $e){

                $transaction->rollback();

                throw $e;

            }catch(\Throwable $e){

                $transaction->rollback();

                throw $e;

            } 

        }



Where $ids is an array of foreign keys from another table to be inserted into the new table and $evento is an id from some other table. Right now this works and inserts the data into the corresponding tables, but when i try to insert a high amount of rows (more than 1000) it gives me PHP fatal error: maximum execution time of 30 seconds exceeded. I know i can modify the maximum execution time to “solve” this issue, but i’m wondering what’s the best way to implement this so the inserts takes the less amount of time possible.

I was reading the docs and found out about the batch insert methods, but don’t know if that’s what i should be using (never used batch insert before).

Thanks in advance for any help.

Schema caching? (perhaps)

http://www.yiiframework.com/doc-2.0/yii-db-connection.html#$schemaCache-detail

I guess this may be relevant

http://www.yiiframework.com/doc-2.0/guide-caching-data.html#cache-components

Yes you should be using the batchInsert function

http://www.yiiframework.com/doc-2.0/yii-db-command.html#batchInsert()-detail

Thanks for the answers, will be looking and implementing schema caching for sure, seems like a must to have before the system goes on production.

I’ve implemented batch insert and the execution time is reduced GREATLY (from 2+ minute query time to something like 10 seconds). This was for a 4865 rows insert. However i need to also insert records into another table for each insert i do on the first table (this table has a relation with the first one, so I need the id of the newly created record in the first table). I was doing that before with $ultimoId = $conexion->getLastInsertID(); for each insert on the first table but i can’t do that with batchInsert in the same way (getLastInsertID() returns only the first inserted ID).

I’ve read some post about this issue like this and it seems like there’s not really a good way to get all the recently created ids. I would really like to stick to batchInsert because of how much it improves performance, unless there’s really no other way around it.