CODEWITHSUNDEEP
phpmysqlxmlmeekro
user2854628
user2854628

Reputation: 17

improve code xml-mysql for large insert (86k entries) (PHP)

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

Upvotes: 0

Views: 172

Answers (3)

Philipe
Philipe

Reputation: 572

MySQL has also a LOAD XML INFILE.
According to the provided XML, I think it needs to be something like this:

LOAD XML LOCAL INFILE 'data.xml'
INTO TABLE tablename
ROWS IDENTIFIED BY '<Table>'

If this do not work, you could try to convert it to a CSV format and use LOAD DATA INFILE instead.

LOAD DATA LOCAL INFILE 'data.csv' INTO TABLE tablename
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
IGNORE 1 LINES;

Just remembering that to make LOAD ... LOCAL INFILE to work you need to have your data.xml or data.csv file in the same server as MySQL and use the real path to the file.

You could also import the file to a temp table to do some processing before inserting it into the real table:

INSERT INTO tablename (col1, col2, col3)
SELECT tmp.col1, tmp.col2, replace(tmp.col3, 'foo', 'bar')
FROM tmp WHERE tmp.col1 > 0

Hope it works! :)

Upvotes: 0

user2854628
user2854628

Reputation: 17

Mysql documentation: "When loading a table from a text file, use LOAD DATA INFILE. This is usually 20 times faster than using INSERT statements"

I think I will parse the data from xml to csv then use load data.

part of xml file --> 

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <Table>
    <referencia>001004024053008</referencia>
    <ubicacion>Montada revisada</ubicacion>
    <refid>3301474</refid>
    <articulo>MOTOR COMPLETO</articulo>
    <modelo>FORD MONDEO TURNIER (GE) Futura X   |   01.06 - 12.07</modelo>
    <metadatos> | MOTOR COMPLETO | FORD MONDEO TURNIER (GE) FUTURA X 2.0 TDCI CAT |   2007 08825 |  | 3301474 |  |  | COLECTOR DE ADMISION DESMONTADO</metadatos>
    <estado>Material revisado</estado>
    <reffab1 />
    <reffab2 />
    <refequiv />
    <nota>COLECTOR DE ADMISION DESMONTADO</nota>
    <precio>900.000</precio>
    <numfotos>0</numfotos>
    <fechamod>2014-08-13T19:38:39.52+02:00</fechamod>
    <idarticulo>9</idarticulo>
    <idversion>2216764</idversion>
  </Table>
  <Table>
    <referencia>006001083046004</referencia>
    <ubicacion>Montada revisada</ubicacion>
    <refid>3300752</refid>
    <articulo>CAJA CAMBIOS</articulo>
    <modelo>VOLKSWAGEN LUPO (6X1/6E1) Advance   |   06.04 - 12.05</modelo>
    <metadatos> | CAJA CAMBIOS | VOLKSWAGEN LUPO (6X1/6E1) ADVANCE 1.4 TDI | AMF  2001 08822 |  | 3300752 |  |  | </metadatos>
    <estado>Material revisado</estado>
    <reffab1 />
    <reffab2 />
    <refequiv />
    <nota />
    <precio>300.000</precio>
    <numfotos>0</numfotos>
    <fechamod>2014-08-13T19:30:43.922+02:00</fechamod>
    <idarticulo>175</idarticulo>
    <idversion>2216763</idversion>
  </Table>
  <Table>
    <referencia>001004083046004</referencia>
    <ubicacion>Montada revisada</ubicacion>
    <refid>3300716</refid>
    <articulo>MOTOR COMPLETO</articulo>
    <modelo>VOLKSWAGEN LUPO (6X1/6E1) Advance   |   06.04 - 12.05</modelo>
    <metadatos> | MOTOR COMPLETO | VOLKSWAGEN LUPO (6X1/6E1) ADVANCE 1.4 TDI | AMF  2001 08822 | AMF | 3300716 |  |  | </metadatos>
    <estado>Material revisado</estado>
    <reffab1>AMF</reffab1>
    <reffab2 />
    <refequiv />
    <nota />
    <precio>1000.000</precio>
    <numfotos>0</numfotos>
    <fechamod>2014-08-13T19:30:46.792+02:00</fechamod>
    <idarticulo>9</idarticulo>
    <idversion>2216763</idversion>
  </Table>
  <Table>
    <referencia>010015023127001</referencia>
    <ubicacion>Almacenada</ubicacion>
    <refid>2851404</refid>
    <articulo>AIRBAG DELANTERO IZQUIERDO</articulo>
    <modelo>FIAT SCUDO ACRISTALADO    |   01.07 - ...</modelo>
    <metadatos> | AIRBAG DELANTERO IZQUIERDO | FIAT SCUDO ACRISTALADO 2.0 JTD | RHK  2007 07282 |  | 2851404 |  |  | [V.A] FIAT SCUDO 2.0 HDI FURGON - ACRISTALADO - CORTO [07282]</metadatos>
    <estado>Material revisado</estado>
    <reffab1 />
    <reffab2 />
    <refequiv />
    <nota>[V.A] FIAT SCUDO 2.0 HDI FURGON - ACRISTALADO - CORTO
