[Holger_N]

Moin,

Ich habe Logdateien(csv) in eine Datenbank eingelesen und mir eine kleine PHP-Seite drumrum gebastelt, um die Daten auswerten zu können. Das funktioniert auch alles soweit, nur möchte ich gerne nachträglich Daten ändern. Es handelt sich um die Zugriffe auf einen Datenserver und in einer Tabellenspalte sind die Pfade der Dateien. Der Anfang der Pfade, der bei fast allen Dateien gleich ist, soll einfach nur gelöscht werden. Das habe ich auch folgendermaßen gelöst:

$suchen = $sql->prepare("SELECT id, datei FROM log_tabelle WHERE `datei` LIKE '%riesenlanger_Pfad_der_fast_ueberall_gleich ist%' ;") ;

$korrektur = $sql->prepare("UPDATE log_tabelle SET datei = :inhalt WHERE id = :id ;") ;

$suchen->execute(); 

while($daten = $suchen->fetch()) 

	{ 
	    $id[] = $daten['id'];
	    $inhalt[] = $daten['datei'];
	}


for ($i=0; $i<count($id); $i++)

	{
	    $inhalt = str_ireplace('riesenlanger_Pfad_der_fast_ueberall_gleich ist', '', $inhalt[$i]) ;
	    
	    $korrektur->bindParam(':inhalt', $inhalt);
	    $korrektur->bindParam(':id', $id[$i]);
	    
	    $korrektur->execute();         
	}

Das funktioniert auch (falls da Fehler in der Syntax sind, die sind nur versehentlich, weil ich das Ganze für hier ein wenig vereinfacht habe) aber es ist sehr zeitaufwändig, da es auch eine knappe Million Einträge sind.

Gibt es da vielleicht einen schnelleren Weg? Manchmal schreibt man ja sowas umständlich und dann gibt es einen einzigen Befehl, der das in fünf Minuten mit einmal macht.

[RalphS]

Nur mal zur Verdeutlichung. Du hast in [Datei] einen mehr oder weniger vollständigen Dateipfad, richtig?

Außerdem schreibst Du, daß er überall gleich ist. Richtig? Also in mehr oder weniger allen Datensätzen. Auch richtig?

Falls ja, reicht ein simples UPDATE mit REPLACE(), ganz ohne WHEREs oder sonstwas. Steck das aber zur Sicherheit in einen rückrollbare Transaktion, falls da irgendwo was schiefgehen sollte.

---

Für die "paar" Einträge wo die Pfade "anders anfangen" müßtest Du dann schauen daß Du die entsprechend behandelst. Könntest ja zB einen Pfad eintragen wie P1:\Pfad_zur_datei und P2:\Pfad zur Datei und dann P1: und P2: irgendwo anders (zB in PHP) wieder rekonstruieren... oder, falls diese Information nahe genug an den Daten selber liegen sollte, halt noch eine extra Tabelle aufmachen namens pfadverweise mit (pfad_id PK; pfad) wo dann halt ('pX:'; 'c:\richtiger\pfad\mit\Eintrag\X\etc\pp') drinsteht für jeden eindeutig identifizierten Dateipfad(Präfix).

Dieser Beitrag wurde von RalphS bearbeitet: 20. Juli 2016 - 21:48

[Holger_N]

Also ich hatte es jetzt probiert mit

$korrektur = $sql->prepare("UPDATE log_tabelle SET datei = REPLACE(datei, 'der_zu_ersetzende_Teil', 'Ersetzung')  ; ") ;
$korrektur->execute(); 

Aber da passiert so ziemlich nichts.

Halt, ich glaube doch, mußte nur komplett durchlaufen.

Wahnsinn, das ging jetzt wirklich in Minuten und ich mußte auch nichts ausklammern oder schützen. Der zu ersetzende Teil kommt in den nicht zu ändernden Einträgen definitiv nicht vor und so blieben die ja unberührt.

Für die kommenden Datensätze schreib ich das so um, dass die Pfade schon beim Import der csv-Dateien angepasst werden.

Dieser Beitrag wurde von Holger_N bearbeitet: 20. Juli 2016 - 22:45

[RalphS]

... Irgendwas stinkt da ganz gewaltig.

"Minuten" kommt mir wahnsinnig spanisch vor. Für <1M Datensätze sollte sowas in Sekunden erledigt sein.

- Nu isses ja durch. Beim nächsten Mal ggf vor derart großen Änderungen/Updates:

