Doctrine combine JoinTable inserts

Question

I have two entites:

Voucher
Support

Those entities have a join table called

 voucher_support

When i add 1000 existing supports to a new voucher, it will call 1000 INSERT queries at voucher_support to my SQL server. With 50.000 for sure also 50.000 inserts.

How can i achieve that doctrine just makes a single INSERT and improve my performance?

EDIT:

I think bulk insertation is not the solution. Here is my current Code for better understanding:

$voucher = new Voucher();
$voucher->setCreatedAt(new \DateTime());
$supports = $this->em->getRepository('Support')->getOpen();
foreach($supports as $support){
    // this generates for each support a INSERT in join table.
    $voucher->addSupport($support);
}
$this->em->persist($voucher);
$this->em->flush();

score 0 · Answer 1 · answered May 30 '17 at 09:58

0

Read Bulk inserts part of Doctrine2 documentation. It suggests using flush() for every 'batch'. You should also consider wrapping it with transaction.

<?php
$batchSize = 20;
for ($i = 1; $i <= 10000; ++$i) {
    $user = new CmsUser;
    $user->setStatus('user');
    $user->setUsername('user' . $i);
    $user->setName('Mr.Smith-' . $i);
    $em->persist($user);
    if (($i % $batchSize) === 0) {
        $em->flush();
        $em->clear(); // Detaches all objects from Doctrine!
    }
}
$em->flush(); //Persist objects that did not make up an entire batch
$em->clear();

answered May 30 '17 at 09:58

Ion Bazan

723
6
16

i know this approach and using it already in other parts. But i do not insert multiple vouchers or supports. The Join Table insertation is the issue. – develth May 30 '17 at 10:04
You can use native query with `INSERT INTO ... SELECT FROM ...` to populate rows into join table. – Ion Bazan May 30 '17 at 10:24

score -1 · Answer 2 · answered May 30 '17 at 10:04

Doctrine2 does not allow you to combine multiple INSERT statements into one:

Some people seem to be wondering why Doctrine does not use multi-inserts (insert into (...) values (...), (...), (...), ...

First of all, this syntax is only supported on mysql and newer postgresql versions. Secondly, there is no easy way to get hold of all the generated identifiers in such a multi-insert when using AUTO_INCREMENT or SERIAL and an ORM needs the identifiers for identity management of the objects. Lastly, insert performance is rarely the bottleneck of an ORM. Normal inserts are more than fast enough for most situations and if you really want to do fast bulk inserts, then a multi-insert is not the best way anyway, i.e. Postgres COPY or Mysql LOAD DATA INFILE are several orders of magnitude faster.

These are the reasons why it is not worth the effort to implement an abstraction that performs multi-inserts on mysql and postgresql in an ORM. You can read more about Doctrine2 batch processing here: http://www.doctrine-project.org/blog/doctrine2-batch-processing.html

You can either switch to DBAL or resort to processing your data in small batches by flushing your entity manager after a set amount of inserts:

$batchSize = 20;

foreach ($items as $i => $item) {
     $product = new Product($item['datas']);

     $em->persist($product);

     // flush everything to the database every 20 inserts
     if (($i % $batchSize) == 0) {
         $em->flush();
         $em->clear();
    }
}

// flush the remaining objects
$em->flush();
$em->clear();

btw. i know the original post: https://stackoverflow.com/a/18655238/1101500 — develth, May 30 '17 at 10:06

Doctrine combine JoinTable inserts

2 Answers2