6
Hello, I am creating an application where I need to constantly consult a spreadsheet, take the data from it and insert in the database (Mysql).
The point is that these spreadsheets will always have at least 55000 (fifty-five thousand) records
What I have to do on every record:
- A query to check if it already exists or not in the comic
- If it exists I do an UPDATE
- If I don’t exist
I do an Insert
For now I’m just checking whether or not there is in the bank, and it’s already taking forever, follow the code below:
set_time_limit(0);
include_once '../../db/conexao.php';
include_once '../../ClassesPhpExcel/PHPExcel/IOFactory.php';
$objReader = new PHPExcel_Reader_Excel5();
$caminho = array('C:','Users','brayan','Documents','LN','estrutura_ecn.xls');
$objPHPExcel = $objReader->load(join(DIRECTORY_SEPARATOR, $caminho));
$sheetData = $objPHPExcel->getActiveSheet()->toArray(null,true,true,true);
unset($sheetData['1']);
$count = 0;
foreach ($sheetData as $value){
try {
$criteria = array( 'select' => 'COUNT(codigo) as codigo',
'condition'=>'cod_produto ='.$value['A']);
$existe = Connection::findAllByAttributes('produto', $criteria, false);
if($existe[0]->codigo == 0){
//insiro o registro
}else{
// faço o update
}
$count++;
}catch (PDOException $e){
echo $e->message."<br/>";
}
}
I was wondering if you have any other way to make these insertions and updates more efficiently, and not take so long... ?
From now on I am grateful...
Yes. There is a way to make insertions and updates more effectively using operations in Bulk, see this answer: http://answall.com/questions/9332/problemas-com-performant-utilizando-update/9344#9344
– cantoni