MSSQL: JOIN - SCAN und SORT vermeiden

[RED-BARON]

Hallo,

Tab1.
ID (UUID, PK), TS (DateTime, Index)
Tab2.
ID (UUID, PK-gruppiert), ...

Stmt. Select tab2.* FROM tab1 INNER JOIN tab2 ON tab1.ID = tab2.ID WHERE tab1.TS BETWEEN ... AND ...

Besonderheit die nicht unwichtig ist es handelt sich _nicht_ um sequenzielle ID's
Wird der TS-Index verwendet sind infolge die ID's aus Tab1 im Zwischenergebnis unsortiert.
Darauf folgt ein Sortiervorgang bevor das Merge-Join ausgeführt wird.

Mit einem erzwungenen "Nested Loops" (SELECT ... INNER LOOP JOIN ... ) kann die
CPU-Zeit auch nicht wirklich reduziert werden. Höchstens wenn sehr wenig Daten
abgefragt werden könnte dies der Fall sein.

Alle anderen Versuche (z.B. TS-INDEX ) entfernen führen zu Index-Scans bei Tab1 und dafür zu Index-Seek bei Tab2.

Alternativ könnten erst die Zeilen aus Tab1 abgefragt und anschließend als Batch die aus Tab2 geholt werden.

Ich wollte aber doch vorher fragen ob jemand in Bezug auf MSSQL
oder auch generell ne Idee hat die Abfrage oder auch die Tabellen besser zu gestalten. Die nicht sequenzielle
UUID läßt sich aber nicht vermeiden. Auch mit einer AUTOINC ID wäre nicht sicher, dass TS UND ID gleichermaßen
in Tab1 sortiert vorliegen würden.

Edit:
Die gleichen Tabellen und Abfrage mit Explain bei MySQL untersucht ergeben
weder "extra=filesort" noch "type=ALL" für eine der Tabellen

[hustbaer]

Ich verstehe nicht was du meinst dass man hier jetzt vermeiden könnte.
BTW würden sequentielle IDs vermutlich schon helfen, nämlich wenn die Daten wirklich von Disk geladen werden müssen.
Also vielleicht einfach NEWSEQUENTIALID() statt NEWID() verwenden.

[RED-BARON]

Servus Hustbaer,

die Daten stammen aus unterschiedlichen Quellen und wurden zusammengeführt.
Daher ist NEWSEQUENTIALID() praktisch nicht anwendbar. Zudem kann ein TS
geändert werden ohne dass die ID sich ändert, so dass die ursprüngliche
Ordnung wieder zu Nichte gemacht werden würde.

Mein Eindruck im Vergleich zu MySQL ist, dass dieser scheinbar das unsortierte
Zwischenergebnis durchläuft und beim Zusammenführen mit der 2. Tabelle auf dieser
ein Radom-Access, binary search, ... anwendet.

MSSQL bevorzugt, wie ich herausgefunden haben will, Merge-Join. Dabei müssen
beide Seiten der Zusammenführung in gleicher Reihenfolge ( bzgl. des verwendeten
Indexes ) vorliegen.

Durch "inner LOOP join" ( was wohl die ungünstigste Methode von allen ist )
kann auf die Sortierung zwar verzichtet werden, der Performancevorteil bei
"kleinen" Datenmengen steht in keinem Verhältnis zu den Einbußen bei nur etwas
größeren Datenmengen.

Mir ist es eben noch nicht gelungen einen Hash-Join zu "erzwingen". Evtl. kommt
diese Methode der von MySQL verwendeten am nächsten.

Wie nachträglich angemerkt. Würde MySQL irgendwo ein Zwischenergebnis sortieren,
müsste es durch Untersuchung der Abfrage mit EXPLAIN als "filesort" erkenntlich sein.
"filesort" wird ja wohl auch dann ausgewiesen, wenn die Sortierung im Speicher und
nicht nur explizit auf der Platte mittels temp-Tabellen erfolgt.

MySQL anstatt MSSQL zu verwenden, ist nicht möglich, weil derzeit beide Systeme
bereits im Einsatz sind. Die Daten werden zwischen beiden Systemen synchronisiert.
Daher kann ich leicht das Verhalten beider untersuchen. Nur hätte ich gern auf Seite
von MSSQL Verbesserungen mir erhofft.

Im Vorfeld der Anfrage hier habe ich mich natürlich auch schon umfangreich in
Blogs und der Doku umgesehen. Gerade was die Indizierungsmöglichkeiten angeht
bestehen zwischen beiden Systemen erhebliche Unterschiede.

MySQL ist wohl auch explizit für schnelle Leseoperationen ausgelegt. Evtl.
stößt mein Problem auch hier auf erhebliche Unterschiede zwischen beiden.

[hustbaer]

RED-BARON schrieb:

Zudem kann ein TS
geändert werden ohne dass die ID sich ändert, so dass die ursprüngliche
Ordnung wieder zu Nichte gemacht werden würde.

