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);
}
}