Temporäre Tabellenverwendung in SQL server

Das ist ein bisschen eine offene Frage, aber ich würde gerne Leute Meinungen hören.

Ich nutze nur ausdrücklich deklarierte temporäre Tabellen (entweder Tabellenvariablen oder reguläre #tmp Tabellen), wie ich glaube, dass dies nicht zu prägnanter, lesbarer und debugfähiger T-SQL führt. Ich denke auch, dass SQL einen besseren Job machen kann, als ich von der Verwendung von temporärem memory, wenn es erforderlich ist (wie, wenn Sie eine abgeleitete Tabelle in einer Abfrage verwenden).

Die einzige exception ist, wenn die database keine typische relationale database ist, sondern ein Stern- oder Schneeflockeschema. Ich verstehe, dass es am besten ist, Filter auf die Fakten-Tabelle zuerst anwenden und dann die resultierende Temp-Tabelle verwenden, um die Werte aus Ihren Dimensionen zu erhalten.

Ist das die gemeinsame Meinung oder hat jemand eine entgegengesetzte view?

Temporäre Tabellen sind für einen komplexen Batch-process wie einen Report oder einen ETL-Job am nützlichsten. Im Allgemeinen würden Sie erwarten, sie ziemlich selten in einer Transaktionsanwendung zu verwenden.

Wenn du komplexe Abfrage mit einem Beitritt mit mehreren großen Tischen (vielleicht für einen Bericht) machst, kann der Abfrage-Optimierer vielleicht nicht in der Lage sein, dies in einem Treffer zu optimieren, so dass temporäre Tische hier ein Gewinn werden – sie zerlegen die Abfrage in eine Serie von einfacheren, die dem Abfrage-Optimierer weniger Gelegenheit geben, den Plan zu verkürzen. Manchmal haben Sie eine Operation, die nicht in einer einzigen SQL-statement überhaupt getan werden kann, so dass mehrere Schritte für die Verarbeitung notwendig sind, um den Job überhaupt zu tun. Auch hier sprechen wir hier über komplexere Manipulationen.

Sie können auch eine temporäre Tabelle für ein Zwischenergebnis erstellen und dann die Tabelle indexieren, evtl. sogar einen gruppierten Index insert, um eine nachfolgende Abfrage zu optimieren. Dies könnte auch eine schnelle und schmutzige Möglichkeit sein, eine Berichtsabfrage auf einem System zu optimieren, in dem Sie keine Indexe zum databaseschema hinzufügen dürfen. SELECT INTO ist für diese Art von Operation nützlich, da es minimal protokolliert (und daher schnell) ist und nicht benötigt, um die Spalten eines select und insert auszurichten.

Andere Gründe könnten das Extrahieren von data aus XML-Feldern mit CROSS APPLY und xpath-Abfragen beinhalten. Im Allgemeinen ist es viel effizienter, diese in einen temp-Tisch zu extrahieren und dann an der temp-Tabelle zu arbeiten. Sie sind auch viel schneller als CTE's für einige Aufgaben, da sie die Abfrageergebnisse materialisieren, anstatt die Abfrage neu zu bewerten.

Eine Sache zu beachten ist, dass temporäre Tabellen sind genau die gleiche Struktur, die die Abfrage-Engine verwendet, um Zwischen-Join-Ergebnisse zu speichern, so gibt es keine Performance-Strafe, um sie zu verwenden. Temporäre Tabellen erlauben auch Mehrphasenaufgaben mit Set-Operationen und machen Cursor fast (nicht ganz aber fast) unnötig im T-SQL-Code.

"Code Smell" ist eine Übertreibung, aber wenn ich viele einfache Operationen mit temporären Tischen sah, würde ich mich fragen, was los war.

Es hängt wirklich davon ab, was du tust. Ich versuche in der Regel, sie zu vermeiden, aber manchmal musst du etwas kompliziertes machen, das mehrere Schritte macht. Im Allgemeinen ist dies weit über die einfache Auswahl aus Tisch Zeug. Wie alles andere ist es ein Werkzeug, das du wissen musst.

Ich würde mit Ihnen einverstanden sein, dass ich normalerweise das db Handle Sachen hinter den Kulissen, aber es gibt timeen, wenn es die Optimierung ist aus und Sie müssen gehen und tun es von Hand.

Ich sehe Temp-Tische als eine Art SQL-Code-Geruch, nur als letztes Resort verwendet werden. Wenn du die data zwischenspeichern musst, bevor du eine endgültige Ergebnismenge bekommst, dann zeigt es mir in der Regel einen schlechten DB-Entwurf an.

Temp-Tabellen haben sicherlich entsprechende Verwendungen, sie sind kein Code-Geruch, wenn sie richtig verwendet werden. Eines der schönen Dinge über sie ist, dass sie in tempdb leben, die typischerweise auf Simple Recovery-model eingestellt ist. Dies bedeutet, dass, wenn Sie mit temp-Tabellen für das, was sie gut für (meistens Bulk-Operationen) sind, generieren Sie eine minimale Menge an Protokoll im Vergleich zu dem, was die gleiche Operation auf Tischen in Ihrer Produktion db tun würde, was wahrscheinlich ist in voller Erholung model.

Wenn, wie ein anderes Poster vorgeschlagen, Ihre Produktion db ist auf gute Hardware, aber Ihre tempdb ist nicht, fragen Sie Ihre DBA, um es zu bewegen. SQL server selbst verwendet tempdb ziemlich viel, um Ihre Abfragen zu verarbeiten, also ist es wichtig für tempdb, ein Hochleistungshaus zu haben.

Tabellenvariablen sind eine andere Kreatur ganz. Sie leben nur im Gedächtnis. Eine gute Verwendung für sie ist, wenn Sie eine function haben, die Sie für jede Zeile in Ihrer Abfrage mit CROSS APPLY aufrufen müssen. Wenn diese function teuer ist, aber die Anzahl der verschiedenen Ergebnisse, die Sie von ihr bekommen können, ist klein, können Sie deutlich höhere performance von Precomputing der Ergebnisse aller möglichen Anrufe (oder vielleicht alle möglichen Anrufe für Ihre datamenge) und speichern, dass in einem Tabellenvariable und dann mit dieser Tabellenvariable anstatt mit CROSS APPLY zu verbinden.

Ich vermeide auch temporäre Tische. Es ist mein Verständnis, dass temporäre Tabellen auf MS SQL server immer in der filegruppe der Master-database sind. Was das bedeutet, ist, dass, während Ihre Produktion Anwendung Tabellen sind wahrscheinlich auf einige teure, Hochleistungs-RAID-Setup, Ihre temporären Tabellen befinden sich überall dort, wo MS SQL server installiert wurde, die wahrscheinlich auf Ihrem C: Laufwerk unter dem Programmdateien Verzeichnis ist.

Auch nützlich, wenn Sie einen datasatz haben, der einmal abgerufen werden muss und immer wieder in nachfolgenden statementen verwendet wird.

Macht diese langen Batch-processe besser lesbar (manchmal ist das wichtiger als performance).