- Indices raus per DROP (da MySQL nicht deaktivieren kann)
- UPDATE ausführen, wie gesagt idealerweise in eine Transaktion gekapselt und "on error Rollback;" mit was-auch-immer-MySQL-da-an-Syntax-will, zB

BEGIN TRY;
 BEGIN TRANSACTION;
  UPDATE ... SET a = b, c = d, ... [WHERE x = y OR 1];
 COMMIT;
END TRY;
BEGIN CATCH;
 ROLLBACK;
END CATCH;

- Hinterher die Indices wieder dran und nochmal ein ANALYZE/OPTIMIZE drüberlaufen lassen.

Dieser Beitrag wurde von RalphS bearbeitet: 20. Juli 2016 - 23:40

[Holger_N]

Die Abarbeitung hat sicherlich auch nur Sekunden gedauert, aber bis ich die Tabelle dann aufgerufen hatte und sehen konnte, dass alles geklappt hat, hat es dann noch einen Moment gedauert und weil ich ja mit der alten Variante stundenlang gerödelt hatte schrieb ich Minuten.

Und weil ich das über php gemacht habe und im Browser per Seitenaufruf gestartet, statt einfach die Zeile ins SQL-Feld von phpMyAdmin zu tippen, hat das ja auch nochmal gebremst und wenn dann auch noch der Browser gerade hakelt, ist ja ne Minute schnell rum.

Nee, alles gut. Heute müßten auch die nächsten 10.000 Datensätze kommen, da kann ich dann gucken, ob der Import richtig funktioniert, dann ist die Umbenennung ja auch gleich automatisiert.

[RalphS]

Wenn Du Dir SQL als solches zutraust und auch an den Server selber rankommst, bist Du möglicherweise über mysql (den Befehl) schneller. Verbinden mit

mysql -u<Benutzername> -p -D <Datenbankname>

wobei -p nach dem PW-Prompt verlangt (wenn kein PW gesetzt, muß es weg) und -D optional ist; standardmäßig wird dann nix ausgewählt und Du müßtest mit use... ran und DIr ggf die DBs mit SHOW DATABASES; auflisten -- wenn Dein Benutzerkonto das darf. Sonst halt die Daten aus dem DSN holen, da steht ja alles drin.

-In jedem Fall, wenn es daran nicht hängt ist es schonmal gut. phpMyAdmin ist zwar bequem, aber mit zunehmendem (zu bearbeitendem) Datenvolumen auch zunehmend träger. Weiß nicht, ich hab das Gefühl gewonnen, daß da zuviel mit Schleifen gearbeitet wurde; aber, mh, es ist ja auch eine sehr abstrakte Angelegenheit, also gut möglich daß das schon das beste so ist was man da rausholen konnte... und daß man aber eben im Gegenzug dann für größere Volumina auf phpMyAdmin verzichten sollte.

- Millionen Datensätze alle anzeigen lassen dauert natürlich. Bringt aber nicht viel; kann man eh nicht wirklich überblicken.

Stattdessen reicht zum Test sowas wie

SELECT * FROM log_tabelle WHERE Datei LIKE 'das_was_Du_da_ersetzt_hattest_und_links_losging%';

wo dann eine leere Menge rauskommen muß. Gibt's hier Ergebnisse, ist was schiefgelaufen. Ansonsten halt die Test-SELECTs mit LIMIT und ggf OFFSET einschränken.

Dieser Beitrag wurde von RalphS bearbeitet: 21. Juli 2016 - 08:47

[Holger_N]

Danke, das werde ich mir beim nächsten Mal mal genauer angucken. Zugriff per Konsole habe ich. (Hatte ich das hier oder im Smalltalk geschrieben?) es ist ja »nur« ein einfaches QNAP-NAS, wo ich vollen Zugriff habe auch von zu Hause per VPN und dann von meinem Debian-Rechner direkt per Konsole, alles kein Problem.

Die php-Seite war nur so eine Idee für eine Art Frontend um den ganzen Spaß über die »Firmensoftware« zu administrieren. Ist aber Blödsinn für Sachen, die ohnehin nur ich mache und bremst ja nur unnötig.

[RalphS]

Gut, für QNAP-Performance müßt ich jetzt passen, aber dadurch daß da Celerons drinstecken (hat der inwischen Cache?) würd ich da jetzt für mich auch nichts erwarten. Mit ein bissel Glück läßt sich der mysqld noch ein bissel umkonfigurieren re: Performance und, je nachdem wie die Indices aussehen (oder überhaupt erstmal da sind) läßt sich vielleicht so noch was beschleunigen.

