PerformanceTest: SQL Performance

Hallo,

ich stelle mir die Frage, wie ich am Besten eine Datenbank realisiere. Das konzept ist folgendes:

Ich habe eine Anleitung für Produkte mit AnleitungsID, Titel, Anleitungstext, Material. Diese Anleitung kann für mehrere Produkte genutzt werden. Ich sage mal durchschnittlich 20. Und diese Anleitung kann bewertet werden.

Die Struktur sieht also folgendermaßen aus:
AnleitungsID | AutorID | Titel | Anleitungstext | Material | Produkt | Bewertung | Datum

Dafür habe ich auf die Spalte Produkt einen Primary Key und Volltextsuche gesetzt. D.h. ich schreibe alle Produkt IDs, die zu dieser passen, in das Feld Produkte und trenne es z.B. durch ein Semikolon. Das gleiche mache ich für die Spalte Bewertung mit den USERIDs, nur dass ich diese ja nicht durchsuchen muss, sondern nur die IDs speichere. Daher hier keine Volltextsuche.

Produkt = 400;323;456;324;900;5000;372;1;19;usw;

Möglicherweise sähen Bewertungen auch so aus:

Bewertung = 400|1;200|2;100|4;usw;

Ich lese die Bewertungen ja nur aus. In diesem Falle würde die erste Ziffer die ID des Nutzers sein getrennt durch einen Strich von seiner abgegebenen Bewertung. Also hier Nutzer mit ID 400 gibt 1 Bewertungspunkt ab, Nutzer mit ID 200 gibt schon 2 Bewertungspunkte ab. Das ganze muss ja dann nur ausgelesen werden, wenn ich die benötigten Anleitungen gefunden habe. Statt dem | würde ich sogar zu einem => tendieren, um das so direkt in Arrays zu schreiben wie auch immer, je nach bedarf.

In meinem Test bei 10.000 Datensätzen (Anleitungen) und genau 1000 Produkt IDs in dem Feld, habe ich bei Volltextsuchen 0.0004 Sekunden benötigt um Datensätze zu erhalten, die einer bestimmten Produkt ID entsprachen.

Jetzt ist aber ein Freund gekommen und meinte, es ginge anders schneller. Und zwar indem ich mehrere Tabellen mache und dann bei der Abfrage durch joins verbinde. Quasi sähe die Struktur dann so aus:

Anleitungen:
AnleitungsID | AutorID | Titel | Anleitungstext | Material | Datum

Bewertungen:
BewID | AnleitungsID | UserID | Bewertungspunkte

AnleitungenProdukte
ProduktID | AnleitungsID

Diese hätte ich auch bei der anderen Variante:
Produkte
ProduktID | Eigenschaften... | Eigenschaften... | Eigenschaften...

Prinzip 3 Tabellen die ich bei allen Abfragen beachten muss gegen eine. Plus den zusätzlichen Programmieraufwand, wenn ich z.B. Datensätze löschen möchte. Sagen wir mal das wäre gegenüber der Performance außer Acht zu lassen.

Nur was ich vorher ich sage mal mit 10000 Datensätzen geschafft habe, wo ich meine 20 Produkt IDs drinne habe, brauche ich ja nach dieser Tabellenordnung die mein Freund vorschlägt, 10000 mal 20 Datensätze, also 200000 Stück um das gleiche abzubilden. Und zwar satte 2 mal, also 400.000 Datensätze. Hier sind die Bewertungen noch garnicht mit drinne, wobei das noch sehr moderat ausfiele, kommt eben auf die Anzahl der Bewertungen an. Wenn es 4 Bewertungen sind, wen juckt das schon. Aber 5000 Bewertungen sind auch wieder 5000 Datensätze und rechnet man das auf 10.000 Anleitungen hoch, stehe ich mit 50.000.000 Datensätzen da in einer Tabelle. Nicht dass eine Anleitung soviele Bewertungen erhielte, aber es streckt sich ja über die Anzahl der Anleitungen hinweg.

