4. [MSSQL] Auswertungsplan ermitteln

[MSSQL] Auswertungsplan ermitteln

  • ?

    Wir haben erstmal entschieden, die Programm-Optimierungen so auszuliefern - wenn ich viel Glück habe reicht das für den Anwendungsfall aus.

    Aber mal eine generelle Frage: Macht es eigentlich für den fertigen Plan einen Unterschied, in welcher Reihenfolge ist die WHERE-Bedingungen schreibe?

    PS: Eine der Anweisungen, die mir schon fast zu komplex ist, sieht ungefähr so aus:

    SELECT * FROM kunden
WHERE  rv!=ev
 AND (   ar IN (SELECT arkunde FROM tpl WHERE nummer like 'A%')
      OR ar IN (SELECT arrech FROM tpl WHERE nummer like 'A%')
      OR ar IN (SELECT arbef FROM tpl WHERE nummer like 'A%')
      OR ar IN (SELECT arkunde FROM dep)
     )

    (kunden.ar ist der primärschlüssel, arkunde etc sind Fremdschlüssel, rv und ev sind varchar(35)-Felder, die an anderen Stellen regelmäßig geändert werden)

    0

  • Die Reihenfolge der Teilbedingungen bei WHERE ist meiner Erfahrung nach egal, so lange die verschiedenen Ausdrücke logisch äquivalent sind. Dazu ist der Query-Optimizer schon schlau genug.

    Was einen Unterschied machen könnte, ist ob du es so schreibt wie gezeigt hast, oder mit "ar IN (SELECT ... UNION SELECT ... UNION SELECT ... UNION SELECT ...)". Bin mir aber nicht sicher, kann auch sein dass selbst das egal ist, weil im Endeffekt der selbe Execution-Plan rauskommt.

    ----

    Was die Beispiel-Abfrage angeht...

    Hier gibt es zwei Bedingungen die mit AND verknüpft sind, und demnach auch zwei Punkte wo man ansetzen kann.

    Angenommen "rv!=ev" ist die "schwächere" Bedingung (=schliesst weniger Zeilen aus), dann würde ich den Teil erstmal vergessen.

    Bleibt noch der "ar IN (...)" Teil.

    Bedingungen wie "col LIKE 'text%'" (also rein rechts trunkierte Patterns) kann SQL Server über einen ganz normalen index auf "col" auflösen, und zwar sehr schnell. Die Bedingung wird normalerweise zu einem Index-(Range-)Scan, der nur die Range im Index liest wo "col" mit "text" anfängt, und die liegt ja "am Stück", daher geht das halbwegs schnell. Anders gesagt: er macht aus "col LIKE 'text%'" ein "col BETWEEN foo AND bar".

    Ein einfacher Index der bloss "nummer" enthält bringt dich allerdings bloss so weit, dass er die Zeilen die er aus "tpl" braucht ermitteln kann. Er muss sie aber immer noch lesen. Dummerweise liegen die dann oft quer über die Tabelle verstreut, d.h. viele IOs mit vielen Gaps dazwischen => langsam.

    Also machst du einen Index auf "tpl.nummer", und nimmst als "included columns" zusätzlich noch "arkunde", "arrech" und "arbef" mit auf.
    Dadurch hast du einen Index der die ersten drei Sub-Selects "abdeckt", einen sog. "covering index" ("abdecken" in dem Sinn, dass SQL Server für die Abfrage überhaupt nicht mehr auf die eigentliche Tabelle zugreifen muss, sondern alles nur über den Index macht, inklusive lesen der Ergebnisspalten).

    Gewinn: SQL Server kann nun durch einen relativ billigen Index-(Range-)Scan das komplette Resultat der ersten drei "IN (...)" ermitteln (d.h. mit wenigen IOs die nahe beinander liegen => schnell).

    Kosten: Dadurch dass es einen neuen Index zu pflegen gibt, wird INSERT/UPDATE/DELETE in "tpl" etwas langsamer. Und natürlich braucht der neue Index zusätzlichen Speicher, die 4 Spalten die er "enthält" sind dann ja effektiv doppelt abgespeichert.

    Das selbe lässt sich dann mit "dep" machen: Index auf "dep.nummer" der als "included column" noch "arkunde" enthält.

    Gewinn/Kosten = selbe wie oben

    -------------

    Sollten die Tabellen "tpl" und "dep" bereits nach "nummer" sortiert sein (=Clustered Index auf "nummer"), zahlt es sich meistens nicht aus noch einen zusätzlichen Index auf "nummer" anzulegen. Ausnahme wäre, wenn die Rows in "tpl" und/oder "dep" sehr gross sind (sehr viele/grosse Felder), und "nummer + arkunde + arrech + arbef" davon nur einen kleinen Teil ausmachen. In dem Fall könnte ein zusätzlicher Index trotzdem was bringen, speziell wenn die Tabellen wirklich sehr gross sind (hunderte MB+).

    -------------

    Über diese Indexe kann SQL Server nun ermitteln welche Zeilen aus "kunden" in Frage kommen. Er muss sie aber noch laden (und weiter nach "rv!=ev" filtern). Hier schlägt wieder der selbe Effekt zu wie vorhin: wenn die benötigten Zeilen sehr verstreut im Table liegen, dann kann das Laden der Zeilen ordentlich lange dauern.

    Dummerweise können wir das jetzt nicht mehr so einfach optimieren.
    Die benötigten Zeilen liegen, nach "ar" sortiert, ja nicht nebeneinander, sondern kreuz und quer verstreut. (Falls die im Normalfall doch "benachbart" sind, sind wir übrigens fertig, alles vom Feinsten).

    Das einzige was mir dazu noch einfällt wären Indexed Views. Und zwar 4 Stück, je eine für jeden "ar IN (...)" Teil. Nähere Ausführungen dazu spar' ich mir mal, das würde zu lang.

    -------------

    Und angenommen "rv!=ev" ist die "stärkere" Bedingung, dann würde sich ein Filtered-Index anbieten. Also ein Index auf "kunden.ar" mit der Bedingung "WHERE rv!=ev".
    Damit hab' ich allerdings noch keine echte Erfahrung, da wir momentan noch SQL Server 2005 einsetzen, und der kann (leider) noch keine Filtered Indexe.

    0
  • C

    Auf lange Sicht würde ich mal davon ausgehen, daß die rv!=ev Bedingung mehr Datensätze ausschließen würde, aber das müsste ich echt noch mal in der Datenbank sicherstellen. Allerdings werden die Spalten recht oft geändert (in anderen Modulen wird bei jeder Operation rv neu gesetzt, in meiner Anwendung wird für jeden gefundenen Datensatz ein "UPDATE SET ev=rv" gesetzt als Kennung, daß der Datensatz gelesen worden ist).
    Übrigens habe ich bei dieser Anweisung auch mal die Variante mit UNION zum Vergleich getestet - von der Geschwindigkeit war es gleich und die Ausführungspläne sahen sich auch fast gleich aus.

    PS: TPL hat über 100 Spalten und einige Tausend Zeilen, DEP ist ein wenig kleiner (und hat auch deutlich weniger Einträge).

    (ich freu mich schon auf meinen Urlaub - drei Wochen, ohne das P-Wort hören zu müssen)

    0

  • Naja, "einige Tausend Zeilen" ist relativ schwammig 🙂
    Und "über 100 Spalten" sagt auch nix darüber aus, wie gross eine durchschnittliche Row in Bytes dann ist.

    Wenn ich mit 500 Byte pro Row und 10K Rows rechne, dann wären das 5 MB. Das ist so klein, dass es vermutlich immer im Cache bleibt - so lange es hin und wieder mal verwendet wird. In dem Fall zahlen sich besonders schlaue "Optimierungen" kaum aus, bzw. könnten sogar alles langsamer machen.

    in meiner Anwendung wird für jeden gefundenen Datensatz ein "UPDATE SET ev=rv" gesetzt als Kennung, daß der Datensatz gelesen worden ist

    Man könnte auch eine "Computed Column" machen, die z.B. "processed" heisst, und deren Wert "CAST((CASE WHEN ev=rv THEN 1 ELSE 0 END) AS TINYINT)" ist.

    Und auf diese "Computed Column" dann nen Index machen.
    Und dann in der Query mit "WHERE processed = 0" prüfen.

    ps: ein "Filtered Index" sollte aber genau so gut funktionieren, vermutlich sogar besser, und weniger Speicherplatz brauchen.

    0
  • ?

    hustbaer schrieb:

    Naja, "einige Tausend Zeilen" ist relativ schwammig 🙂
    Und "über 100 Spalten" sagt auch nix darüber aus, wie gross eine durchschnittliche Row in Bytes dann ist.

    Sorry, die genauen Zahlen hatte ich gestern leider nicht im Kopf.
    In der Tabelle TPL sind 58000 Zeilen (davon fallen ca. 15000 unter die Selektionsbedingung nummer like 'A%' ). Außerdem hat sie 174 Spalten (11 DATETIME, 135 NUMERIC und 28 VARCHAR (insgesamt 1000 Zeichen reserviert, im größten Datensatz ca 200 genutzt)).

    ps: ein "Filtered Index" sollte aber genau so gut funktionieren, vermutlich sogar besser, und weniger Speicherplatz brauchen.

    Dann müßte ich mal nachsehen, ob unsere Datenbank "Filtered Index" unterstützt, aber ich fürchte nicht (unsere Datenbank im Haus ist SQL Server 2005, beim Kunden vermutlich auch).

    PS: Ich hab' nochmal nachgesehen und anscheinend wird doch durch die Sub-Selects der größere Teil der Daten eliminiert.

    0
  • ?

    Noch ein Nachtrag:
    Die Kunden-Tabelle, die ich dort oben angeführt hatte, gehört zu den kleineren Tabellen auf der Datenbank (2500 Einträge, davon fallen 170 unter die "ar in..." Bedingungen und im Regelfall 0 bis 2 unter die komplette Auswahl). In anderen Tabellen habe ich 15000 Datensätze (mit Zuwachsraten von 200 bis 500 pro Tag), davon erfüllen idR maximal 10 die Bedingung "rv!=ev".
    Ein gefilterter Index kommt leider nicht in Frage (von der Datenbank nicht unterstützt), berechnete Spalten will ich nur ungern verwenden, weil ich damit die Struktur der Datenbank beeinflusse (das hat dann Auswirkungen auf die weitere Verarbeitungslogik des Programms). Aber beim Stöbern in deinen Links bin ich auf eine andere Idee gekommen:

    Wenn ich einen View samt Index anlege für

    SELECT ar FROM tabelle
