Phalcon Task is very slow for migrating old tables to new tables?

I'm trying to migrate OLD database tables to my new database. New database have fresh and optimized structure.

I have an old table having 300k+ records and size is 11 GB. So now I have created a phalcon task which contain the below function for the mentioned table: This function in the task is taking almost 40+ hours to migrate the old data to new table. How can we optimize this task? I want to speedup the process!

    private function migrateEvaluationClients(){
    $this->writeLogInfo("Starting Evaluation Clients migration.", true);

    $progress = 0;
    $errors = 0;
    $total = $this->getEvaluationClientsTotal();
    $skipped = $this->getEvaluationClientsMigratedCount();
    $migrated = 0;

    $query = $this->getMySqlConnection()->query("SELECT * FROM old_evaluation_client_migration WHERE migrated = 0");
    if($query->num_rows == 0){
        $this->writeLogWarning("No Evaluation Clients found for migration.");
        return;
    }

    while($oldEvaluationClient = $query->fetch_assoc()){
        // Get the old evaluation client id
        $oldEvaluationClientId = $oldEvaluationClient['id'];
        $progress++;
        try{
            $EbEvaluationClient = EbEvaluationClient::getByExternalRefKeyAndValue('evaluation_client', $oldEvaluationClientId);
            // Check if the client data was already migrated before
            if($EbEvaluationClient){
                $skipped++;
            } else {
                $EvaluationClientUserGroup = CxUserGroup::findFirstByName(self::EB_EVALUATION_CLIENT_USER_GROUP_NAME);

                // Get the old record email and check if it is valid
                $emailAddress = CxHelper::arrayGetValueByKey($oldEvaluationClient, 'email');
                if(!$emailAddress || !filter_var($emailAddress, FILTER_VALIDATE_EMAIL)){
                    $this->writeLogError("Evaluation Client (ID: $oldEvaluationClientId): skipped because of missing or invalid email ($emailAddress)", true);
                    $errors++;
                } else {
                    // Get existing CX User with specified email (if any)
                    $extendedUser = CxUserExtension::findFirstByEmail($emailAddress);
                    if($extendedUser){
                        // Add external reference if not already set
                        $extendedUser->save();
                        $this->writeLogWarning("Evaluation Client (ID: $oldEvaluationClientId): skipping CX User creation because already exists");
                    }else{
                        $this->writeLogInfo("Evaluation Client (ID: $oldEvaluationClientId): creating new CxUser record");
                        $extendedUser = new CxUserExtension();
                        $userAuthProvider->save();
                    }
                    // Add the Evaluation Client user group (if not already added)
                    if(!$extendedUser->isMemberOfGroups(array($EvaluationClientUserGroup->getId()))){
                        $extendedUser->addCxUserGroup($EvaluationClientUserGroup);
                        $extendedUser->save();
                    }
                    $EvaluationClient = new EbEvaluationClient();
                    $EvaluationClient->setAge($oldEvaluationClient['age']);
                    switch($oldEvaluationClient['gender']){
                        case 'male':
                            $EvaluationClient->setGender(new CxGender(CxGender::MALE));
                            break;
                        case 'female':
                            $EvaluationClient->setGender(new CxGender(CxGender::FEMALE));
                            break;
                        default:
                            $EvaluationClient->setGender(new CxGender(CxGender::UNKNOWN));
                    }
                    switch($oldEvaluationClient['call_option']) {
                        case 'Not Contacted':
                            $EvaluationClient->setCallStatus(new EbEvaluationCallStatus(EbEvaluationCallStatus::NOT_CONTACTED));
                            break;
                        case 'Sale':
                            $EvaluationClient->setCallStatus(new EbEvaluationCallStatus(EbEvaluationCallStatus::SALE));
                            break;
                        case 'Unqualified/Bad':
                            $EvaluationClient->setCallStatus(new EbEvaluationCallStatus(EbEvaluationCallStatus::UNQUALIFIED_BAD));
                            break;
                        case 'Pending':
                            $EvaluationClient->setCallStatus(new EbEvaluationCallStatus(EbEvaluationCallStatus::PENDING));
                            break;
                        case 'Not interested at this time':
                            $EvaluationClient->setCallStatus(new EbEvaluationCallStatus(EbEvaluationCallStatus::NOT_INTERESTED));
                            break;
                    }
                    $EvaluationClient->setEvaluationStatus($oldEvaluationClient['evaluation_status'] == '1');
                    $EvaluationClient->setPendingResponse($oldEvaluationClient['pending_response'] == '1');
                    if($oldEvaluationClient['created_at']){
                        $dateCreated = CxDateTime::createFromFormat('Y-m-d H:i:s', $oldEvaluationClient['created_at']);
                        $EvaluationClient->setDateCreated($dateCreated->getTimestamp());
                    }
                    if($oldEvaluationClient['updated_at']){
                        $dateUpdated = CxDateTime::createFromFormat('Y-m-d H:i:s', $oldEvaluationClient['updated_at']);
                        $EvaluationClient->setDateUpdated($dateUpdated->getTimestamp());
                    }
                    $token = md5($EvaluationClient->getDateUpdated() . $emailAddress);
                    $EvaluationClient->setToken($token);
                    $EvaluationClient->setExternalRefValue('evaluation_client_id', $oldEvaluationClientId);
                    $this->db->begin();
                    $EvaluationClient->save();
                    $resultJson = $this->getEvaluationClientResultJson($oldEvaluationClientId);
                    if($resultJson){
                        $result = new EbEvaluationClientResult();
                        $result->setResultJson($resultJson);
                        $result->setEbEvaluationClient($EvaluationClient);
                        $result->save();
                    }
                    $this->db->commit();

                    $this->setEvaluationClientMigratedStatus($oldEvaluationClientId, true);
                    $migrated++;
                }
            }

            // Cleanup memory
            unset($EvaluationClient);
            unset($healthCoachId);

        }catch(\Exception $ex){
            // Undo any pending transaction (needed because of Phalcon bug not undoing transaction after exception)
            while($this->db->isUnderTransaction()){
                $this->db->rollback();
            }
            $this->writeLogError("Evaluation Client (ID: $oldEvaluationClientId): ERROR - " . $ex->getMessage(), true);
            $errors++;
        }

        $this->writeMigrationStats("Migrating Evaluation Clients", $total,$progress+$skipped, $migrated, $skipped, $errors);
    }
    }


7.5k

Can you measure some stuff too check where the delay happens? Don’t see really weird stuff. Does your name field have an index?

Can you measure some stuff too check where the delay happens? Don’t see really weird stuff. Does your name field have an index?

Which name field mate? You mean findFirstByName?



7.5k

Yes, I assume name is mapped on the column name on your database. If you have don't have an index on name, depening on your database, it could be a bit bit slower. But please start with some measurements. For example using microtime.

If you're going with speed, ORM should be avoided. ORM trades off speed for ease of use - but in this case speed is what you want. I'd consider writing the queries by hand.

Line 43: $userAuthProvider->save(). $userAuthProvider doesn't exist - that save might be causing some slowdown. It also looks like you're wrapping $EvaluationClient->save() in a transaction. Why? You're doing a bunch of other stuff outside a transaction.

Might it be possible to do a lot of this stuff without PHP code? For example, could you just do something like:

INSERT
INTO
    newdatabase.evaluation_client
SELECT
 *
FROM
    olddatabase.evaluation_client

Then modify like:

UPDATE
    olddatabase.evaluation_client
SET
    gender = 'm' where gender = 'male'