Das wäre ja kein Problem. Die Reihenfolgen muss ja nicht exakt gleich sein -- es würde nur der Performance dienlich sein wenn die Reihenfolgen halbwegs gut zusammenstimmen. Die Korrektheit des Ergebnis hängt davon aber nicht ab.

RED-BARON schrieb:

Mein Eindruck im Vergleich zu MySQL ist, dass dieser scheinbar das unsortierte
Zwischenergebnis durchläuft und beim Zusammenführen mit der 2. Tabelle auf dieser
ein Radom-Access, binary search, ... anwendet.

Und das ist schnell? Kann ich mir grad nicht vorstellen. Entspricht auch wenn ich es richtig verstehen einem nested Loops Join.

RED-BARON schrieb:

MSSQL bevorzugt, wie ich herausgefunden haben will, Merge-Join. Dabei müssen
beide Seiten der Zusammenführung in gleicher Reihenfolge ( bzgl. des verwendeten
Indexes ) vorliegen.

MSSQL verwendet das was der Query Optimizer für die beste Lösung hält. Dass diese Schätzung nicht immer der Realität entspricht ist auch klar. Bestimmte Heuristiken sind VIEL zu einfach bzw. müssen mit viel zu wenig Daten auskommen.

RED-BARON schrieb:

Mir ist es eben noch nicht gelungen einen Hash-Join zu "erzwingen". Evtl. kommt
diese Methode der von MySQL verwendeten am nächsten.

Schonmal
https://msdn.microsoft.com/en-us/library/ms173815.aspx
probiert?

RED-BARON schrieb:

MySQL MySQL MySQL

Ist MySQL mit der selben Abfrage (auf den selben Daten auf der selben Hardware usw.) schneller?
Wenn ja wie viel?

[hustbaer]

Achja, nochwas: wie gross sind die Datensets die du abfragst a) im Vergleich zum RAM des Servers und b) im Vergleich zur Grösse der gesamten Tabelle?

[RED-BARON]

hustbaer schrieb:

Schonmal
https://msdn.microsoft.com/en-us/library/ms173815.aspx
probiert?

Ist MySQL mit der selben Abfrage (auf den selben Daten auf der selben Hardware usw.) schneller?
Wenn ja wie viel?

das "LOOP" hatte ich aus einem Blog,... die anderen Varianten sind mir neu.
Danke für den Link!

Die Daten liegen/passen auf beiden Systemen im RAM, das Verhältnis der Abgefragten Daten ist unterschiedlich. Auf dem MSSQL sind es größer.
Auf dem MySQL ist praktisch eine Teilmenge vorhanden aus der auch wieder
ein kleinerer Teil ( im Verhältnis ) abgefragt wird.
Die Geschwindigkeit kann man so glaub ich nicht fair vergleichen.

Mir ist nur im Ablaufplan von MSSQL ein "Sort" sowie "Index Scans" aufgefallen.
Das "Sort" verursacht 17% der Gesamtkosten, lt. Ablaufplan. Diese Kosten würde ich gern eliminieren. Weil es auf dem anderen System anscheinend diesen
Kostenpunkt nicht gibt hab ich mir so meine Gedanken gemacht, was falsch ist
oder was ich korrigieren könnte.

Mir geht es nicht darum welches System "besser" ist. Ich nutze beide und das bleibt so.

Wenn am Montag wieder Zugriff besteht werde ich die Infos aus dem Link ausprobieren.

[hustbaer]

Probier' einfach mal alle JOIN Operatoren durch.
Dann siehst du ja welcher JOIN mit deinen Daten am schnellsten funktioniert.

Wenn es wirklich der vom Query Optimizer gewählte MERGE Join ist, dann sei froh über den Sort mit 17% Kosten, denn der macht den MERGE Join erst möglich.
Und wenn nicht, dann lässt du den "HASH" Hint halt drinnen.

Dass MySQL hier anders vorgeht ist ... OK, ist halt so. Aber wenn die Datenmengen, Systeme, Abfragen etc. bei MySQL vs. MSSQL nicht wirklich vergleichbar sind, dann ... sollte man sie vielleicht auch nicht vergleichen?

ps: Wenn du stark unterschiedliche Abfrage-Grössen hast, dann kann auch RECOMPILE hilfreich sein. Das zwingt MSSQL jedes mal nen neuen Query-Plan zu erstellen, mit den konkreten Parametern der aktuellen Abfrage.
Wenn du dann keinen JOIN Typ per Hint erzwingst, dann wählt MSSQL anhand der geschätzen Grösse des Ergebnisses den passenden JOIN Typ.

[RED-BARON]

Guten Morgen

Danke für Deine Geduld !

Ich habe nun für Tab1. zusammengesetzten PK (TS,ID), Für Tab2. weiterhin PK(ID)

"JOIN-Optionen" habe ich probiert aber im Ergebnis keine mehr benutzt, dafür
Dein Hinweis OPTION(RECOMPILE) ausprobiert ...