[07282]</nota>
    <precio>0.000</precio>
    <numfotos>3</numfotos>
    <fechamod>2013-06-12T21:47:33+02:00</fechamod>
    <idarticulo>54</idarticulo>
    <idversion>2207294</idversion>
  </Table>
  <Table>
    <referencia>010071023113007</referencia>
    <ubicacion>Almacenada</ubicacion>
    <refid>3021150</refid>
    <articulo>AIRBAG CORTINA DELANTERO IZQUIERDO</articulo>
    <modelo>FIAT STILO (192) 1.9 120 Dynamic Multijet   |   09.05 - ...</modelo>
    <metadatos> | AIRBAG CORTINA DELANTERO IZQUIERDO | FIAT STILO (192) 1.9 120 DYNAMIC MULTIJET 1.9 8V JTD CAT |   0 |  | 3021150 |  |  | </metadatos>
    <estado>Material de segunda mano</estado>
    <reffab1 />
    <reffab2 />
    <refequiv />
    <nota />
    <precio>0.000</precio>
    <numfotos>0</numfotos>
    <fechamod>2013-06-12T23:22:17+02:00</fechamod>
    <idarticulo>460</idarticulo>
    <idversion>2208730</idversion>
  </Table>
  <Table>
    <referencia>010080059055011</referencia>
    <ubicacion>Almacenada</ubicacion>
    <refid>3104859</refid>
    <articulo>PRETENSOR AIRBAG IZQUIERDO</articulo>
    <modelo>NISSAN PRIMERA BERLINA (P11) SLX   |   05.96 - 12.99</modelo>
    <metadatos> | PRETENSOR AIRBAG IZQUIERDO | NISSAN PRIMERA BERLINA (P11) SLX 2.0 TURBODIESEL CAT | CD20T  1998 07690 |  | 3104859 |  |  | </metadatos>
    <estado>Material de segunda mano</estado>
    <reffab1 />
    <reffab2 />
    <refequiv />
    <nota />
    <precio>0.000</precio>
    <numfotos>4</numfotos>
    <fechamod>2013-08-12T09:32:57+02:00</fechamod>
    <idarticulo>469</idarticulo>
    <idversion>2210365</idversion>
  </Table>

Upvotes: 0

Lauri Orgla
Lauri Orgla

Reputation: 561

You could do it as a batch job. Dont do a insert after every read. Insert multiple rows at a time.

What you would do, is you gather like 10 rows inside array, then build query from these rows like: INSERT INTO table (field 1,2,3,4,4 etc..) VALUES (dataset1), (dataset2), (dataset3)... 10

Then you will save a LOT of time, because u do 10x less inserts. You probably have MANY rows in your database... and maybe even multiple indexes, which makes inserts slow.

Upvotes: 1

Related Questions