Performance MySQL updaten

[j4nk3y]

Einen schönen Sonntag zusammen,

Ich bin ja stolz wie Oscar, dass mein Raspberry jetzt als Server läuft. Gestern hab ich den ersten Cronjob geschrieben und eingerichtet (Ich freu mir'n Keks).
In diesem hole ich mir Daten aus meiner Datenbank und berechne einen Wert aus einer Hand von Daten und Update diesen Wert für die entsprechende Id. Jetzt landet in meiner Log Datei folgendes:

[05.03.2017 - 14:30:01] Updated 1452 Rows in 19.094034910202 seconds. Get Data in 3.0557448863983 seconds, calculated in 0.066218137741089 seconds and updated informations in 15.952975988388 seconds.

[05.03.2017 - 14:40:01] Updated 169 Rows in 0.44454598426819 seconds. Get Data in 0.083180904388428 seconds, calculated in 0.008436918258667 seconds and updated informations in 0.3280611038208 seconds.

Zwischendurch hab ich kurz die Datenbank gelöscht und neu erstellt. Nun sieht das PHP Script dazu etwa so aus:

$time = microtime(true);

include('db_connect_function.php');
include('general_functions.php');

$get_start_time = microtime(true);
$get_data = $db->prepare(sprintf('SELECT x,y FROM a'));
$get_data->execute();
$result_data = $get_data->get_result();
if(!mysqli_error($db)){
	while ($row_data = $result_data->fetch_assoc()){
		$id = $row_data['id'];
		unset($row_data['id']);
		$data[$id] = $row_data;
		
	}
	$db->close();
	$calc_start_time = $get_end_time =  microtime(true);
	foreach($data as $id => $set){
	 //calculations
	}
	$calc_end_time = $update_start_time = microtime(true);
	
	include('db_connect_function.php');
	foreach($data as $id => $set){
		
		$update_data = $db->prepare(sprintf('UPDATE %s SET y = %.20f WHERE id = %d', mysqli_real_escape_string($db, 'table'), $data['y'], $id));
		
					
	}
	$db->commit();
}	
else{
	
}

Lange rede, kurze Frage. Gibt es eine bessere Methode, Werte in der Datenbank zu aktualisieren?

Gruß
Jo

[Der-Dennis]

Hallo j4nk3y,

	foreach($data as $id => $set){
		
		$update_data = $db->prepare(sprintf('UPDATE %s SET y = %.20f WHERE id = %d', mysqli_real_escape_string($db, 'table'), $data['y'], $id));
		
					
	}
	$db->commit();
}	
else{
	
}

Lange rede, kurze Frage. Gibt es eine bessere Methode, Werte in der Datenbank zu aktualisieren?

Du könntest die Abfrage außerhalb der Schleife vorbereiten (prepare) und anschließend die Parameter binden. Als feststehende Zeichenkette kannst Du auch ‚table‘ direkt im Statement angeben.

Gruß
Dennis

[j4nk3y]

Hey,

Du könntest die Abfrage außerhalb der Schleife vorbereiten (prepare) und anschließend die Parameter binden. Als feststehende Zeichenkette kannst Du auch ‚table‘ direkt im Statement angeben.

Danke, das ist gerade etwa ~3300% schneller.

Gruß
Jo

[dedlfix]

Tach!

Lange rede, kurze Frage. Gibt es eine bessere Methode, Werte in der Datenbank zu aktualisieren?

Wenn du die Werte per Ausdruck in einem SQL-Statement berechnen kannst, also keine externen Daten benötigst, dann kannst du dich auf lediglich ein Update-Statement beschränken. Also gemäß diesem Prinzip:

UPDATE tabelle SET feld = feld + 1;

Ansonsten kann ich dir nicht sagen, was du kürzen kannst, ohen zu wissen, was du konkret vorhast. Nur ein paar offensichtliche Dinge kann ich ansprechen:

$get_data = $db->prepare(sprintf('SELECT x,y FROM a')); $get_data->execute();