Ergebnis:
Abfrage von Daten über Zeitraum 1 Monat _ohne_ RECOMPILE
Tab1. (SEEK), Tab2. (SCAN), JOIN(Hash-Match)

Abfrage von Daten über Zeitraum 1 Monat ( max. 1 Monat ist der Anwendungsfall ) _mit_ RECOMPILE
Tab1. (SEEK), Tab2. (SEEK), JOIN(Nested-Loops)

Abfrage von Daten über Zeitraum 3 Monate _mit_ oder _ohne_ RECOMPILE
Tab1. (SEEK), Tab2. (SCAN), JOIN(Hash-Match)

Was aber auch mit der Kardinalität zusammenhängt. Bei 3 Monaten kommen
einfach zu viele Daten im Verhältnis ( 4-5% Grenze gilt für mich als Faustregel )
zu den Records in der Tab2. In dem Fall würde auch MySQL die Tabelle scannen ...

Jetzt ist für mich die Welt erstmal wieder in Ordnung, "Sort" wurde nicht mehr gesichtet.

Vorher:
SQL Server-Ausführungszeiten:
, CPU-Zeit = 3386 ms, verstrichene Zeit = 4624 ms.

Nachher:
SQL Server-Ausführungszeiten:
, CPU-Zeit = 437 ms, verstrichene Zeit = 1923 ms.

[hustbaer]

Bitte!

BTW: Ich muss mich korrigieren.

RECOMPILE kann nicht nur bei stark unterschiedlichen "Abfrage-Grössen" helfen. Ohne RECOMPILE schätzt der Query-Optimizer unabhängig von den in der Abfrage verwendeten Konstanten und Parametern. Er erstellt also einen Query-Plan der mit geschätzten Standardwerten optimiert wurde.

D.h. RECOMPILE kann auch helfen wenn man über ein Predicate bzw. einen JOIN sehr wenig bzw. sehr viele Datensätze auswählt -- auch wenn man immer so wenig bzw. so viele Datensätze auswählt.

RECOMPILE ist natürlich nicht für alle Abfragen gut, da es den Server wie der Name schon vermuten lässt dazu zwingt die Query neu zu kompilieren, also einen neuen Query-Plan zu erstellen. Bei Queries die eh immer den selben Query-Plan ergeben, sehr billig sind (z.B. einfach eine Zeile rausholen + evtl. noch 2-3 Zeilen aus anderen Tabellen dazujoinen) und sehr oft ausgeführt werden kann RECOMPILE natürlich schaden.
Weswegen Query Plan Caching und Dinge wie Forced Parameterization ja überhaupt erfunden wurden.

[RED-BARON]

Servus

SUPER !

Du hast mir die Erklärung zu meiner Beobachtung gegeben.
Ein Query zunächst mit RECOMPILE ausgeführt ( Plan sollte gespeichert worden sein )
und unmittelbar danach ohne dieser Option führt zu einem anderen Plan ...

Er erstellt also einen Query-Plan der mit geschätzten Standardwerten optimiert wurde.

... und auch nur diese Pläne scheinen verwendet zu werden, wenn MSSQL selbst entscheiden darf

Momentan bin ich nun ein Schritt weiter, suche gezielt kostspielige Abfragen mit

--DBCC FREEPROCCACHE
--DBCC DROPCLEANBUFFERS

SELECT  creation_time 
        ,last_execution_time
        ,total_physical_reads
        ,total_logical_reads 
        ,total_logical_writes
        , execution_count
        , total_worker_time
        , total_elapsed_time
        , total_elapsed_time / execution_count avg_elapsed_time
        ,SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
         ((CASE statement_end_offset
          WHEN -1 THEN DATALENGTH(st.text)
          ELSE qs.statement_end_offset END
            - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
ORDER BY total_elapsed_time / execution_count DESC;

sp_updatestats habe ich mir auch schon angesehen, nach dem mir der Gedanke kam,
dass irgendwo die Schätzungen her kommen müssen. Ich denke mal nach Anpassungen
von Indizes diese zu aktualisieren ist sinnvoll. Denn im "normalen" Leben wachsen die
Statistiken wohl durch das Füllen der Tabellen.

[hustbaer]

Nochmal ps: Konstanten werden IIRC nur ignoriert wenn man "forced parameterization" aufgedreht hat (Default = Off soweit ich weiss) und es sich um Ad-Hoc Queries handelt. Und ohne "forced parameterization" "trifft" es soweit ich weiss nur Parameter.

In Stored Procedures werden Konstanten immer berücksichtigt.

Mit RECOMPILE werden IIRC sowohl Konstanten als auch Parameter berücksichtigt.

Das ganze ist aber halbwegs gut dokumentiert, z.B. einfach mal nach "forced parameterization" suchen - da findet man einiges zu dem Thema.

Und für halbwegs aktuelle Statistiken zu sorgen ist vermutlich immer eine gute Idee.