CODEWITHSUNDEEP
phpdoctrine-ormsymfony4
jj-aa
jj-aa

Reputation: 1039

Migrating data with Doctrine becomes slow

I need to import data from one table in db A to another table in db B (same server) and I've chosen doctrine to import it.

I'm using a Symfony Commands and is all good for the first loop, just spends 0.04 secs, but then starts to become slower and slower and takes almost half an hour ...

I'm considering to build a shell script to call this Symfony command giving the offset ( I manually tried it and keeps same speed ). This is running in a docker service and the php service is around 100% CPU, however mysql service is 10%

Here part of the script:

class UserCommand extends Command
{
    ...
    protected function execute(InputInterface $input, OutputInterface $output)
    {
        $container = $this->getApplication()->getKernel()->getContainer();
        $this->doctrine = $container->get('doctrine');
        $this->em = $this->doctrine->getManager();
        $this->source = $this->doctrine->getConnection('source');

        $limit = self::SQL_LIMIT;
        $numRecords = 22690; // Hardcoded for debugging
        $loops = intval($numRecords / $limit);
        $numAccount = 0;
        for ($i = 0; $i < $loops; $i++){

            $offset = self::SQL_LIMIT * $i;
            $users = $this->fetchSourceUsers($offset);

            foreach ($users as $user) {
                try{

                    $numAccount++;
                    $this->persistSourceUser($user);
                    if (0 === ($numAccount % self::FLUSH_FREQUENCY)) {
                        $this->flushEntities($output);
                    }

                } catch(\Exception $e) {
                    //
                }
            } 
        }
        $this->flushEntities($output);
    }

    private function fetchSourceUsers(int $offset = 0): array
    {
        $sql = <<<'SQL'
        SELECT email, password, first_name
        FROM source.users
        ORDER by id ASC LIMIT ? OFFSET ?
SQL;

        $stmt = $this->source->prepare($sql);
        $stmt->bindValue(1, self::SQL_LIMIT, ParameterType::INTEGER);
        $stmt->bindValue(2, $offset, ParameterType::INTEGER);
        $stmt->execute();
        $users = $stmt->fetchAll();

        return $users;
    }
}

Upvotes: 2

Views: 1118

Answers (1)

malarzm
malarzm

Reputation: 2966

If the time it takes to flush is getting longer every other flush then you forgot to clear entity manager (which for batch jobs should happen after flush). Reason is that you keep accumulating entities in the entity manager and during every commit Doctrine is checking each and every one for changes (I assume you're using default change tracking).

I need to import data from one table in db A to another table in db B (same server) and I've chosen doctrine to import it.

Unless you have some complex logic related to adding users (i.e. application events, something happening on the other side of the app, basically need some other PHP code to be executed) then you've chosen poorly - Doctrine is not designed for batch processing (although it can do just fine if you really know what you're doing). For "simple" migration the best choice would be to go with DBAL.

Upvotes: 5

Related Questions