Das sprintf() hat an der Stelle keinen Nutzen.

  $update_data = $db->prepare(sprintf('UPDATE %s SET y = %.20f WHERE id = %d', mysqli_real_escape_string($db, 'table'), $data['y'], $id));

Zudem ist es nicht sinnvoll, die Methode des Zusammenstückeln von Statement und Werten mit Prepared Statements zu mixen, ohne die Eigenschaften von Prepared Statements zu verwenden.

Wenn du das Statement selbst zusammenbauen möchtest, dann kannst du mysqli_query() statt mysqli_prepare() und mysqli_stmt_execute() nehmen. Das ist kürzer und spart einen Roundtrip zwischen PHP und MySQL. Dann solltest du aber auch das mit dem Maskieren richtig machen. Ein Tabellenname ist kein Wert, sondern ein Identifier. Für Identifier gelten andere Maskierungsregeln als die Funktion mysqli_real_escape_string() berücksichtigt. Üblicherweise stehen Identifier direkt im Statement und werden nicht bei Bedarf hinzugefügt. Vermutlich gibt es deshalb keine vorgefertigte Funktion, mit der man das erledigen könnte.

Außerdem fehlt dir zu dem Prepare das Execute. Das Commit ist kein Ersatz und auch nur für Transactions sinnvoll. Und die Verbindung selbst zu schließen ist auch nur selten notwendig. Besonders dann nicht, wenn man weitere Statements ausführen möchte und sie dazu wiederherstellen muss. Die Verbindung wird am Script-Ende bereits von PHP geschlossen. Man muss da nicht aufräumen. Es ist nur notwendig, sie händisch zu schließen, wenn die Verbindungen eine knappe Ressource sind und das Script noch ewig läuft, ohne sie zu nutzen. Üblicherweise hat man solch einen Fall nicht.

dedlfix.

[j4nk3y]

Hey,

Wenn du die Werte per Ausdruck in einem SQL-Statement berechnen kannst, also keine externen Daten benötigst, dann kannst du dich auf lediglich ein Update-Statement beschränken. Also gemäß diesem Prinzip:

UPDATE tabelle SET feld = feld + 1;

Doch ich brauch Werte aus 3 anderen Tabellen. Wäre dann die frage ob das mit Joins auch geht? Und leicht modifizierter rechnung etwa:

$a = $o * (1+ $e);
$b = $o * (1- $e);
$d = sqrt(pow($a * cos($alpha), 2) + pow($b * sin($alpha), 2));
$om = sqrt($G * ($sm + $pm) * ((2 / ($d * pow(10, 3))) - (1 / ($a * pow(10, 3))))) / ($o * pow(10, 3));
$alpha += $om * ( 600 ) * $t;

Nur ein paar offensichtliche Dinge kann ich ansprechen:

$get_data = $db->prepare(sprintf('SELECT x,y FROM a')); $get_data->execute();

Das sprintf() hat an der Stelle keinen Nutzen.

Stimmt, im Orginal query jedoch schon.

  $update_data = $db->prepare(sprintf('UPDATE %s SET y = %.20f WHERE id = %d', mysqli_real_escape_string($db, 'table'), $data['y'], $id));

Zudem ist es nicht sinnvoll, die Methode des Zusammenstückeln von Statement und Werten mit Prepared Statements zu mixen, ohne die Eigenschaften von Prepared Statements zu verwenden.

Das Prepared Statement habe ich auch nur eingefügt weil ich das Problem hatte das Zahlen und Floats als String aus der Datenbank kamen. Mittlerweile Versuche ich überall wo ich es nicht mache umzuschreiben.

Wenn du das Statement selbst zusammenbauen möchtest, dann kannst du mysqli_query() statt mysqli_prepare() und mysqli_stmt_execute() nehmen. Das ist kürzer und spart einen Roundtrip zwischen PHP und MySQL. Dann solltest du aber auch das mit dem Maskieren richtig machen. Ein Tabellenname ist kein Wert, sondern ein Identifier. Für Identifier gelten andere Maskierungsregeln als die Funktion mysqli_real_escape_string() berücksichtigt. Üblicherweise stehen Identifier direkt im Statement und werden nicht bei Bedarf hinzugefügt. Vermutlich gibt es deshalb keine vorgefertigte Funktion, mit der man das erledigen könnte.

