Warum wäre die Verwendung einer temporären Tabelle schneller als eine verschachtelte Abfrage?

Question

Wir versuchen, einige unserer Abfragen zu optimieren.

Eine Abfrage macht folgendes:

SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, () Datum,
INTO [#Gadget]
FROM task t

SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID) as Client 
FROM [#Gadget]
order by CASE WHEN Datum IS NULL THEN 1 ELSE 0 END , Datum ASC

DROP TABLE [#Gadget]

(Ich habe die komplexe Unterabfrage entfernt. Ich denke nicht, dass sie relevant ist, außer um zu erklären, warum diese Abfrage als zweistufiger Prozess durchgeführt wurde.)

Ich dachte, es wäre effizienter, dies in eine einzelne Abfrage mit Unterabfragen zu kombinieren:

SELECT TOP 500 TaskID, Task, Tracker, ClientID, dbo.GetClientDisplayName(ClientID)
FROM
(
    SELECT t.TaskID, t.Name as Task, '' as Tracker, t.ClientID, () Datum,
    FROM task t
) as sub    
order by CASE WHEN Datum IS NULL THEN 1 ELSE 0 END , Datum ASC

Dies würde dem Optimierer bessere Informationen zur Verfügung stellen, um herauszufinden, was passiert und temporäre Tabellen zu vermeiden. Ich ging davon aus, dass es schneller sein sollte.

Aber es stellt sich heraus, dass es viel langsamer ist. 8 Sekunden gegen unter 5 Sekunden.

Ich kann nicht herausfinden, warum das so ist, da mein gesamtes Wissen über Datenbanken darauf hindeutet, dass Unterabfragen immer schneller sein sollten als die Verwendung von temporären Tabellen.

Was übersehe ich?

Bearbeiten --

Soweit ich aus den Abfrageplänen sehen konnte, sind beide weitgehend identisch, bis auf die temporäre Tabelle, die einen zusätzlichen "Table Insert" Vorgang mit einem Anteil von 18% hat.

Offensichtlich ist aufgrund der beiden Abfragen der Kosten für das Sortieren obersten N viel höher in der zweiten Abfrage als die Kosten des Sortierens im Unterabfrage-Verfahren, daher ist es schwierig, einen direkten Vergleich der Kosten anzustellen.

Alles, was ich aus den Plänen sehen kann, würde darauf hindeuten, dass das Unterabfrage-Verfahren schneller sein sollte.

Answer 1

"Sollte sein" zu sagen ist eine riskante Sache in Bezug auf die Leistung von Datenbanken. Oft habe ich festgestellt, dass Temporäre Tabellen die Dinge beschleunigen, manchmal sogar dramatisch. Die einfache Erklärung ist, dass es dem Optimierer erleichtert, sich zu wiederholende Arbeit zu vermeiden.

Natürlich habe ich auch gesehen, dass Temporäre Tabellen die Dinge verlangsamen, manchmal sogar erheblich.

Es gibt keinen Ersatz für das Profiling und das Studieren von Abfrageplänen (lesen Sie ihre Schätzungen jedoch mit Vorsicht).

Answer 2

Natürlich wählt SQL Server den falschen Abfrageplan aus. Ja, das kann passieren, mir ist das schon ein paar Mal genau wie dir passiert.

Das Problem ist, dass die Optimierung einer Abfrage (du erwähnst eine "komplexe Unterabfrage") eine nicht triviale Aufgabe ist: Wenn du n Tabellen hast, gibt es ungefähr n! mögliche Join-Reihenfolgen - und das ist nur der Anfang. Es ist also durchaus möglich, dass es sinnvoll ist, zuerst deine innere Abfrage und dann deine äußere Abfrage auszuführen, aber SQL Server kann diese Information nicht in angemessener Zeit ableiten.

Was du tun kannst, ist SQL Server zu helfen. Wie Dan Tow in seinem großartigen Buch "SQL Tuning" schreibt, ist der Schlüssel normalerweise die Join-Reihenfolge, beginnend mit der selektivsten Tabelle. Wenn du auf dein Bauchgefühl gehst (oder die Methode in seinem Buch anwendest, die viel besser ist), könntest du feststellen, welche Join-Reihenfolge am angemessensten wäre, und dann den Abfragehinweis FORCE ORDER verwenden.

So oder so, jede Abfrage ist einzigartig, es gibt keinen "Magic Button", um SQL Server schneller zu machen. Wenn du wirklich herausfinden möchtest, was los ist, musst du dir die Abfragepläne deiner Abfragen ansehen (oder uns zeigen). Andere interessante Daten werden durch SET STATISTICS IO angezeigt, was dir mitteilen wird, wie viel (kostspieligen) Festplattenzugriff deine Abfrage verursacht.

Answer 3

Ich habe diese Frage hier noch einmal wiederholt: Wie kann ich eine Unterabfrage dazu zwingen, sich so zu verhalten wie eine #temp-Tabelle?

Das Entscheidende ist, ja, ich verstehe, dass der Optimierer manchmal berechtigt ist, mit Ihren Unterabfragen zu spielen, als wären sie nicht vollständig eigenständig, aber manchmal trifft er eine falsche Entscheidung, wenn er versucht, klug zu sein, auf eine Weise, die uns allen bekannt ist. Ich sage, es muss einen Weg geben, diese "Klugheit" bei Bedarf abzuschalten, anstatt einen Ansatz mit Ansichten durch temp-Tabelle zu ruinieren.