Ändern der execution-time & undefined offset

[michiman]

Einen schönen Abend zusammen!

Ich möchte gleich gerne auf den Punkt kommen. Ich versuche gerade, Geodaten, die mir als CSV-Daten vorliegen in eine DB zu klopfen. Dafür habe die CSV erstmal in ein Array geschrieben. Dieses mehrdimensionale Array habe ich dann um die nicht benötigten Daten gekürzt, so dass am Ende noch ein Array in der passenden Form (Ort, PLZ, LON, LAT) übrig bleibt. Nun zu den Schwierigkeiten:

Ich verwende folgende Schleife um die Daten aus dem Array in die DB zu schicken:

for ($index = 0; $index < $elemente; $index++)
{
    $ort = $geo_passend[$index][0];
    $lat = $geo_passend[$index][1];
    $lon = $geo_passend[$index][2];
    $plz = $geo_passend[$index][3];
    
    $sql = "INSERT INTO geo_data (ORT, LAT, LON, PLZ) VALUES ('$ort', '$lat', '$lon', '$plz')";
    $result = mysqli_query($verbindung, $sql);
    $anzahl = $anzahl + mysqli_affected_rows($verbindung); 

Das klappt auch solange ich als Schleifenbedingung keine zu großen Durchläufe veranstalte. Bei 100 Durchgängen macht das Script alles super, auch bei 1000 kein Problem, bei 10000 und mehr kommt dann auf einmal eine Notice in dieser Form:

Notice: Undefined offset: 0 in C:\xampp\htdocs\geo_csv_in_db.php on line 68
Notice: Undefined offset: 1 in C:\xampp\htdocs\geo_csv_in_db.php on line 69
Notice: Undefined offset: 2 in C:\xampp\htdocs\geo_csv_in_db.php on line 70
Notice: Undefined offset: 3 in C:\xampp\htdocs\geo_csv_in_db.php on line 71

Das sind genau diese Zeilen, in denen ich den Variablen Werte aus dem Array zuweise. Woran kann das liegen, wenn ich mit den Durchläufen hoch gehe? Am Ende müssen es nicht ganz 70k sein, bis ich alle Geodaten in der DB hab.

Dann dachte ich, es liegt an meiner php.ini. Hier wollte ich nun die execution-time hochstellen, aber die Änderung macht sich auch nach einem Xampp-Neustart nicht bemerkbar, obwohl die Änderung abgespeichert und in der php.ini auch geändert ist.

Ich hoffe, ihr habt da ein paar Denkanstöße für mich.

Merci und Grüße

Michael

[Tropi]

Ich schätze mal du wirst eine Leerzeile o.ä. haben, demnach sind dann die Indizes nicht vergeben.
Am besten du packst mal das in die Schleife:

if (!isset($geo_passend[$index][0])) {
  var_dump($geo_passend[$index]);
} 

und schaust damit was tatsächlich vorhanden ist, wenn es offensichtlich kein Array (mit numerischen Indizes) ist.

Der Fehler hat übrigens absolut nichts mit Zeit oder Anzahl der Durchläufe zu tun, offensichtlich gibt es nur irgendwann eine problematische Zeile in deinem CSV file.

[lstegelitz]

Arrays werden über foreach iteriert (es kann Lücken geben!)

Ein INSERT kann mehr als ein VALUES Block enthalten. Das sollte man auch nutzen, um die Performance zu erhöhen, pro Datensatz ein Query abzufeuern, ist Verschwendung.

[michiman]

@Tropi: Vielen Dank für diesen Testansatz. Bringt tatsächlich einige, die wohl nichts enthalten. Darum muss ich mich kümmern

@Istegelitz: Mir wäre die Variante mit foreach auch lieber gewesen, allerdings fehlt mir spontan der Ansatz wie ich hier, im Falle des mehrdimensionalen Arrays an die "inneren" Werte komme, ich müsste dann irgendwie sowas basteln:

foreach ($geo_passend as $temp)
{
    foreach {$geo_passend[äußere_Zelle] as $temp2)
    {
        $sql = ...;
    }
} 

Danke auch für den Hinweis der Perfomanceveresserung, allerdings verstehe ich das gar nicht. Ich möchte ja grad gar nicht mehr machen, als "nur" die Daten in die DB schreiben. Ein Datensatz besteht aus den vier Werten ORT, PLZ, LON, LAT und dieses Viererpaket schicke ich auf einmal an die DB oder sehe ich das falsch.

Danke nochmal!

[Tropi]

Zur Schleife: Es gibt auch diese Notation

foreach (array_expression as $key => $value)
    statement 

(siehe: http://at2.php.net/manual/en/control...es.foreach.php)
Anstatt der Zählervariable verwendest du dann eben $key.

Zum Eintragen der Daten: Es ist nicht falsch, aber unnötig aufwendig (= inperformant). Wenn du jedes Datum einzeln einträgst, hast du viel Overhead dabei. Wenn du jetzt schon weißt das du mehrere Dinge eintragen willst, dann kannst du das auch auf einmal tun. Stell dir vor du willst 70.000 Äpfel kaufen und fährst jedes Mal extra mit dem Auto los...Auch nicht gerade ideal, oder?

[lstegelitz]

@Istegelitz: Mir wäre die Variante mit foreach auch lieber gewesen, allerdings fehlt mir spontan der Ansatz wie ich hier, im Falle des mehrdimensionalen Arrays an die "inneren" Werte komme, ich müsste dann irgendwie sowas basteln:

foreach ($geo_passend as $temp)
{
    foreach {$geo_passend[äußere_Zelle] as $temp2)
    {
        $sql = ...;
    }
} 

$temp ist ebenfalls ein Array und kann als solches benutzt werden:

foreach ($arr as $sub_arr) {
  foreach ($sub_arr as $item) {
  }
} 

[michiman]

@Tropi: Der Vergleich mit den Äpfeln ist gut, das hab ich verstanden. Und es macht auch nichts (in Bezug auf Perfomance oder anderen Überlegungen), wenn die Query dann ca. 70000 Einträge hat?

@Istegelitz: Der Hinweis hat geholfen. Mich würde jetzt nur interessieren, ob es eine "schönere" Möglichkeit gibt, die Query zusammen zu setzen??

Die foreach-Schleifen sehen nun so aus...

$sql_temp = "INSERT INTO geo_data (ORT, LAT, LON, PLZ) VALUES";
foreach ($geo_passend as $key1)
{
    $sql_temp .= "(";
    foreach ($key1 as $temp2)
    {
        $sql_temp .= "'$temp2',";
    }
    $sql_temp = substr($sql_temp, 0, -1);
    $sql_temp .= "),";
}  
    $sql = substr($sql_temp, 0, -1); 

Allerdings hab ich nun ein viel größeres Problem damit bekommen, ich bekomm es nicht mehr an die DB gesendet, denn vorher kommen diese Warnungen

Warning: mysqli_query(): MySQL server has gone away in C:\xampp\htdocs\tests\geo_test.php on line 82
Warning: mysqli_query(): Error reading result set's header in C:\xampp\htdocs\tests\geo_test.php on line 82

Das ist line 81 und 82:

$result = mysqli_query($verbindung, $sql);
$anzahl = mysqli_affected_rows($verbindung); 

Habt ihr dafür ne Idee???

Merci

[lstegelitz]

Warning: mysqli_query(): MySQL server has gone away in C:\xampp\htdocs\tests\geo_test.php on line 82

Dann schaufelst du vermutlich zuviele Daten in ein Query. Es gibt da eine Konfigurationsvariable in MySQL, max_allowed_packet, welche die maximale Größe eines Queries angibt, die steht defaultmäßig auf 1MB. Ist ein Query größer, unterbricht der Server die Verbindung, um die Möglichkeit der Datenflutung zu unterbinden.

Ich hätte hier zufällig noch ein bischen Code für den gestückelten Import von CSV Dateien in eine Datenbank... be my guest

Die CSV muss in der ersten Zeile die Spaltennamen haben, passend zur Zieltabelle.

/* Liest eine CSV Datei und erzeugt ein mehrdimensionales, assoziatives Array
 * Setzt voraus, das die erste Zeile die Feldnamen enthält
 * */
function csvFromFile($fileName, $delim = ';') {
    $fp = fopen($fileName, 'r');
    if (!$fp) return false;
    
    $head = fgetcsv($fp, 4096, $delim);
    
    $data = array();
    while($row = fgetcsv($fp, 4096, $delim)) {
        if ( count($row) == count($head) ) {
            $data[] = array_combine($head, $row);
        }
    }
    return $data;
}

/* Importiert CSV Daten.
 * Setzt voraus, das die Tabelle in der Datenbank bereits existiert. 
 * Sollten die Felder in der CSV nicht zu der in der Tabelle passen, wird der Import nach dem
 * ersten Durchlauf abgebrochen. Daten werden in kleineren Stückelungen zur Datenbank gesendet.
 * Benutzt ein PDO Datenbankobjekt.
 * */
function importToDatabase($db, $data, $tblName = 'your_table_name', $multiInsertLimit = 100) {
    while(!empty($data)) {
        // trage Daten zusammen
        $buffer = array();
        while (count($data)>0 && count($buffer)<$multiInsertLimit) 
            $buffer[] = array_shift($data);
        
        // extrahiere Feldnamen
        if (isset($buffer[0])) 
            $head = array_keys($buffer[0]);
        
        // Template für eine VALUES Zeile (unnamed)
        $tpl = '(' . implode(', ', array_fill(0, count($head), '?')) . ')';        
    
        $values = array();
        foreach($buffer as $row) {
            $values[] = $tpl;
        }
        
        // SQL Statement zusammenstellen
        $sql = 'INSERT INTO `' . $tblName . '` (`' . implode('`,`', $head) . '`) ';
        $sql.= 'VALUES ' . implode(',', $values)  ;        
        
        $stmt = $db->prepare($sql);
        if (!$stmt) return false;

        // Werte binden        
        $i = 1;
        foreach ($buffer as $row) {
            foreach($row as $field) {
                // Unterscheidung Zahl/String
                $stmt->bindValue($i++, $field, (is_numeric($field))? PDO::PARAM_INT : PDO::PARAM_STR);
            }            
        }

        $result = $stmt->execute();
        if ($result === false) return false;
    }
    return true;
}

error_reporting(-1);
ini_set('display_errors', 1);

// Todo: DB Verbindungsparameter und Benutzer anpassen, ggfs. auch das Encoding usw.
$dsn = 'mysql:host=localhost;dbname=test';
$db = new PDO($dsn, 'root', '', array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES 'UTF8'"));

// Todo: CSV Datei bereitstellen / Pfad anpassen
$file = './users.csv';
$data = csvFromFile($file);
// Todo: Tabellenname angeben
$ret = importToDatabase($db, $data, 't_user');

if (!$ret) print_r($db->errorInfo()); 

[Tropi]

@Tropi: Der Vergleich mit den Äpfeln ist gut, das hab ich verstanden. Und es macht auch nichts (in Bezug auf Perfomance oder anderen Überlegungen), wenn die Query dann ca. 70000 Einträge hat?

Jein, das (mögliche) Problem dabei hast du jetzt ja. (Und von lstegelitz auch die Lösung dafür.)

Natürlich dauert ein Query mit 70.000 Einträgen länger als ein Query mit einem Eintrag, aber er ist schneller als 70.000 Queries mit einem Eintrag. Um bei den Äpfeln zu bleiben: 70.000 Äpfel in ein Auto zu bringen dauert sicher auch ne Zeit lange, aber nicht so lange wie 70.000 Einzelfahrten.

Und auch das andere Problem verhält sich analog. Alles auf einmal (Queries oder Einträge) ist vielleicht nicht immer möglich, aber mehrere auf einmal ist trotzdem sinnvoller.

[michiman]

@Istegelitz: Vielen Dank für Dein Skript. Ich habe allerdings versucht mein Skript dahingehend zu modifizieren, dass ich selbst die Paketgröße bestimmen kann, mit der dann an die DB gesendet wird. Der eigentliche Grund dafür liegt daran, dass ich im Vorfeld schon alles, was ich nicht brauche aus dem Array lösche, dass aus der CSV-Datei generiert wird. Somit habe ich in diesem Array wirklich nur noch die vier Daten, die ich haben möchte.

Jetzt tut sich eine neue Schwierigkeit auf. Ich kann ja nun selbst die Paketgröße zum senden an die DB bestimmen. Bei einer Größe von 5000 Einträgen auf einmal kommt nur ca. jeder vierte an, wieso auch immer... Also von den ca. 68000 nur exakt 15000.

Geh ich mit der Paketgröße runter, geht auch die Anzahl der übertragenen Daten hoch, allerding schaffe ich es nicht, alle einzutragen. Selbst wenn ich Paketgröße 3 nehme, fehlen am Ende noch ca. 900 Daten.

Wie kann das sein, habt ihr hierfür eine Idee???

[tkausl]

Entweder die DB hat eine Einschränkung an Inserts/Sekunde oder du hast ein Fehler im Script, zeig mal bitte dein Aktuelles Script wo der Fehler auftritt.

(Mal in den Log der DB zu sehen kann auch nicht schaden).

[michiman]

@tkausl: Anbei der Ausschnitt aus dem Code, der das Query erzeugt und an die DB sendet. Dieser ist zweigeteilt, einmal in ganze Pakete und einmal in den Rest.

//$durchgaenge ermittel ich weiter oben mit $durchgaenge = floor($gesamt/$daten_auf_einmal)
//Dieser Teil ist für ganze Durchgänge
for ($index_step = 0; $index_step < $durchgaenge; $index_step++)
{
    {
        $sql_temp = "INSERT INTO geo_data (ORT, LAT, LON, PLZ) VALUES";
        for ($index = $daten_auf_einmal*$index_step; $index < $daten_auf_einmal*($index_step+1); $index++)
        {
            $sql_temp .= "(";
            foreach ($geo_passend[$index] as $temp2)
            {
                $sql_temp .= "'$temp2',";    
            }
            $sql_temp = substr($sql_temp, 0, -1);
            $sql_temp .= "),";
        }
        $sql = substr($sql_temp, 0, -1);
        $result = mysqli_query($verbindung, $sql);
        $sql_temp = "";
        $sql = "";
    }
}

//$letzter_durchgang ermittel ich weiter oben mit $gesamt - $durchgaenge * $daten_auf_einmal
//Dieser Teil macht den Rest
$sql_temp = "INSERT INTO geo_data (ORT, LAT, LON, PLZ) VALUES";
for ($index = $daten_auf_einmal*$durchgaenge; $index < $daten_auf_einmal*$durchgaenge+$letzter_durchgang; $index++)
{
    $sql_temp .= "(";
    foreach ($geo_passend[$index] as $temp2)
    {
        $sql_temp .= "'$temp2',";    
    }
    $sql_temp = substr($sql_temp, 0, -1);
    $sql_temp .= "),";
}
$sql = substr($sql_temp, 0, -1);
$result = mysqli_query($verbindung, $sql); 

Ich hoffe, das ist soweit verständlich.

Merci schon mal

[tkausl]

Ich würds mal mit nem Prepared-Statement versuchen, vielleicht bringt das bessere Ergebnisse.

[michiman]

Ich danke euch allen!

Hab ne tolle Lösung mit Prepared-Statements und "Blockabfertigung" gefunden. Läuft sauschnell und es fehlt kein Datensatz.

Danke nochmal und bis zum nächsten Problem