Symfony mass hydration from one repository to another

Question

I have the next code that is executed ok but it takes too much time... is there any way i can optomize the this with createQueryBuilder and update methods?

here is the current function:

 private function getCsv()
    {

        $file = 'Diva_tarif.csv';
        Message::write("Retriving $file", 3);
        $file = $this->root_data . $file;

        $serializer = new Serializer([new ObjectNormalizer()], [new CsvEncoder(';')]);
        $counter = 0;
        $batch_size = 30;
        if (($handle = fopen($file, 'r')) !== false) {
            $header = fgets($handle);
            while (($line = fgets($handle)) !== false) {

                $line = $serializer->decode($header . $line, 'csv');
                $repo = $this->em->getRepository(Article::class);
                if(isset($line['DOS'])){
                    $counter ++;

                    $arts = $repo->findBy(array('dos' => $line['DOS'], 'ref' => $line['REF']));

                    $used_metas = [
                        'TACOD',
                        'VENUN',
                        'DEV',
                        'PUB',
                        'ALZTXREMMAX',
                        'ALZTXREMMAXLALPHA',                    
                    ];

                    foreach($arts as $art){                        
                        foreach ($used_metas as $metakey ) {

                            $meta_obj = new ArticleMeta();
                            $meta_obj->setName($metakey);
                            $meta_obj->setValue($line[$metakey]);
                            $meta_obj->setArticle($art);
                            $this->em->persist($meta_obj);
                        }
                    }   
                    if (($counter % $batch_size) === 0) {                   
                        $this->em->flush();
                        $this->em->clear(); 
                    }

                    if(($counter % 500) == 0){                   
                        Message::write("$counter lines added", 4);
                    }
                }
            }
            Message::write("$counter lines added", 4);
            Message::write("Done", 3);
            $this->em->flush();
            $this->em->clear(); 
            fclose($handle);           
        }
    }

The objective is to get the Article and assign new values to the ArticleMeta. Any idea?

I'll suggest you some improvements points – Mcsky Sep 04 '18 at 15:27 — Mcsky, Sep 04 '18 at 15:27
What is the average of lines into your csv ? – Mcsky Sep 04 '18 at 15:45 — Mcsky, Sep 04 '18 at 15:45

Mcsky · Accepted Answer · 2018-09-04T16:01:05.203

You could use the CsvEncoder directly without the Serializer, you are using the decoding without the denormalization.

I distinguish 2 ways to improve performances

No code change

You should index the pair ref-dos into your database, this will really increase query time.

But the time to execute your script will grow with the number of lines of your file.

One big query

You could parse one time your file, extract all the dos and ref, and then build an array with pair ref-dos.

Example: [ABCD-1234, BCDE-2345, ... ]

After that you can do ONE big query to your database, and avoid the one query/line, that IMO is very time-consuming.

This way is less related to the number of lines because you're querying the database once.

This way IMO is the best one

Moreover you can declare $used_metas and $repo outside the while.

You're already using batching + entity manager cleanup, this is a good way while inserting many entities with Doctrine.

If you really want to go fast you could use raw SQL and avoid the doctrine entity hydration. But I don't recommend you this way

Symfony mass hydration from one repository to another

1 Answers1

No code change

One big query