Tabellen für viele kleine Datensätze optimieren

[Dejan Vu]

Zitat von jobo:

Ich kann Dir das ehrlich gesagt nicht erklären und ich hab da auch keine Ambitionen, mich in die Optimierungsalgorithmen beliebiger Optimizer reinzudenken.

Hmmm. Ich dachte, Du wüsstest das.

Zitat:

Schon bei vertauschter Reihenfolge der Felder in der Where Bedingung gegenüber der Indexdefinition wird der Index gar nicht genutzt oder nur teilweise bis zum vertauschten Feld.

Dann ist das kein Optimizer am Werk, denn der würde das ja optimieren, aber das ist mir bei einigen RDBMS auch schon aufgefallen. (Für Oracle gab es / gibt es wohl entsprechende Query-Optimizer, die diese Reihenfolge dann optimieren). Na ja 'Optimizer' sind das schon, aber eigentlich sind es Strategieauswähler, weil bei jedem Schritt der Query (eigentlich nur ein Baum aus Filtern, und Enumeratoren) die vermeindlich kostengünstigste Variante aus eine Pool an Möglichkeiten (Index-Seek, Index-Scan, Table-Scan, Sort, Bucket, Hash etc.) herausgepickt wird.

Übrigens wird ein Index-Scan benutzt, wenn einzelne Felder benötigt werden, die in Index-Informationen enthalten sind und wenn dafür dann kein Datensatz gelesen werden muss. Oder der Scan über den Index, um etwas zu finden, ist schneller als ein Table-Scan. Insofern sind einzelne Index eventuell geeignet, Scan-Vorgange zu beschleunigen, aber 'eigentlich'(*) auch nur dann, wenn sie kombiniert sind, oder weitere Felder huckepack mit ablegen ('INCLUDE' Klausel bei T-SQL in Verbindung mit 'CREATE INDEX')
(*)'Eigentlich'=Ich weiss es nicht genau.

Aber was war das alles doch gleich? Ach ja, sekundär und wie Du schon sagtest per Experiment in ein paar Minuten belegt/widerlegt/erkannt.

Vom Diskussionsstoff her jedoch ein schönes Thema für einen anderen Thread.

[BUG]

Die Annahme, dass die Optimierer nach festen Regeln optimieren, ist meines Wissens falsch. Vielmehr ist es tatsächlich eine Minimierung des geschätzten Aufwandes über die Ausführungspläne der Anfrage.

Dabei hängt schon das Schätzen des Aufwandes von den Daten ab (Menge, Histogrammen, usw.).
Außerdem ist die Menge der Pläne auch zu groß, um komplett durchsucht zu werden. Natürlich können Heuristiken verwendet werden, um Pläne zu verbessern. Es ist aber auch nicht unwahrscheinlich, das bei der Auswahl des betrachteten Suchraums eine (pseudo-)zufällige Komponente mit reinspielt.

Insofern halte ich es für sinnlos, sich als Anwender zu genau mit dem Optimierer zu beschäftigen. Was aber nicht heißen soll, das man ihn nicht mithilfe von Erfahrung einen Tritt in die richtige Richtung geben kann.

[jobo]

Zitat von BUG:

Die Annahme, dass die Optimierer nach festen Regeln optimieren, ist meines Wissens falsch.

Nein, es gibt auch Optimierer, die nach festen Regeln arbeiten.
Aber was Du schlussfolgerst, läuft auf das hinaus, was ich auch rüberbringen wollte. Es macht wenig Sinn, einen nicht regelbasierten Optimizer "verstehen" zu wollen. Unabhängig von der Qualität der Statistiken, die von manchen Systemen herangezogen werden, sagt man gewissen Systemen nach, dass bspw. sogar physikalische Gegebenheiten berücksichtigt werden. (Welches Device hält welche Datenmenge, ..)
Wie auch immer, im Zweifel kann ich ihn mit Hints lenken. Im Beispiel hier bei den Indizes halt ausprobieren.

Letztlich ist es dennoch so, dass ich im Produktivbetrieb mit schleichenden Änderungen oder plötzlich kippendem Laufzeitverhalten rechnen sollte. Da muss also entweder ein kompetenter DBA ran und nachjustieren oder eben ein Supportvertrag her.