Tja, dann ist das halt so.

Außerdem fehlt dir zu dem Prepare das Execute. Das Commit ist kein Ersatz und auch nur für Transactions sinnvoll.

Ganz vielleicht nutze ich ja Transactions in der db_connect_function, wer weiß. Und vielleicht wurde das Execute einfach unbeabsichtigt mit gelöscht bei dem simplifizieren des Scripts.

Und die Verbindung selbst zu schließen ist auch nur selten notwendig.

Bei Transactions zwingend notwendig.

Besonders dann nicht, wenn man weitere Statements ausführen möchte und sie dazu wiederherstellen muss.

Vielleicht ist das gerade ein zwischen Schritt den ich eingebaut habe um zu sehen wie lange welcher Teil braucht und anderen Scripten zwischendurch die Möglichkeit zu geben mit der Datenbank zu interagieren.

Die Verbindung wird am Script-Ende bereits von PHP geschlossen.

Stimmt.

Gruß
Jo

[dedlfix]

Tach!

Wenn du das Statement selbst zusammenbauen möchtest, dann kannst du mysqli_query() statt mysqli_prepare() und mysqli_stmt_execute() nehmen. Das ist kürzer und spart einen Roundtrip zwischen PHP und MySQL. Dann solltest du aber auch das mit dem Maskieren richtig machen. Ein Tabellenname ist kein Wert, sondern ein Identifier. Für Identifier gelten andere Maskierungsregeln als die Funktion mysqli_real_escape_string() berücksichtigt. Üblicherweise stehen Identifier direkt im Statement und werden nicht bei Bedarf hinzugefügt. Vermutlich gibt es deshalb keine vorgefertigte Funktion, mit der man das erledigen könnte.

Tja, dann ist das halt so.

Wenn es keine Funktion gibt, heißt das nicht, dass du die Regeln, die für einen ordnungsgemäßen Identifier gelten, ignorieren darfst. Du musst dir dann eben selbst eine Funktion schreiben, wenn es nicht mit einem simplen str_replace() oder ähnlichem geht. Die eigentliche Frage ist aber: Hast du denn hier die Notwendigkeit, einen Identifier aus einer Variable hinzufügen zu müssen, deren Inhalt potentiell Sonderzeichen enthalten kann - in dem Fall Backticks, die als Begrenzer für Identifier definiert sind? Es wäre in einem solchen Fall auch sinnvoll, die Backticks im Statement nicht zu vergessen.

Außerdem fehlt dir zu dem Prepare das Execute. Das Commit ist kein Ersatz und auch nur für Transactions sinnvoll.

Ganz vielleicht nutze ich ja Transactions in der db_connect_function, wer weiß. Und vielleicht wurde das Execute einfach unbeabsichtigt mit gelöscht bei dem simplifizieren des Scripts.

Vielleicht, wer weiß. Ist das aber ein Schuh, den ich mir anziehen muss?

Und die Verbindung selbst zu schließen ist auch nur selten notwendig.

Bei Transactions zwingend notwendig.

Ich arbeite nicht mit Transactions, aber das halte ich doch für ein Gerücht. Dazu konnte ich bei meiner kurzen Recherche nichts finden. Wenn es nur darum geht, aus dem Transaktionsmodus wieder rauszukommen, dann kann man auch mysqli_autocommit(true) aufrufen.

Besonders dann nicht, wenn man weitere Statements ausführen möchte und sie dazu wiederherstellen muss.

Vielleicht ist das gerade ein zwischen Schritt den ich eingebaut habe um zu sehen wie lange welcher Teil braucht und anderen Scripten zwischendurch die Möglichkeit zu geben mit der Datenbank zu interagieren.

Vielleicht. Vielleicht verliere ich aber auch grad die Lust aufgrund dieser schnippischen Antworten auf das eigentliche Problem einzugehen.

dedlfix.

