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?