WHERE rv!=ev

    kann dieser Index dann auch für Zugriffe auf meine Haupt-Tabelle per

    SELECT * FROM tabelle
WHERE rv!=ev AND ar>0

    genutzt werden?

    0

  • Also erstmal bin ich grad nicht sicher ob eine Indexed View WHERE a!=b drinnen haben darf (gibt einige Einschränkungen). Falls das OK geht, dann ... jain 🙂

    SQL Server Enterprise Edition verwendet Indexed Views z.T. automatisch für Queries, auch wenn der View-Name in der Query nicht genannt wird.

    Bei SQL Server Standard oder Express musst du ihn explizit dazu zwingen, indem du sagst:

    SELECT ...
FROM indexedView WITH (NOEXPAND) /* SELECT muss auf die view gehen, und WITH (NOEXPAND) muss man auch dranschreiben */
WHERE blah
...

    Also entweder du nimmst alle Spalten in die Indexed View auf die du brauchst, oder du JOINst von der Indexed View wieder zurück auf die Haupttabelle ( FROM indexedView WITH (NOEXPAND) INNER JOIN bastTable ON ... ).

    0
  • ?

    hustbaer schrieb:

    Also erstmal bin ich grad nicht sicher ob eine Indexed View WHERE a!=b drinnen haben darf (gibt einige Einschränkungen). Falls das OK geht, dann ... jain 🙂

    In der MSDN habe ich zumindest keine Einschränkung gesehen, die die Konstruktion verbieten. Aber ich fürchte, der Punkt "Datenbanken mit vielen Aktualisierungen." trifft auf die Anwendung zu (jede Änderung an der Tabelle aus dem System heraus wirkt auch auf die Spalte rv).

    0

  • Ja, klar, das muss man immer abwägen.
    Das trifft auch nicht nur auf Indexed Views zu, sondern allgemein auf Indexe.

    Blöderweise lässt es sich kaum vorhersagen, was im Endeffekt besser (schneller) läuft.

    Und es kommt auch immer drauf an was man erreichen will. Will man den Throughput des Systems maximieren, oder will man das System dahingehend optimieren, dass es keine einzelne Abfrage gibt, die unverhältnismässig lange dauert.

    Im ersten Fall muss man in Kauf nehmen, dass vielleicht ein paar Abfragen (die nicht so oft ausgeführt werden) sehr lange dauern.
    Im zweiten Fall muss man in Kauf nehmen dass der Throughput des Systems etwas sinkt, dafür muss man aber auf nichts besonders lange warten, auch nicht auf Dinge die man (bzw. das System) nicht so oft macht.

    0
  • ?

    hustbaer schrieb:

    Und es kommt auch immer drauf an was man erreichen will. Will man den Throughput des Systems maximieren, oder will man das System dahingehend optimieren, dass es keine einzelne Abfrage gibt, die unverhältnismässig lange dauert.

    Erstes Hauptziel ist es, die Last auf der Datenbank zu reduzieren und Kollisionen mit anderen Anwendungen zu minimieren*, zweitens soll "mein" Anweisungblock insgesamt möglichst schnell durchlaufen werden (und wenn es möglich ist, in O(1)).

    * Ich bin nicht der einzige, der auf die Datenbank zugreift, allerdings einer von denen, die auch ohne Nutzer-Eingriff ständig Last erzeugen.

    0
Anmelden zum Antworten