[j4nk3y]

Hey,

Wenn es keine Funktion gibt, heißt das nicht, dass du die Regeln, die für einen ordnungsgemäßen Identifier gelten, ignorieren darfst. Du musst dir dann eben selbst eine Funktion schreiben, wenn es nicht mit einem simplen str_replace() oder ähnlichem geht. Die eigentliche Frage ist aber: Hast du denn hier die Notwendigkeit, einen Identifier aus einer Variable hinzufügen zu müssen, deren Inhalt potentiell Sonderzeichen enthalten kann - in dem Fall Backticks, die als Begrenzer für Identifier definiert sind? Es wäre in einem solchen Fall auch sinnvoll, die Backticks im Statement nicht zu vergessen.

Eigentlich, kommen die meisten Variablen aus der Datenbank und werden in php gesetzt und kommen nicht durch eine User Eingabe. Aber Anfangs wurde mir gesagt das ich alles ordnungsgemäß Escapen muss, also Escape ich auch einen Intenger.

Vielleicht, wer weiß. Ist das aber ein Schuh, den ich mir anziehen muss?

Nein, aber mich ärgert es einfach immer wieder, wenn etwas kritisiert oder kommentiert wird was nicht ersichtlich ist bzw der Einfachheit gekürzt wurde. Aber wenn ihr lieber 2000 Zeilen PHP code möchtet, dann kann ich auch den Posten.

Ich arbeite nicht mit Transactions, aber das halte ich doch für ein Gerücht. Dazu konnte ich bei meiner kurzen Recherche nichts finden. Wenn es nur darum geht, aus dem Transaktionsmodus wieder rauszukommen, dann kann man auch mysqli_autocommit(true) aufrufen.

Bei einem simplen Select ist das manuelle schließen unnötig, das ist richtig. Wenn man jedoch in einem Script mehrmals die Datenbankverbindung aufbaut und autocommit gerade nicht auf true setzt dann ist das schließen mit commit() respektive rollback() notwendig.

Besonders dann nicht, wenn man weitere Statements ausführen möchte und sie dazu wiederherstellen muss.

Vielleicht. Vielleicht verliere ich aber auch grad die Lust aufgrund dieser schnippischen Antworten auf das eigentliche Problem einzugehen.

Es tut mir Leid, manchmal ärgert es mich nur wenn von allen Seiten von jedem etwas anderes gesagt wird weil jeder seinen Stil über die Jahre entwickelt hat. Und vorallem so etwas wie:

$get_data = $db->prepare(sprintf('SELECT x,y FROM a')); $get_data->execute(); Das sprintf() hat an der Stelle keinen Nutzen.

Das weiß ich auch, dass es so keinen Sinn hat. Auch wenn es gut gemeint war, bei mir kommt nur an: "sprintf(%2$s %1$s %3$s %5$s %4$s, 'zulernen', 'viel', 'du', 'hast', 'noch') junger Padawan". Was ich auch weiß, und wenn es etwas gibt was ich nicht verstehe oder nicht weiß, weil ich es noch nicht gesehen oder gemacht habe, dann frage ich euch. Darum finde ich es immer besser erst einmal auf die Frage zu antworten und sich dann um mögliche schwächen und Fehler des Codes zu kümmern.

Gruß
Jo

[dedlfix]

Tach!

Eigentlich, kommen die meisten Variablen aus der Datenbank und werden in php gesetzt und kommen nicht durch eine User Eingabe. Aber Anfangs wurde mir gesagt das ich alles ordnungsgemäß Escapen muss, also Escape ich auch einen Intenger.

Es kommt vielmehr darauf an, dass du nicht nur einfach so machst, was man dir sagt, sondern dass du verstehst, warum etwas so gemacht wird. Das ist in der Regel das Bestreben der meisten Teilnehmer hier, Kompetenz zu vermitteln und nicht nur einen Befehlsempfänger zu züchten.

