Ankündigung

Einklappen
Keine Ankündigung bisher.

Update-Performance bei Tabellen mit vielen Einträgen

Einklappen
X
  • Filter
  • Zeit
  • Anzeigen
Alles löschen
neue Beiträge

  • #16
    Zitat von Niko310391 Beitrag anzeigen
    Müsste dann ja anhand deines Beispiels wie folgt aussehen, richtig?


    Zwei Fragen bleiben dann aber noch:

    1. An welcher Stelle muss ich den Befehlt dann überhaupt ausführen? Ich befinde mich ja in einer while-Schleife, wo die Effekte sich ja in Abhängigkeit vom Spieler immer verändern? Dann müsste ich hier erneut mit Arrays arbeiten, oder?
    Du verzichtest auf das holen der Daten und auf das ganze Schleifen-Gedöhns. Die Daten für die Spieler etc. bekommst Du, indem Du die Tabellen beim Update mit einbindest, joinst. Ich habe keinen Bock, Deine Logig da zu ergründen, daher schrieb ich ja auch, Du sollst Dir das selber mal zusammenstellen.

    2. Wie sieht es mit dem anderen Update-Befehl aus? Kann man diesen auch performanter durchführen?
    Sicher. Selbes Prinzip.

    Bei Datenbanken vermeidet man Schleifen. Das mag mit 3 oder 30 Datensätzen noch funktionieren, mit richtigen Datenbanken und Anwendungen, wo z.T. Milliarden von Rows in einer Tabelle sind, nicht mehr.
    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

    Kommentar


    • #17
      Sorry, wenn ich frage, aber für 1. habe ich wirklich keine Lösung, wie ich das machen soll. Daher wäre ich dir wirklich extrem dankbar, wenn du mir hier einmal einen Lösungsweg aufzeigen könntest, da ich hier echt keinen blassen Schimmer habe...

      Kommentar


      • #18
        *seufz*

        Ist das so schwer? Mal ein kleines Fingerspiel:

        Code:
        test=# create table a(id int primary key, val_a int);
        CREATE TABLE
        test=*# create table b(id int primary key, a_id int references a, val_b1 int, val_b2 int, result int default 0);
        CREATE TABLE
        test=*# copy a from stdin;
        Enter data to be copied followed by a newline.
        End with a backslash and a period on a line by itself, or an EOF signal.
        >> 1    11
        >> 2    22
        >> 3    33
        >> \.
        COPY 3
        test=*# copy b (id, a_id, val_b1, val_b2) from stdin;
        Enter data to be copied followed by a newline.
        End with a backslash and a period on a line by itself, or an EOF signal.
        >> 1    1    33    44
        >> 2    2    55    11
        >> 3    3    47    11
        >> \.
        COPY 3
        test=*# select * from a;
         id | val_a
        ----+-------
          1 |    11
          2 |    22
          3 |    33
        (3 rows)
        
        test=*# select * from b;
         id | a_id | val_b1 | val_b2 | result
        ----+------+--------+--------+--------
          1 |    1 |     33 |     44 |      0
          2 |    2 |     55 |     11 |      0
          3 |    3 |     47 |     11 |      0
        (3 rows)
        
        test=*# commit;
        COMMIT
        test=# update b set result = a.val_a + b.val_b1 + a.val_a * b.val_b2 from a where b.a_id = a.id;
        UPDATE 3
        test=*# select * from b;
         id | a_id | val_b1 | val_b2 | result
        ----+------+--------+--------+--------
          1 |    1 |     33 |     44 |    528
          2 |    2 |     55 |     11 |    319
          3 |    3 |     47 |     11 |    443
        (3 rows)
        
        test=*#
        PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

        Kommentar


        • #19
          Mir erklärt sich allerdings nicht, inwieweit mir das helfen soll...

          Kurz mal zum Verständnis, was mein Skript wirklich macht:

          Es wird eine Abfrage ausgeführt, welches alle aktiven Vereine holt...innerhalb dieser Abfrage hole ich mir den Array mit den Vereinsspielern, wo ich dann für jeden Spieler einzeln den Trainingseffekt (Fitness, Frische und Fähigkeiten) berechne und abschließend in die Datenbank schreibe...
          Jetzt frage ich mich, wie ich in diesem Fall ohne die Schleifen auskommen kann?!


          Es könnte übrigens noch eine andere Möglichkeit geben:

          Zum Spielstart / beim Laden eines Spielstandes werden sämtliche Daten in Arrays / Objekte geschrieben und im Spielstand selbst wird nur noch mit diesen gearbeitet (keine Datenbank-Verbindungen mehr). In die Datenbank wird dann nur noch geschrieben, wenn der Benutzer wirklich den Spielstand speichert (dann würde sämtliche Arrays einmal in die Datenbank übertragen werden).
          Allerdings fehlt mir hier der Ansatz, wie die Daten aus den Arrays / Objekten im Spielstand verfügbar sein könnten.

          Kommentar


          • #20
            Es wird eine Abfrage ausgeführt, welches alle aktiven Vereine holt...innerhalb dieser Abfrage hole ich mir den Array mit den Vereinsspielern, wo ich dann für jeden Spieler einzeln den Trainingseffekt (Fitness, Frische und Fähigkeiten) berechne und abschließend in die Datenbank schreibe...
            Jetzt frage ich mich, wie ich in diesem Fall ohne die Schleifen auskommen kann?!
            Fass bitte mal kurz zusammen wie du denn die Trainingseffekte berechnest. Ich schließe mich Akretschmer an, die 100+ Codezeilen zu lesen um herauszufinden was da eigentlich alles gemacht wird... neeee lieber nicht Dafür sind die Benamungen auch teilweise zu kryptisch - $arTT kann man imo nur zuordnen wenn man eh weiß worum es geht

            PHP-Code:
                    ### bei eigenem Team wird dann kein Training durchgeführt ###
                    
            if($db->teamid == $teamid):
                        
            $effect_fitness     = -8;
                        
            $effect_freshness     28;

                        foreach(
            $arPlayer AS $playerid => $key):
                            
            ### Fitness darf nicht unter 0 sinken ###
                            
            if(($key["fitness"] + $effect_fitness) < 0):
                                
            $fitness 0;
                            else:
                                
            $fitness $key["fitness"] + $effect_fitness;
                            endif;

                            
            ### Frische darf nicht über 100 steigen ###
                            
            if($key["freshness"] + $effect_freshness 100):
                                
            $freshness 100;
                            else:
                                
            $freshness $key["freshness"] + $effect_freshness;
                            endif;

                            
            $arUpdatePlayers[$playerid]["fitness"]         = $fitness;
                            
            $arUpdatePlayers[$playerid]["freshness"]     = $freshness;
                        endforeach; 
            Der Codeabschnitt sieht gut greifbar aus, wenn an der Stelle wirklich schon die Werte bestimmt werden, kannst du das so in der Datenbank lösen... ich weiß aber nicht wie die folgenden 100 Zeilen noch was verändern.


            Abschließend noch. Wenn der Usecase wirklich so aussieht, dass da für x-tausend Datensätze individuell etwas geprüft und berechnet werden muss... ist das halt so, passiert schonmal.
            Aber ist das denn etwas was der User bemerken muss? Könntest du die Berechnungen im "Hintergrund" (z.B. per Cron Nachts) irgendwo laufen lassen und die Ergebnisse seperat irgendwo speichern?
            Relax, you're doing fine.
            RTFM | php.de Wissenssammlung | Datenbankindizes | Stay fit

            Kommentar


            • #21
              Mal angenommen die Logik ist einfach erstmal:

              Wenn ein Training stattfand: Fittness +8 Frische -8
              Wenn kein Training stattfand: Fittness -8 Frische +8

              Code:
              UPDATE players p
              JOIN playerteams t ON p.playerid = t.player_id
              LEFT JOIN teamtrainings tt ON t.team_id = tt.team_id AND datum = CURDATE()
              SET p.fitness = (CASE
                   WHEN p.fitness + (@effect_fitness := (CASE
                                                             WHEN tt.team_id IS NULL THEN -8
                                                             ELSE 8
                                                         END)) < 0 THEN 0
                   WHEN p.fitness + @effect_fitness > 100 THEN 100
                   ELSE p.fitness + @effect_fitness
               END),
              p.freshness = (CASE
                     WHEN p.freshness + (@effect_freshness := (CASE
                                                                   WHEN tt.team_id IS NULL THEN 8
                                                                   ELSE -8
                                                               END)) < 0 THEN 0
                     WHEN p.freshness + @effect_freshness > 100 THEN 100
                     ELSE freshness + @effect_freshness
                 END)
              Das ganze lässt sich natürlich mit beliebig vielen weiteren Variablen, Tabellen und CASE WHENs fortführen. (Auch wenn das sicher schnell sehr unübersichtlich wird, wenn man nicht alles vernünftig einrückt.)
              Ich habe es allerdings nicht testen können und würde mich wundern, wenn das so ohne Anpassungen bei dir funktioniert...

              Kommentar


              • #22
                Zitat von Niko310391 Beitrag anzeigen
                Mir erklärt sich allerdings nicht, inwieweit mir das helfen soll...

                Kurz mal zum Verständnis, was mein Skript wirklich macht:

                Es wird eine Abfrage ausgeführt, welches alle aktiven Vereine holt...innerhalb dieser Abfrage hole ich mir den Array mit den Vereinsspielern, wo ich dann für jeden Spieler einzeln den Trainingseffekt (Fitness, Frische und Fähigkeiten) berechne und abschließend in die Datenbank schreibe...
                Jetzt frage ich mich, wie ich in diesem Fall ohne die Schleifen auskommen kann?!
                Verstehe das Prinzip eines Updates!
                Am einfachsten:
                Ausgehend von einem Select Befehl kannst Du Dir vorstellen, dass das Select Statement verschieden viele Werte zurück liefert, abhängig von Datenmenge und Abfragekriterien (WHERE Clause)
                Genau das gleiche macht das UPDATE, es betrifft per Definition nicht einen einzelnen Datensatz, sondern soviel DS wie die WHERE Bedingung beschreibt. Also z.B. alle Spieler (Fitnessdaten), die noch aktiv sind, über 18, männlich, .. etc

                Wenn die Update Menge per WHERE Clause (richtig) eingeschränkt ist, kommt das SET
                Hier werden alle Spalten geändert, die Du dort reinnimmst. Dafür stehen oben bereits Beispiele. Die Änderungen sind flexibel, CASE, IF, etc.

                Beispiel
                PHP-Code:
                Update player
                  set BMI 
                Gewicht/Square(Groesse
                Update aller BMI Werte für alle Datensätze, bedingungslos. Gewicht und Groesse müssen dabei befüllte Spalten in der Tabelle sein.

                für alle aktiven Spieler:

                PHP-Code:
                Update player
                   set BMI 
                Gewicht Square(Groesse)
                 
                where aktiv true 
                für Männer und Frauen getrennt:
                PHP-Code:
                Update player
                   set BMI 
                = case
                               
                when sex 'm' then Gewicht Square(Groesse)
                               
                when sex 'w' then 'andere Formel' -- hab keine Ahnung von sowas
                             end
                 where aktiv 
                true 

                Fazit: Vergiß die Schleife, die macht das Update über alle Datensätze passend zur WHERE Clause von alleine.
                Du schickst nur das Statement. Es werden keine Daten von PHP ausgelesen und keine verarbeitet und keine zurückgeschickt. Das geht sehr schnell, versprochen.

                Die Fragen an Dich zur Logik oben sind keine Fragen nach PHP Code, sondern nach den Formeln und Bedingungen für das Update, also pure Anforderungsbeschreibung.
                Nebenbei: Wenn Du mit Worten und harten Formeln beschreibst, was geändert werden soll, spart sich ein hilfreicher Geist, der sich evtl. durch Dein PHP Code quält, u.U. sogar, Fehler aus Deinem PHP Code in das Update Statement zu übertragen.

                Schleifen im Client zum Update von Tabellendaten zählen m.E. zu den größten Missverständnissen beim Zugriff auf Datenbanken.Die Kunst ist im Zweifel, alle notwendigen Daten für das Update 'parat' zu haben. Liegen die (teilweise) außerhalb der Datenbank oder haben keine Relation zum Update, ist man natürlich in den Arm gekniffen. Könnte man auch als Designfehler bezeichnen.

                Kommentar


                • #23
                  Meister1900 Wenn die Logik so einfach wäre, dürfte das so machbar sein, aber geht das auch, wenn ich in Abhängigkeit von Team & Spieler die Effekte berechne? Diese Berechnung läuft ja aktuell über PHP (aus Basis von Werten aus Arrays, die größtenteils durch die Datenbank gefüllt werden).

                  Perry Staltic Also die Logik ist eigentlich nicht so kompliziert...ich schreibe sie dir hier mal grob auf:

                  1. Hole alle aktiven Vereinsteams (der SELECT-Query zu Beginn mit der while-Schleife)
                  2. Guck, ob für dieses Team am entsprechenden Tag Trainingseinheiten definiert sind (werden in $arUnits geschrieben)
                  2.1. Wenn nein, dann schau ob es das eigene Team (dann 4x die Einheit "Kein Training") oder ein KI-Team (dann die 4 Einheiten aus dem Default Trainingsplan) ist
                  2.2. Wenn ja, dann lade die 4 Einheiten aus dem eingestellten Training
                  3. Hole alle verfügbaren Spieler des jeweiligen Teams (Verletzte usw. werden hier ausgenommen)
                  4. Hole die Auswirkungen auf Fitness & Frische (diese sind abhängig von Trainings-Intensität des Spielers sowie seines aktuellen Fitnesszustandes) sowie die Fähigkeiten-Fortschritte (abhängig von Alter, Talent, Fitness & Trainings-Intensität des Spielers) durch die jeweiligen Einheiten
                  5. Abschließende Update-Queries für jeden Spieler jedes Teams

                  Kommentar


                  • #24
                    Zitat von Perry Staltic Beitrag anzeigen


                    Beispiel
                    PHP-Code:
                    Update player
                    set BMI 
                    Gewicht/Square(Groesse
                    Update aller BMI Werte für alle Datensätze, bedingungslos. Gewicht und Groesse müssen dabei befüllte Spalten in der Tabelle sein.

                    für alle aktiven Spieler:

                    PHP-Code:
                    Update player
                    set BMI 
                    Gewicht Square(Groesse)
                    where aktiv true 

                    Mal vom Thema des Threads abgesehen, ist das ein excellentes Beispiel für etwas, was man nicht machen sollte. Der BMI wäre beim Select berechenbar, eine extra Spalte dafür ist redundant und damit ein ganz klares Zeichen von Faildesign.
                    PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

                    Kommentar


                    • #25
                      Zitat von akretschmer Beitrag anzeigen
                      ist das ein excellentes Beispiel für etwas, was man nicht machen sollte. Der BMI wäre beim Select berechenbar, eine extra Spalte dafür ist redundant und damit ein ganz klares Zeichen von Faildesign.
                      Ja, richtig, ich habe ja ganz klar geschrieben, was mein Anliegen war, Updates verstehen, Schleifen vergessen. Redundanzfreiheit war es nicht, da mir das Verständnisproblem des Updatemechnismus viel grafierender scheint.

                      Aber gut, dass Du es ansprichst.
                      Jedes Update, das aus eigenem Datenbestand heraus erfolgen kann, impliziert, dass redundante Daten vorliegen. Dies gilt vermutlich auch für das Anliegen des TE.
                      Umgekehrt kann man also sagen, alle Updates auf Berechnungsbasis könnte man sich sparen. Das ist pauschal natürlich auch gar nicht verkehrt.

                      Damit kommt man dann sehr stark zu Design Fragen, Performanceproblemen usw.. Es ist ja legitim, redundant zu arbeiten, wenn man entsprechend abgesichert ist im Modell und der Performancegewinn, die Redundanzüberwachung deutlich übersteigt. Man muss halt wissen, was man tut.

                      Und lass mal kurz sehen, wie ist das in Deinem Beispiel?

                      Kommentar


                      • #26
                        Zitat von Niko310391 Beitrag anzeigen
                        Perry Staltic Also die Logik ist eigentlich nicht so kompliziert...ich schreibe sie dir hier mal grob auf:
                        ...
                        Ok, also einfache Antwort für Dein Problem:
                        Baue ein Select Statement, das alle benötigten Daten aus Schritt 1-4 zusammenfasst (join, Berechnung, Bedingungen, ..) und benutze es als Datenquelle für Dein Update, schränke die Update Menge so ein, dass sie den gewünschten Kriterien entspricht.
                        Dafür gab es ja hier auch schon Vorschläge.

                        Skizze
                        PHP-Code:
                        with quellDaten (großes Selectstatement inklPK ID der updatebedürftigen Zielspalte)
                        update ziel
                           set 
                        .. (hier kannst Du Dir aussuchenob Du konditionale Werte erst hier berechnest oder schon oben im With
                        from quellDaten
                        where ziel
                        .id=quellDaten.id
                          
                        and ..(ggFweitere Bedinungen, die im With nicht (gutabgebildet werden können
                        Das ist ein(1)! Statement für alles, wie oben gesagt.
                        (kann sich je nach DB syntaktisch unterscheiden)

                        Kommentar


                        • #27
                          Perry Staltic Kannst du mir bei diesem Befehl vielleicht behilflich sein? Ich habe noch nie mit solchen großen SQL Statements gearbeitet und bin diesbezüglich daher unerfahren

                          Kommentar


                          • #28
                            Fnagen wir doch mal ganz einfach an aus deinem Beispiel vom 1. Beitrag.
                            PHP-Code:
                            foreach($arUpdatePlayers AS $playerid => $key):
                                
                            mysqli_query($connection,"UPDATE players SET fitness = '".$key["fitness"]."', freshness = '".$key["freshness"]."' WHERE playerid = '$playerid'");
                            endforeach; 
                            Hier sollen also eine bestimmte Menge an Spielern(hier mit playerid näher bezeichnet) aktualisiert werden.

                            Welche Bedingungen müssen die Spieler erfüllen, damit die Spalten fitness und freshness aktualisiert werden?

                            .

                            Kommentar


                            • #29
                              Das:
                              Code:
                              SELECT .., players.birthdate, ...
                              PHP-Code:
                                      ### Berechnung Alter ###
                                      
                              $date1 date("d.m.Y"$db_players->birthdate);
                                      
                              $gebtag explode (".",$date1);    

                                      
                              $year     date("Y",$date);  
                                      
                              $month     date("n",$date);
                                      
                              $day     date("d",$date);

                                      
                              $age $year $gebtag[2];  
                                      if(
                              $month <= $gebtag[1]):
                                          if(
                              $month == $gebtag[1]):
                                              if(
                              $day $gebtag[0]):
                                                  
                              $age $age 1;
                                              endif;
                                          else:
                                              
                              $age $age 1;
                                          endif;
                                      endif; 

                              Geht auch direkt einfacher in SQL zB in MySQL:
                              Code:
                               SELECT ..., TIMESTAMPDIFF(YEAR, birthdate, CURDATE()) AS age, ...

                              Und nutze Aliase und Formattierung... macht alles um einiges schlanker und lesbarer, dann hilft man dir hier ev. auch leichter

                              Also statt:

                              PHP-Code:
                               $query_players mysqli_query($connection,"SELECT players.playerid, players.birthdate, players.talent, players.fitness, players.freshness, players.training_intensity FROM players INNER JOIN playerteams ON players.playerid = playerteams.player_id WHERE playerteams.team_id = '$db->teamid' AND (playerteams.begin IS NOT Null AND playerteams.end IS Null)"); 

                              Sowas..
                              PHP-Code:
                              $sql "

                              SELECT
                                  p.playerid, TIMESTAMPDIFF(YEAR, p.birthdate, CURDATE()) AS age,
                                  p.talent, p.fitness, p.freshness, p.training_intensity

                              FROM
                                  players p

                              INNER JOIN
                                  playerteams pt ON p.playerid = pt.player_id

                              WHERE
                                  pt.team_id = '
                              $db->teamid' AND (pt.begin IS NOT Null AND pt.end IS Null)

                              "
                              ;

                              $query_players mysqli_query($connection$sql); 
                              Debugging: Finde DEINE Fehler selbst! | Gegen Probleme beim E-Mail-Versand | Sicheres Passwort-Hashing | Includes niemals ohne __DIR__
                              PHP.de Wissenssammlung | Kein Support per PN

                              Kommentar


                              • #30
                                Zitat von Niko310391 Beitrag anzeigen
                                Ich habe noch nie mit solchen großen SQL Statements gearbeitet und bin diesbezüglich daher unerfahren
                                Das bekommst Du schon hin, Du hast es ja auch mit PHP geschafft! Zauberformel: Divide and Conquer, teile und herrsche.
                                Alles in kleine Problem zerlegen und lösen.

                                Du wirst sehen, es lohnt sich (Performance)!
                                Ich hab leider heute kaum Zeit und danach gar nicht, für mehr Details. Aber ich würde mal tippen, je mehr konkrete Tabellenangaben (Create Statement), Beispieldaten (Insert Statements) und eigene Vorarbeit von Dir hier kommt, wird sicher jemand hier weiter helfen.

                                Du hast 5 Punkt aufgeführt, 4 davon sind Daten sammeln.

                                Nimm Dir Punkt 1-4 und erstelle für jeden einzeln das notwendige select. Also alle Spalten die für Berechnung, Bedingungen, join gebraucht werden, sowie richtige Begrenzung der Mengen.
                                Dann Joinst Du die 4 Mengen.

                                Du kannst jetzt mit der Basismenge weitermachen und die entgültigen Updatewerte darin mit aufnehmen, also schon das Ergebnis sehen.
                                Das nutzt Du nun für Dein Update.

                                Wenn alles läuft, kannst Du am Ende optimieren und doppelte Selektion wieder ausbauen und ggF. weiter tunen. Mit einem neu aufgebauten, funktionierenden, geprüften Update, kannst Du immer querchecks nach einem Optimierungsschritt machen. Wichtig ist erstmal ein validiertes Ergebnis.
                                Du könntest auch einen Vergleich fahren, PHP gegen SQL und die Ergebnisse gegeneinander abtragen.

                                Du kannst die vielen Vorschläge hier zur Selektion, Join usw. sicher dabei als Hilfestellung verwenden.

                                Zu der With Geschichte noch ein technischer Hinweis: With eher einsetzen, wenn Updatemenge und With Select Menge nicht zu stark divergieren. Also riesen With Clause (Datenmenge) und am Ende Update eines Datensatzes bringt es nicht so. Umgekehrt ist wahrscheinlich unproblematisch, aber hier nicht zu erwarten.
                                Und überhaupt, hab jetzt grad kein Plan, welche DB Du nutzt. with geht nur, wenn die DB das kann, aber seit 8 ist es ja wohl auch bei mysql drin.

                                Kommentar

                                Lädt...
                                X