I must insert a large data to mysql from a xml every day, I do this job with cron job, but it takes about 2 hours to insert all the data to my DB,
Is there anyway to decrease that time?
Here is my code:
I use meekroDB to insert
My first code was (It is pretty simple):
for ($i = 0; $i <= count($xml->Table);$i++) {
DB::insert($PreFix."_stock", array(
'refid' => (string)$xml->Table[$i]->refid,
'articulo' => (string)$xml->Table[$i]->articulo,
'modelo' => str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->modelo),
'metadatos' => str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->metadatos),
'estado' => (string)$xml->Table[$i]->estado,
'reffab1' => (string)$xml->Table[$i]->reffab1,
'reffab2' => (string)$xml->Table[$i]->reffab2,
'refequiv' => (string)$xml->Table[$i]->refequiv,
'nota' => str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->nota),
'precio' => (string)$xml->Table[$i]->precio,
'numfotos' => (string)$xml->Table[$i]->numfotos,
'fechamod' => (string)$xml->Table[$i]->fechamod,
'idarticulo' => (string)$xml->Table[$i]->idarticulo,
'idversion' => (string)$xml->Table[$i]->idversion
));
So my question is: It is normal that long time to insert 86k rows or there is any best way?
Before i began testing with meekroDB I wrote this code, but i was getting always Timed out
for ($i = 0; $i <= count($xml->Table);$i++) {
$VALUES[] = "( '".
(string)$xml->Table[$i]->refid."' , '".
(string)$xml->Table[$i]->articulo."' , '".
str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->modelo)."' , '".
str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->metadatos)."' , '".
(string)$xml->Table[$i]->estado."' , '".
(string)$xml->Table[$i]->reffab1."' , '".
(string)$xml->Table[$i]->reffab2."' , '".
(string)$xml->Table[$i]->refequiv."' , '".
str_replace($lessCharMeta,$lessCharMeta2,(string)$xml->Table[$i]->nota)."' , '".
(string)$xml->Table[$i]->precio."' , '".
(string)$xml->Table[$i]->numfotos."' , '".
(string)$xml->Table[$i]->fechamod."' , '".
(string)$xml->Table[$i]->idarticulo."' , '".
(string)$xml->Table[$i]->idversion."' )";
}
$stmt = $mysqli->prepare(
"CREATE TABLE IF NOT EXISTS ".$PreFix."_stock(ID int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`refid` VARCHAR(10),
`articulo` VARCHAR(200),
`modelo` VARCHAR(16),
`metadatos` VARCHAR(500),
`estado` VARCHAR(100),
`reffab1` VARCHAR(50),
`reffab2` VARCHAR(50),
`refequiv` VARCHAR(50),
`nota` VARCHAR(200),
`precio` VARCHAR(15),
`numfotos` VARCHAR(2),
`fechamod` VARCHAR(50),
`idarticulo` VARCHAR(10),
`idversion` VARCHAR(10) )"
);
$stmt->execute();
$stmt->close();
$temp = "";
foreach ($VALUES as $KEY){
if (!empty($KEY)){
$temp = $temp." , ".$KEY;}
}
$sentencia = "
INSERT INTO ".$PreFix."_stock
(refid,articulo,modelo,metadatos,estado,reffab1,reffab2,refequiv,nota,precio,numfotos,fechamod,idarticulo,idversion)
VALUES
";
if ($stmt = $mysqli->prepare($sentencia.$temp) ){
$stmt->execute();
$stmt->close();
}
else {
printf("Errormessage: %s\n", $mysqli->error."<hr/>");
}
Then I decided to jump to the same script over and over every 500 insert by sending the for loop index by post data, but when I set cron job to do the work it was never jumping over the script.
With meekroDB its a bit slow but I never get PHP timed out