Maskieren muss man, weil Code und Daten gemischt werden, und man dann genau kennzeichnen muss, was Daten sind. Und wenn dann Zeichen innerhalb der Daten vorkommen, die als Code interpretiert werden könnten, schreibt man die in einer besonderen Form, so dass genau das nicht passiert. Üblicherweise handelt es sich bei den zu maskierenden Zeichen um die Zeichen, die man als Begrenzer für die Daten verwendet, also meist Anführungszeichen. Wenn du keine solchen Start- und Ende-Zeichen um einen Wert stehen hast, dann hast du da nicht in den Datenmodus geschaltet, bei dem verhindert werden muss, das man ihn durch Datenbestandteile fälschlicherweise verlassen kann. Stattdessen bleibt man da im Code-Modus und muss anderweitig dafür sorgen, dass Datenliterale korrekt notiert werden, die nicht mit Begrenzungszeichen eingerahmt werden. Auch hilft es nicht, falsche Zeichen zu maskieren, weil möglicherweise die Maskierzeichen wörtlich genommen werden, oder in dem Kontext eine ganz andere Bedeutung haben.

Nein, aber mich ärgert es einfach immer wieder, wenn etwas kritisiert oder kommentiert wird was nicht ersichtlich ist bzw der Einfachheit gekürzt wurde. Aber wenn ihr lieber 2000 Zeilen PHP code möchtet, dann kann ich auch den Posten.

Nicht unbedingt, wenn du keine Revision dessen möchtest. Nur ist es so, dass man aus Weggelassenem nicht erkennt, dass das eigentlich doch da ist. Es wäre dann sinnvoll, wenn du das kenntlich machst, dass da was entfernt wurde. Sinnentstellende oder die eigentliche Struktur verändernde Kürzungen sind auch nicht hilfreich, um die eigentlichen Fehler im Code zu finden.

Bei einem simplen Select ist das manuelle schließen unnötig, das ist richtig. Wenn man jedoch in einem Script mehrmals die Datenbankverbindung aufbaut und autocommit gerade nicht auf true setzt dann ist das schließen mit commit() respektive rollback() notwendig.

Wie gesagt, nein. Du kannst den Transaktionsmodus mit dem Setzen des Autocommit-Modus auf true ebenfalls verlassen, ohne die Verbindung zu schließen. Du hast hier ein Performance-Problem, und da ist es nicht zuträglich unnötige Aktionen vorzunehmen. Es sieht in dem gekürzten Code jetzt nicht so aus, als ob du die Verbindung in der Schleife ständig öffnest und schließt. Man merkt die Verzögerung auch nicht im normalen Betrieb, weil das Öffnen eigentlich schnell geht. Aber ich hatte es mal, dass PHP und der Datenbankserver an jeweils anderen Ecken der Welt standen, und da merkt man deutlich die laufzeitbedingte Verzögerung. Jeder Funktionsaufruf (Verbinden, Kodierung setzen, Datenbank auswählen) musste da einmal um die Welt reisen. Insofern läppert sich auch ständiges unnötiges Schließen-Öffnen im lokalen Betrieb zu einer Größe zusammen.

Auch wenn es gut gemeint war, bei mir kommt nur an: "sprintf(%2$s %1$s %3$s %5$s %4$s, 'zulernen', 'viel', 'du', 'hast', 'noch') junger Padawan". Was ich auch weiß, und wenn es etwas gibt was ich nicht verstehe oder nicht weiß, weil ich es noch nicht gesehen oder gemacht habe, dann frage ich euch.

Ja, aber ich weiß nicht, was du schon alles weißt und was nicht. Ich sehe nur das Offensichtliche. Auch wenn ich dir vielleicht schon ein paar mal geantwortet habe, merke ich mir nicht unbedingt deinen Wissensstand, den du in vorherigen Postings gezeigt hast. Ärgern hilft dir nicht weiter. Du darfst dann auch einfach die nebensächlichen Punkte ignorieren.

Darum finde ich es immer besser erst einmal auf die Frage zu antworten und sich dann um mögliche schwächen und Fehler des Codes zu kümmern.