[BUG]

Zitat von jobo:

Nein, es gibt auch Optimierer, die nach festen Regeln arbeiten.

Okay, vielleicht habe ich an der Stelle etwas übertrieben

Zitat von jobo:

Da muss also entweder ein kompetenter DBA ran ...

Wobei es insgesamt keine schlechte Idee wäre, sich im Vorfeld mit einem Spezialisten für das entsprechende Datenbank-System abzusprechen, der das dann auch betreut. Die sind ja nicht ohne Grund so gut bezahlt.

[jobo]

Natürlich ist das keine schlechte Idee! Ich muss meine Aussage "Optimizer kann man eh nicht nachvollziehen" etwas gerade rücken.
Im Zuge von Hibernate, JPA und Persistenzframeworks ist es trendy, die DB als Blackbox zu betrachten. Das Verständnis der Abläufe innerhalb der Blackbox ist damit per Definition ausgeblendet. Sehr bequem, Schuld ist im Zweifel der DB Admin.
Das mag in vielen Fällen (z.B. kleine Projekte, wenig User / Daten) gut gehen, kann aber auch voll in die Hose gehen.

Daher sollte man sich schon um ein gewisses Verständnis von (grundlegenden) DB Verfahren bemühen.
Ein Tuning der DB funktioniert im Produktivbetrieb übrigens am besten bei der Verwendung von Views, hier kann man nachträglich Verbesserungen anbringen bzw. sogar testen, ohne der Anwendung ein Haar zu krümmen, z.B. über Optimizer Hints..

[Dejan Vu]

Zitat von BUG:

Die Annahme, dass die Optimierer nach festen Regeln optimieren, ist meines Wissens falsch.

Da jedes Programm nach 'festen Regeln' (nämlich dem Code) arbeitet, ist das 100% korrekt (die festen Regeln). Nur frage ich mich, wer das behauptet. Ich habe nur grob skizziert, wie ein Optimierer bzw. der Strategieauswähler arbeitet. Da muss ich auch nicht lange überlegen, in welcher Reihenfolge ich die Dinge abarbeiten und großartige Kombinationen, die alle durchzutesten sind, sehe ich auch nicht. Deshalb liegen die ja auch manchmal daneben, eben weil sie ziemlich starr sind. Es ist wirklich kein Hexenwerk, was da abgeht: Das Statement (meistens ja ein SELECT) wird in einen Baum überführt (das macht der Parser), dann wird 'gekürzt', d.h. gleiche Fragmente zusammengefasst und dann rechnet man für die einzelnen Knoten die beste Strategie nach Brute Force aus. Hierbei wird eine Performancefunktion angewandt, die mit den Statistiken gefüttert wird. Da mittlerweile der I/O-Durchsatz eine entscheidende Rolle spielen kann (SSD, HD, RAM) wird sich das, wenn man die Statistiken erweitert, auch entsprechend auswirken. Wichtig ist: Man muss diese Statistiken pflegen, also im Rahmen eines Wartungsplans immer mal wieder neu erstellen lassen.

Aber da ich nicht alle Optimierer kenne, sondern eigentlich nur 2 (SQL-Server und einen für ein RDBMS selbst entwickelten) kenne ich die Magie hinter anderen Optimizern natürlich nicht. Nur eines kann ich mir nicht vorstellen: Das man irgendwelche Kombinationen durchprobiert, denn da gibt es eigentlich keine. Aber wenn ihr mit 'nicht regelbasiert' Fuzzy-Logik meint, dann ist das natürlich korrekt, denn es ist eine analoge Funktion, die 'ausrechnet', wie teuer ein bestimmtes Verfahren ist und anhand dessen wird bestimmt, was jetzt das beste ist. Also nicht: 'Index-Seek < Index-Scan < Table-Scan'.

Zitat von jobo:

Daher sollte man sich schon um ein gewisses Verständnis von (grundlegenden) DB Verfahren bemühen.

Genau. Und wie Du schon sagtest: Probieren, Probieren, Probieren. Denn nur aus der Stoppuhr (bzw. die IO-Statistik) spricht die Wahrheit. Amen.

[DSP]

@to: Mal ne dumme Frage, wie ist denn jetzt das Tabellendesign und der Stresstest ausgegangen?