- Kann mir zB durchaus vorstellen, daß wenn Du nach einem Wert für [Datei] suchst und da kein Index drauf ist daß da die Abfrage arg lange dauert. Also einen draufsetzen und das auch so streng wie es die Situation hergibt; wenn also die Werte für [Datei] alle eindeutig sind (und insbesondere eindeutig sein müssen) dann einen UNIQUE index und ansonsten halt einen "normalen".

Weil,
ohne Index: Laufzeit linear proportional zur Anzahl der Datensätze (eg 1M Datensätze <=> 1 Million Zeiteinheiten für die Abfrage insgesamt); aber
mit Index: Laufzait logarithmisch proportional zur Anzahl der Datensätze (eg 1M Datensätze <=> 6 Zeiteinheiten für die Abfrage insgesamt).

[Holger_N]

Ja das war ja meine Idee hinter der ganzen Aktion. Weil praktisch bei fast allen Einträgen der Anfang identisch war, also der ganze Pfad bis zum Kundenordner, wollte ich das abschneiden und dann fängt der Eintrag mit dem Kundennamen an. Dann reicht es, die ersten 10 oder 12 Zeichen zu indizieren statt 100.

Sooo performant muß das Ganze aber auch nicht sein. Es geht nur darum, weil regelmäßig zwei pro Woche die Frage auftaucht, Wer hat zuletzt an der Datei das und das geändert, die Datei gelöscht oder wann war überhaupt der letzte Zugriff. In dem Moment ist das dann egal, ob die Recherche 8 oder 14 Sekunden dauert. Da bin ich dann so oder so der Held, wenn ich sage »Du hast die Datei am 12.7.2014 um 13:04 Uhr und 12 Sekunden gelöscht.« Und dann hole ich die heimlich aus dem Netzwerk-Papierkorb und sage am nächsten Tag, ich habe das in der Nacht zu Hause alles nochmal neu gemacht.

[Holger_N]

Hab da noch was entdeckt, allerdings dann bei der Ausgabe selbst. Ich habe Zeiten als Timestamp in der Datenbank gespeichert und wenn ich die auslese und per PHP in ein Datum/Zeitformat bringe, dann dauert das doch bestimmt auch viel länger, als wenn ich das gleich in der Abfrage mit

DATE_FORMAT(FROM_UNIXTIME(zeit),'%W: %d.%m.%Y - %H:%i:%s') AS zeit

mache.

[RalphS]

"Eigentlich" sollten Zeitstempel bei SQL in YYYY-MM-DD HH:MM:SS abgelegt sein. Aber, wenn das bei Dir mit einem Unix-Zeitstempel drinsteht, dann ja sollte das so passen.

- Ansonsten, ja. Was das Datenbanksystem machen kann, kannst Du es auch machen lassen.

Und wenn Du sowas öfter brauchst, einfach ein VIEW erstellen und da dann die o.a. Zeile als Spaltendefinition mit rein da (aber dran denken, daß das dann kein DATETIME mehr ist und entsprechend Datumsfunktionen nicht mehr drauf angewendet werden können).

Solange wie Du Dich nicht hinstellst und zwei Tabellen nach PHP reinholst und die dann in PHP gegenüberstellst statt einen simplen JOIN draus zu machen denke ich mal haut das sonst schon hin.

[Holger_N]

Ich speichere Datumsangaben absichtlich als Timestamp. Also das ist jetzt nicht, weil irgendwas komisch gelaufen ist, das habe ich bewußt gemacht. Als ich mit PHP & Co. anfing, waren mir die Datumsformate und vor Allem die Berechnungen damit zu kompliziert. Mit dem Timestamp war aber für mich alles logisch. Da habe ich mich irgendwie darauf eingeschossen.

Für dieses »Projekt« ist das aber auch alles ganz einfach gehalten. Es ist auch nur eine Tabelle. Der Hintergrund ist der, dass das QNAP-NAS die Zugriffe loggt und die kann man sich auch angucken. Nach 10.000 Datensätzen werden die in eine CSV-gespeichert und außerdem sind da sehr viele unsinnige Zugriffe mit drin, die ich nicht brauche. Nun importiere ich die CSV in meine Datenbanktabelle, kann dabei die unnützen Datensätze gleich ausklammern und kann mir dann ein Auswertungs- und Bearbeitungsfrontend so basteln, wie ich es brauche, so mit viel umfangreicheren Filterregeln, als es die Bearbeitungsoberfläche des NAS hergibt.