Ja, das ist auch mein Bestreben. Nur beschränkt sich das mitunter erstmal auf eine Rückfrage oder eine ganz allgemeine Antwort, weil das eigentliche Problem nicht klar wurde oder Fakten zum Verstehen fehlen. In einem solchen Fall alles andere unbeachtet zu lassen, und erst sehr viel später (Tage, Wochen, gar nicht - je nachdem, wie aktiv der Probleminhaber mitarbeitet) zu behandeln, ist auch nicht die Lösung.

So, genug der Erklärungsversuche dieser Art und meinerseits, das Fachliche werde ich an anderer Stelle fortsetzen.

dedlfix.

[dedlfix]

Tach!

Wenn du die Werte per Ausdruck in einem SQL-Statement berechnen kannst, also keine externen Daten benötigst, dann kannst du dich auf lediglich ein Update-Statement beschränken. Also gemäß diesem Prinzip:

UPDATE tabelle SET feld = feld + 1;

Doch ich brauch Werte aus 3 anderen Tabellen. Wäre dann die frage ob das mit Joins auch geht? Und leicht modifizierter rechnung etwa:

$a = $o * (1+ $e);
$b = $o * (1- $e);
$d = sqrt(pow($a * cos($alpha), 2) + pow($b * sin($alpha), 2));
$om = sqrt($G * ($sm + $pm) * ((2 / ($d * pow(10, 3))) - (1 / ($a * pow(10, 3))))) / ($o * pow(10, 3));
$alpha += $om * ( 600 ) * $t;

Ja, prinzipiell kann man bei einem UPDATE auch andere Tabellen joinen. Nur ein Subselect auf dieselbe Tabelle geht bei einem Update nicht, aber das wirst du hier nicht brauchen. Die Länge der Formel sollte auch kein Problem sein, es wird nur unübersichtlich und du musst sie dann als Einzeiler schreiben. Lediglich einmalig berechnete Werte könnte man vor dem UPDATE berechnen und in einer User-defined Variable ablegen.

Alternativ kannst du die Berechnung in eine Stored Function auslagern. Siehe Stored Programs and Views und CREATE PROCEDURE and CREATE FUNCTION Syntax.

dedlfix.

[Achot]

Hallo,

ist das zweite

include('db_connect_function.php');

vor

foreach($data as $id => $set){

nicht überflüssig? Ich meine, die Datei wird doch ganz oben bereits eingebunden?

[dedlfix]

Tach!

ist das zweite

include('db_connect_function.php');

vor

foreach($data as $id => $set){

nicht überflüssig? Ich meine, die Datei wird doch ganz oben bereits eingebunden?

Das kommt darauf an, ob darin sofort ausführbarer Code drinsteht oder nur beispielsweise Funktionen.

Anscheinend wird darin die Datenbankverbindung aufgebaut. Guter Stil ist das jedenfalls nicht, denn man sieht nicht, was der Aufruf bewirkt. Besser wäre, wenn der Aufbau in einer Funktion geschähe, die man aufruft und das DB-Handle bekommt, à la $db = open_database_connection();. Das würde durch den sprechenden Namen der Funktion besser verdeutlichen, was an der Stelle geschieht.

dedlfix.

[j4nk3y]

Hey,

ist das zweite

include('db_connect_function.php');

vor

foreach($data as $id => $set){

nicht überflüssig? Ich meine, die Datei wird doch ganz oben bereits eingebunden?

Das kommt darauf an, ob darin sofort ausführbarer Code drinsteht oder nur beispielsweise Funktionen.

Anscheinend wird darin die Datenbankverbindung aufgebaut. Guter Stil ist das jedenfalls nicht, denn man sieht nicht, was der Aufruf bewirkt. Besser wäre, wenn der Aufbau in einer Funktion geschähe, die man aufruft und das DB-Handle bekommt, à la $db = open_database_connection();. Das würde durch den sprechenden Namen der Funktion besser verdeutlichen, was an der Stelle geschieht.

Ja das hab ich mir auch schon überlegt, aber als ich das geschrieben hab war ich noch nicht so weit (gemeint sind Programmierkentnisse).

Gruß
Jo