Doctrine insert many data

Viewed 4281

I'm working on an import of 300000 row inside many csv.

First I take csv and import every row into a table inside my database.

After I want to parse all row and insert into the right table with some relation that data.

So I have tried this:

    $qb = $this->entityManager->createQueryBuilder();
    $flows = $qb->select('flow')
        ->from('AppBundle:FlowAndata', 'flow')
        ->getQuery()
        ->getResult();

    $countRows = 0;
    foreach ($flows as $row) {
         //some check 
         $entity = new TestTable();
         $entity->setCode($row->getCode());
         //many other fields
         $this->entityManager->persist($entity);
         $this->entityManager->flush();
    }

In this case all procedure took about 5 seconds for every row!

Now If I add setMaxResults like this:

    $qb = $this->entityManager->createQueryBuilder();
    $flows = $qb->select('flow')
        ->from('AppBundle:FlowAndata', 'flow')
        ->setMaxResults(100)
        ->getQuery()
        ->getResult();

It took less than 1 second!

So I have thought to get all rows and split it into a recursive function with setMaxResult like this:

    $qb = $this->entityManager->createQueryBuilder();
    $flows = $qb->select('flow')
        ->from('AppBundle:FlowAndata', 'flow')
        ->getQuery()
        ->getResult();

    $countFlows = count($flows);
    $numberOfQuery = $countFlows / 100;

    for ($i = 0; $i <= $numberOfQuery; $i++) {
         $this->entityManager->clear();
         $qb = $this->entityManager->createQueryBuilder();
         $flows = $qb->select('flow')
            ->from('AppBundle:FlowAndata', 'flow')
            ->setFirstResult($i * 100)
            ->setMaxResults(100)
            ->getQuery()
            ->getResult();

    }

In this way I create many query splitted into 100 rows. Is a good practice or there is a better way to parse many rows and make an insert of It?

1 Answers
Related