Noch dazu die joins, die Tabellen die angesprochen werden müssen.

Es würde mich interessieren, wie ihr das seht, ob ihr schon Erfahrung mit so Sachen habt. Ist das wirklich schneller? Um wie viel? Wie sieht es bei sehr vielen Datensätzen aus?

MfG,
Rolf

  1. Tach!

    Es würde mich interessieren, wie ihr das seht, ob ihr schon Erfahrung mit so Sachen habt. Ist das wirklich schneller? Um wie viel? Wie sieht es bei sehr vielen Datensätzen aus?

    Wenn das DBMS (trotz aller Optimierungen) zu langsam ist, kannst du das mit Hardware beschleunigen. Mit Programmierern geht das nicht. Man verwendet auch gern ORMs oder andere Zwischenschichten, die per se langsamer sind als direkte Abfragen, hat dann aber teure Entwicklungsarbeit gespart, weil du dann ohne Gefummel an die zugehörigen Daten kommen kannst. Wenn du keinen sehr guten Grund hast, in einem relationalen Datenbanksystem auf die Relationen zu verzichten, dann lass es lieber. Es ist darauf ausgelegt und optimiert.

    Ob nun die eine oder die andere Struktur schneller ist, kann man so pauschal nicht sagen. Und es ist auch nicht ganz einfach, das zu testen. Der Optimizer entscheidet nach verschiedenen Kriterien, wie er eine Abfrage abarbeitet. Die Kriterien sind dabei nicht nur vorhandene Indexe sondern auch die Anzahl der Datensätze. Du kannst also zum Beispiel nicht nur mit 3 Datensätzen testen und auf 3 Mio für Werte im Produktiveinsatz hochrechnen.

    dedlfix.

    1. Hallo,

      Wenn das DBMS (trotz aller Optimierungen) zu langsam ist, kannst du das mit Hardware beschleunigen. Mit Programmierern geht das nicht. Man verwendet auch gern ORMs oder andere Zwischenschichten, die per se langsamer sind als direkte Abfragen, hat dann aber teure Entwicklungsarbeit gespart, weil du dann ohne Gefummel an die zugehörigen Daten kommen kannst.

      Ich werde mir das mal ansehen und schauen, ob es am Ende zu meiner Situation passt (ORM).

      Wenn du keinen sehr guten Grund hast, in einem relationalen Datenbanksystem auf die Relationen zu verzichten, dann lass es lieber. Es ist darauf ausgelegt und optimiert.

      Du würdest also auch eher zu der Struktur tendieren, die mein Freund mir angeraten hat, wenn ich das richtig deute.

      Vielen Dank,
      Rolf

      1. Hakuna matata!

        Wenn du keinen sehr guten Grund hast, in einem relationalen Datenbanksystem auf die Relationen zu verzichten, dann lass es lieber. Es ist darauf ausgelegt und optimiert.

        Du würdest also auch eher zu der Struktur tendieren, die mein Freund mir angeraten hat, wenn ich das richtig deute.

        Ja, aber das wesentliche Argument ist an diese Stelle nicht die Perfomanz. Es geht viel mehr darum, die Daten auf eine verständliche und wartbare Art zu modellieren. Es gibt sogenannte Datenbank Normalformen, mit denen man die gröbsten Fehler im Datenbankdesign vermeiden kann. Dein Ansatz, mehrere Produkt-IDs in einem Feld zu modellieren, verstößt zum Beispiel gegen die erste Normalform.

        --
        “All right, then, I'll go to hell.” – Huck Finn

        1. Hallo

          Es gibt sogenannte Datenbank Normalformen, mit denen man die gröbsten Fehler im Datenbankdesign vermeiden kann. Dein Ansatz, mehrere Produkt-IDs in einem Feld zu modellieren, verstößt zum Beispiel gegen die erste Normalform.

          Ich habe das jetzt so realisiert, wie die Normalformen das vorgeben.

          Es geht viel mehr darum, die Daten auf eine verständliche und wartbare Art zu modellieren.

          Jetzt stehe ich nur vor dem Problem nicht zu wissen, welche ID die Anleitung hat, die ich eingestellt habe, um sie mit den einzelnen Produkt IDs zu verbinden. Vorher war das ja klar, da es in einer Tabelle stand.

          Auslesen kann ich die ID zwar mit $PDO->lastInsertId(), aber wie sicher ist das? Wird diese ID, "die durch das Query erzeugt wurde", daran gebunden und ist daher sicher oder muss ich mir Gedanken darüber machen, dass jemand zwischendurch einen Request ausführt und dann die ID einer anderen Anleitung erhalte?

          mfg,
          Rolf

          1. Auslesen kann ich die ID zwar mit $PDO->lastInsertId(), aber wie sicher ist das? Wird diese ID, "die durch das Query erzeugt wurde", daran gebunden und ist daher sicher oder muss ich mir Gedanken darüber machen, dass jemand zwischendurch einen Request ausführt und dann die ID einer anderen Anleitung erhalte?

            die last_insert_id ist in der regel session-gebunden. anderer request - andere session. hängt aber letztlich vom treiber ab, den man benutzt.

            1. Tach!

              Auslesen kann ich die ID zwar mit $PDO->lastInsertId(), aber wie sicher ist das? Wird diese ID, "die durch das Query erzeugt wurde", daran gebunden und ist daher sicher oder muss ich mir Gedanken darüber machen, dass jemand zwischendurch einen Request ausführt und dann die ID einer anderen Anleitung erhalte?

              die last_insert_id ist in der regel session-gebunden. anderer request - andere session. hängt aber letztlich vom treiber ab, den man benutzt.

              Dann ist der Treiber kaputt. PDO::lastInsertId() verhält sich unterschiedlich, je nach verwendetem DBMS. Bei MySQL liefert es die zuletzt vergebene ID, solange die Datenbankverbindung bestehen bleibt. Das heißt also, wenn da kein Connection-Sharing betrieben wird, die Verbindung nicht geschlossen wird und auch kein anderer INSERT auf Tabellen mit auto_increment-Spalte ausgeführt wird, dann ist der lastInsertId-Wert eindeutig. Das wird von MySQL garantiert.

              dedlfix.

              1. Vielleicht hätte ich schreiben sollen "abhängig von DBS und Treiber".
                Von MySQL war im Ausgangsposting nicht die Rede.

  2. Wie viele Abfragen von wie vielen Usern erfolgen denn gleichzeitig?

    SQL Performance

    1. SQL Performance

      Die Seite ist sehr gut, habe mich gestern schon eingelesen und darunter vergessen, zu antworten. ;)

      Wie viele Abfragen von wie vielen Usern erfolgen denn gleichzeitig?

      Noch keine. Ich mache derzeit immer nur utopische Hochrechnungen. Wie Menschen das Angebot annehmen, ist immer ungewiss bei dem ganzen Überangebot. Ich versuche am Ende durch Performance mit gutem Inhalt verknüpft zu punkten gegenüber Wordpress, Jimbo Blogs und anderen Webseiten. Egal wie es ausgeht, es werden wichtige Erfahrungen sein.

      mfg,
      Rolf

  3. Wie die anderen schon schrieben: "Normalisiere" Deine Datenbestände. Im Zweifelsfall ist Performance nicht alles, Tatsache ist nämlich, dass Dir (oder einem anderen) schon morgen etwas einfallen kann (und wird) was mit einer derzeit optimalen Datenstruktur eben nicht performant funktioniert und dann richtig Zeit und Geld kostet.

    Die von Dir genannten Zahlen für die Datensätze kommen auch nicht in einem Bereich wo man einen hoch studierten Informatiker von Oracle ordert  um diese zu optmieren. Da lacht die Datenbank nämlich drüber. Es sei denn, die verwendetst DBase IV auf einem 386SX mit 25MHz und 1MB Arbeitsspeicher und hast die eigentliche Datenbank auf Disketten...

    Jörg Reinholz