Brauche hilfe bei performance und /oder besseren Code


basti1012

Erfahrenes Mitglied
Kurze erklärung zum Problem.
Habe einen Ordner mit 99999 Datein Ca.
Da stehen alle Strassennamen mit Geo Daten und co drinne.
Die Datein gehen von 01000.php bis 99999.php . Die Zahlen sind die Postleitzahlen.

In diesen Datein sieht es so aus.
Code:
[{"id":74510,"name":"Adolph-Kolping-Weg","gemeinde":"Gilching","plz":"82205","centr_lon":11.288130,"centr_lat":48.118161},
{"id":76162,"name":"Ahornstraße","gemeinde":"Gilching","plz":"82205","centr_lon":11.323844,"centr_lat":48.110645},
{"id":86007,"name":"Allinger Straße","gemeinde":"Gilching","plz":"82205","centr_lon":11.291399,"centr_lat":48.123642},
{"id":86491,"name":"Almersweg","gemeinde":"Gilching","plz":"82205","centr_lon":11.287451,"centr_lat":48.115983},
usw...
Am Anfang habe ich alle Datein durch eine Schleife gejagt und da ist klar das Php Timeout rauswirft.
Also hole ich jetzt immer nur eine Datei aus den Ordner und lese die dann so in der Db ein
PHP:
$inhalt=file_get_contents($link);
$gep=json_decode($inhalt,true);
//print_r($gep);
foreach ($gep as $geparst){
  $r++;
  $gesammt=$r;
  if($r<2000){// versucht zu begrenzen bei großen Datein
    $fileid=$geparst['id'];
    $name=$geparst['name'];
    $gemeinde=$geparst['gemeinde'];
    $plz=$geparst['plz'];
    $centr_lon=$geparst['centr_lon'];
    $centr_lat=$geparst['centr_lat'];
    $query="SELECT * FROM `stadt_strassen_datenbank1` WHERE `fileid`='$fileid' AND `name`='$name' AND `centr_lon`='$centr_lon'";
    $re=mysqli_query($mysqli,$query);
    if($re){
       $cnt = mysqli_num_rows($re);
       if($cnt>=1){
            //echo $fileid.' Schon  '.$cnt.' mal da<br>';
            $alt++;
       }else{
            $query1="INSERT INTO `stadt_strassen_datenbank1`(`fileid`, `name`, `gemeinde`, `plz`, `centr_lon`, `centr_lat`)
            VALUES ('$fileid','$name','$gemeinde','$plz','$centr_lon','$centr_lat')";

            $re1=mysqli_query($mysqli,$query1);
            if($re1){
                 //echo $fileid.' wurder erstellt<br>';
                 $neu++;
            }else{
                 $nicht='{"fileid":'.$fileid.', "name":'.$name.', "gemeinde":'.$gemeinde.', "plz":'.$plz.', "centr_lon":'.$centr_lon.', "centr_lat":'.$centr_lat.'"}';
                 file_put_contents('NOT_GESPEICHERT.php', $nicht, FILE_APPEND | LOCK_EX);

                 echo "Error INSERT: " . mysqli_error($mysqli) . "<br>";
                 $error++;
            }
       }
    }else{
         echo "Error SELECT: " . mysqli_error($mysqli) . "<br>";
    }
  }
}
if($error==0){
     unlink($link);
     $gel=$file.' Gelöscht';
}else{
     $gel=$file.' Nicht gelöscht';
}
Bitte keine aussagen zu sql und co weil der nur für mich einmalig zum einlesen ist.

Der Code Funktioniert soweit ganz gut.
Sind da aber mehr Einträge als ca 100 drinne kommt immer Timeout.
Datenbanken sollten doch mehr alls 100 Einträge lesen und schreiben können bis zum Timeout oder ?
Gibt da eine schnellere möglichkeit , oder was performance Technischer besser ist ?
 

Sempervivum

Erfahrenes Mitglied
Ist denn diese ID gobal eindeutig, d. h. über alle Dateien oder nur pro Datei, d. h. sie kann in verschiedenen Dateien mehrfach auftreten?
 

ComFreek

Mod | @comfreek
Moderator
Bei allen Performanceproblemen: nutz einen Profiler oder zumindest time manuell, was wie lange braucht. Ohne das weißt du ja nicht einmal, woran es liegt, und verschwendest ggf. nur Zeit mit Optimieren unnötiger Stellen.

Flaschenhälse, die mir spontan einfallen:
  • file_get_contents + json_decode (unnötiger Speicherverbrauch; warum nicht streamen?)
  • Keine Prepared Statements => dasselbe SQL-Statement als String wird immer wieder und wieder geparst
  • Constraints (etwa UNIQUE) und Indizes in der Datenbank, die bei jedem INSERT überprüft werden
  • Das Einfügen von nur einer einzigen Zeile pro Iteration -- SQL Datenbanken sind nicht dafür gedacht, dass du mittels INSERT einen Bulk Insert machst, indem du nur eine Zeile jeweils einfügst. Dafür ist der Overhead PHP -> SQL Driver -> SQL Datenbank und zurück wahrscheinlich viel zu groß.

Gibt da eine schnellere möglichkeit , oder was performance Technischer besser ist ?
Zuerst profilen, dann nach "SQL Bulk Insert" googeln.
 

m.scatello

Mitglied
Wenn das nur einmalig ist und nicht auf gute Programmierung ankommt, dann lese die Daten blockweise ein. Also z.B. die ersten 50, dann eine Weiterleitung auf das gleiche Script mit Parameter, damit du weißt, wo das Script weiter machen soll.

Normalerweise würde ich sagen: schmeiß das Script weg, das ist Murks.
 

basti1012

Erfahrenes Mitglied
Das Script soll nur einmal zum Einsatz kommen bis alles in der Datenbank ist.
Ich werde erstmal das versuchen was ihr geschrieben habt und dann mal weiter sehen wie es läuft.

Das mit den blockweise habe ich auch versucht.
Das klappt auch ,aber dauert wie es jetzt ist ewig.
In eine Datei ( zb plz von Berlin ) können mal 3000 Strassennamen drinn sein.
Da kann man sich ja vorstellen wie lange das bei allen Plz Bereichen dauern kann.

Ich versuche erstmal eure Vorschläge , vor allem mit den streamen muß ich mal Googeln weil das sagt mir gerade nicht viel .
Danke schon mal.
 

basti1012

Erfahrenes Mitglied
Ist denn diese ID gobal eindeutig, d. h. über alle Dateien oder nur pro Datei, d. h. sie kann in verschiedenen Dateien mehrfach auftreten?
Die ids die bei den Strassennamen stehen sind meines wissens eindeutig und kommen nicht doppelt vor.
Diese id ist in meiner db "fileid".
Habe schon gedacht das ich die id auch als id in der Datenbank nutzen soll, dann brauch man ja eigentlich die SELECT Abfrage nicht mehr ?
Oder sehe ich das gerade falsch ?
 

Sempervivum

Erfahrenes Mitglied
Genau daran dachte ich bei meiner Frage. Ich denke, ein select mit where nach mehreren Feldern dürfte performance-intensiv sein. Ich habe mich gefragt, ob man das auch mit einem Constraint erreichen kann, dass sicher gestellt wird, dass ein Eintrag nur einmal vorkommen kann. Da wissen andere wahrscheinlich mehr.
 

m.scatello

Mitglied
Ich habe das gerade mal mit einer Datei mit 500 Einträgen ausprobiert, geht ruckzuck:
PHP:
<?php
   $fn = "ein_datei_name.txt";
 
   if (file_exists($fn))
   {
      $db = mysqli_connect("localhost", "root", "", "postleitzahlen");
      mysqli_set_charset($db, "utf8");
     
      $query = "INSERT INTO `data` (`fileid`, `name`, `gemeinde`, `plz`, `centr_lon`, `centr_lat`)
                VALUES (%d,'%s','%s','%s', %2.7f, %2.7f)";
               
      $content = file_get_contents($fn);
     
      $content = json_decode($content,true);
     
      $queries = array();
     
      foreach ($content as $entry)
      {
         $fileid    = mysqli_real_escape_string($db, $entry['id']);
         $name      = mysqli_real_escape_string($db, $entry['name']);
         $gemeinde  = mysqli_real_escape_string($db, $entry['gemeinde']);
         $plz       = mysqli_real_escape_string($db, $entry['plz']);
         $centr_lon = mysqli_real_escape_string($db, $entry['centr_lon']);
         $centr_lat = mysqli_real_escape_string($db, $entry['centr_lat']);
       
         $queries[] = sprintf($query, $fileid, $name, $gemeinde, $plz, $centr_lon, $centr_lat);
      }
     
      if (mysqli_multi_query($db, implode(";", $queries)))
        $output = "$fn erfolgreich importiert\n";
      else
        $output = "$fn *** IMPORT-FEHLER ***\n";
       
             
   }
   else
     $output = "$fn *** FEHLER DATEI NICHT VORHANDEN ***\n";
 
 
   $fp = fopen("import.log", "a+");
 
   fputs($fp, $output);    
 
   fclose($fp);
?>
 

basti1012

Erfahrenes Mitglied
Ich werde das mal testen.
Ich habe da ja schon viele Tage versuche gemacht ,rumgespielt usw.
Deswegen habe ich schon sehr viele Einträge in der Db.
Um doppelte Einträge zu vermeiden wollte ich ja mit SELECT erst abfragen ob der Eintrag schon vorhanden ist.

Hatte auch gedacht das man INSERT INTO auch mit IF NOT EXISTS und so verbinden könnte.
Versuche sahen so aus.

SQL:
$query="INSERT INTO `stadt_strassen_datenbank` (`fileid`, `name`, `gemeinde`, `plz`, `centr_lon`, `centr_lat`)
        SELECT  '$fileid','$name','$gemeinde','$plz','$centr_lon','$centr_lat'
        FROM `stadt_strassen_datenbank`
        WHERE NOT EXISTS (SELECT *
               FROM `stadt_strassen_datenbank`
               WHERE `fileid`='$fileid'
               AND   `name` = '$name')";
Ich denke mal das ich mit den einlesen nochmal von vorne anfange.
Da die Id's der Strassen wahrscheinlich einmalig sind , kann ich auf Select ja verzichten.
Wenn da doch eine Doppelt sein sollte laße ich es in einer Datei schreiben die man dann später per Hand kontrolliert.

Info nebenbei.
Die Hauptstrasse gibt es über 8000 mal.
Die Strasse Fi....tor nur 1 mal.

Das wahr Geografie, gute Nacht
 
Zuletzt bearbeitet:

basti1012

Erfahrenes Mitglied
So wollte mal bescheid geben.
Der Code aus post 11 sieht jetzt so aus
PHP:
<?php
/*
  Tabelle data

  CREATE TABLE `data` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fileid` int(11) NOT NULL,
`name` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`gemeinde` varchar(64) COLLATE utf8_unicode_ci NOT NULL,
`plz` varchar(5) COLLATE utf8_unicode_ci NOT NULL,
`centr_lon` float NOT NULL,
`centr_lat` float NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
*/

$time_start = microtime(true);


$fp = fopen("import.log", "a+");

$files = glob("./txt/*.txt");

$db = mysqli_connect("localhost", "root", "", "postleitzahlen");
mysqli_set_charset($db, "utf8");

$query = "INSERT INTO `data` (`fileid`, `name`, `gemeinde`, `plz`, `centr_lon`, `centr_lat`)
           VALUES (%d,'%s','%s','%s', %2.7f, %2.7f)";

foreach ($files as $file)
{ 
      $content = file_get_contents($file);
    
      if (strlen ($content) > 2)
      { 
         $content = json_decode($content,true);
    
         $queries = array();
    
         foreach ($content as $entry)
         {
            $fileid    = mysqli_real_escape_string($db, $entry['id']);
            $name      = mysqli_real_escape_string($db, $entry['name']);
            $gemeinde  = mysqli_real_escape_string($db, $entry['gemeinde']);
            $plz       = mysqli_real_escape_string($db, $entry['plz']);
            $centr_lon = mysqli_real_escape_string($db, $entry['centr_lon']);
            $centr_lat = mysqli_real_escape_string($db, $entry['centr_lat']);
      
            $queries[] = sprintf($query, $fileid, $name, $gemeinde, $plz, $centr_lon, $centr_lat);
         }
    
         if (mysqli_multi_query($db, implode(";", $queries)))
         {
            $i = 0;
            while (mysqli_next_result($db)) $i++;
          
           $output = "$file erfolgreich importiert\n";
         }
         else
            $output = "$file *** IMPORT-FEHLER ***\n";
     }
     else
       $output = "$file empty\n";
 
     fputs($fp, $output);   
}

fclose($fp);

$time_end = microtime(true);

$time = $time_end - $time_start;

echo "Running $time seconds";

?>
Damit konnte ich 1.200.000 Einträge in 80 Sekunden in der Datenbank eintragen.
Wenn noch einer Ideen zu verbesserung hat dann bitte schreiben.
Ansonsten würde ich das so erstmal als Lösung stehen lassen.
 

Sempervivum

Erfahrenes Mitglied
Dieses fällt mir noch ein:
Da die Id's der Strassen wahrscheinlich einmalig sind , kann ich auf Select ja verzichten.
Damit keine mehrfachen Einträge entstehen, muss dann die ID unique sein. Möglicherweise hast Du das schon, oder Du hast vorher alles gelöscht, so dass es nicht akut ist.
 

ComFreek

Mod | @comfreek
Moderator
Dieses fällt mir noch ein:
Damit keine mehrfachen Einträge entstehen, muss dann die ID unique sein. Möglicherweise hast Du das schon, oder Du hast vorher alles gelöscht, so dass es nicht akut ist.
Beachte, dass wenn du die id in der DB als UNIQUE deklarierst, der Import sehr langsam werden kann. Hingegen wenn du zuerst importierst und dann die id als UNIQUE deklarierst, dann kann es bedeutend schneller sein -- allein wegen dem geringeren Overhead das einmal anstatt 1,2 Millionen mal zu machen.

@basti1012 Wie gesagt, ich würde Prepared Statements empfehlen. So muss das DBMS das SQL Query nur ein einziges Mal parsen und du brauchst auch kein mysqli_real_escape_string mehr. Escaping ist ja nur unnötiger Overhead, wenn du die Daten als solches direkt an das DBMS liefern kannst -- was Prepared Statements genau tun.
 

basti1012

Erfahrenes Mitglied
Die ID unique zu stellen hätte ich gemacht wenn ich die Datenbank so weiter geführt hätte wie am Anfang, wo ich noch mit Select und insert gearbeitet habe.
Ich hatte dann diesen Code laufen lassen und gesehen das es ohne probleme funktioniert hat.
Dann konnte ich die alte Datenbank löschen und gut ist.
Das gute wahr das hier kein Datensatz doppelt sein dürfte.
Wenn ich das Script noch mal laufen lassen würde ( ausversehen oder extra ) , dann würde alles doppelt sein.
Kann man eigentlich irgendwie bei der Datenbank einstellen das man jetzt nicht mehr ( ausversehen oder extra ) da noch mehr Einträge machen kann ?.

Ich muß zugeben das ich bis jetzt noch nie mit Prepared Statements gearbeitet habe.
Man kennt zwar die Vorteile aber ich habe es mal ohne gelernt und seid dem auch nicht dran gedacht das zu ändern.
Ich werde es aber mal versuchen.
 

ComFreek

Mod | @comfreek
Moderator
Kann man eigentlich irgendwie bei der Datenbank einstellen das man jetzt nicht mehr ( ausversehen oder extra ) da noch mehr Einträge machen kann ?.
Hingegen wenn du zuerst importierst und dann die id als UNIQUE deklarierst [...]
Also ja, siehe How add unique key to existing table (with non uniques rows). Möglicherweise willst du gleich einen PRIMARY KEY für deine id-Spalte. Dafür siehe How to add a primary key to a MySQL table?. Beide Links sind übrigens Tophits bei Google :)
 

Neue Beiträge