Langsame Abfrage wird durch Schreiben in Tabelle blitzschnell

Question

Guten Tag,

mich würde sehr interessieren, wie folgender Umstand zu erklären ist:

Unter SQL Server 2016 haben wir eine recht komplexe Abfrage, welche zur Ausführung ca. 4min benötigt (Ergebnis sind knapp 800 Datensätze, zusammengeraffelt aus verschiedenen Tabellen, welche bis zu ca. 100t Datensätze enthalten).

Wenn ich jedoch mittels Insert Into Tabelle from Abfrage genau die gleiche Abfrage in eine (bereits vorhandene) Tabelle schreibe, benötigt dieser Aufruf nur rd. 25 Sekunden!

Das Ergebnis ist - natürlich - in beiden Fällen identisch, aber durch das Schreiben in eine Tabelle einen solchen "Boost" zu erreichen, macht mich sprachlos...

Freue mich über Hinweise!

Danke + Grüße,

Andreas

Answer 1

Da ist wie immer raten angesagt.
Ein Grund kann die Datenmenge sein, also nicht die reinen 800 Sätze, sondern alles was zu dem Resultset gehört.
Der Client macht ja nicht nur die reine Abfrage mit den Daten sondern an Hand des Ergebnisses werden auch die Metadaten der Spalten abgerufen.
Bei einem lokalen "Insert Into Select from" entfällt die Netzlaufzeit sowie die Abfrage von zusätzlichen Metadaten.

Wobei auch hier 25 Sekunden für 800 Sätze im Ergebnis nicht gerade schnell sind.

Und wie lange dauert dann das Laden der Daten aus der Into-Tabelle?

Da helfen manchmal auch "Local/Global Temporary"-Tables, die connectionspezifisch sind und somit das parallele Ausführen der Abfrage von mehreren Clients ohne Konflikte erlauben.

Answer 2

Moin,

lass Dir doch den Execution Plan anzeigen, dann wirst Du vielleicht auch schon sehen, wo die Sekunden bleiben:

---

Evgenij Smirnov

I work @ msg services ag, Berlin -> http://www.msg-services.de
I blog (in German) @ http://it-pro-berlin.de
my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
Exchange User Group, Berlin -> http://exusg.de
Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com

Answer 3

Hallo Andreas,

ist das Verhalten denn bei Dir reproduzierbar oder war es einfach nur so, das Du die zweite Abfrage mit dem INSERT kurz nach der ersten ausgeführt hattest und so die Daten noch im Cache (buffer pool) vorhanden waren?

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 4

Hallo Olaf,

danke für Deine Nachfrage.

Ja, ist reproduzierbar. Egal, welches Statement ich direkt im Management Studio zuerst ausführe - die Zeitverhältnisse bleiben gleich.

Grüße,

Andreas

Answer 5

Danke für den Beitrag.

Beide Statements führe ich direkt im Management Studio aus.

Die Daten aus der Into-Tabelle sind blitzschnell geladen.

Offenbar ist das manchmal ein sinnvoller Weg, um langsamen Abfragen Beine zu machen...

Answer 6

Danke für Deinen Hinweis bezügl. Abfragepläne.

Das sind allerdings in dieser Konstruktion absolute Monstertapeten... :(

Jedenfalls konnte ich schon einige Unterschiede mittels der einzelnen Einträge in der Ergebnisliste feststellen.

Man müsste nun die "normale" Abfrage dazu bringen, den ganz offensichtlich viel schnelleren Ablauflplan des insert-Statements zu verwenden (weiß ich leider nicht wie so etwas gehen kann).

Eigentlich würde ich erwarten, das da der Server quasi "von selbst" drauf kommen könnte...?!

Grüße,
Andreas

Answer 7

Man müsste nun die "normale" Abfrage dazu bringen, den ganz offensichtlich viel schnelleren Ablauflplan des insert-Statements zu verwenden (weiß ich leider nicht wie so etwas gehen kann).

Naja, vorgesehen ist es, aber es ist schon höhere Schule ;-) Siehe https://technet.microsoft.com/en-us/library/ms186343(v=sql.105).aspx 

Oder ihr probiert es mit SQL 2017, da ist mit Adaptive Query Processing einiges automatisiert worden.

 

---

Evgenij Smirnov

I work @ msg services ag, Berlin -> http://www.msg-services.de
I blog (in German) @ http://it-pro-berlin.de
my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
Exchange User Group, Berlin -> http://exusg.de
Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com

Answer 8

Hallo zusammen,

ein ähnliches Verhalten konnte ich vor kurzem ebenfalls beobachten - noch extremer.

Hier erzielte ich eine Beschluenigung von vorher 6-8 Minuten auf wenige Sekunden.

Ein Zufallsergebnis, ich benötigte die Tabelle als Zwischentabelle zur Weiterverarbeitung.

Auch bei mir ist das Ergebnis reproduzierbar.

Eine Eigenheit von SQL Server 2016?

Answer 9

Übrigens, hier ist noch ein interessanter Punkt: Das ganze könnte auch, je nach Recovery Model, noch einmal beschleunigt werden: http://persistencevision.blogspot.de/2012/08/select-into-is-faster-than-insert-select.html

---

Evgenij Smirnov

I work @ msg services ag, Berlin -> http://www.msg-services.de
I blog (in German) @ http://it-pro-berlin.de
my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
Exchange User Group, Berlin -> http://exusg.de
Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com

Answer 10

@Evgenij

die unteschiedliche Perfomance abhaengig vom Recovery Model ist nicht ueberraschend sondern faellt unter den Begriff "Minimally Logged Operations" und ist offiziell im Artikel Operations That Can Be Minimally Logged dokumentiert.

Dazu noch einen neueren Artikel rerequisites for Minimal Logging in Bulk Import

---

Please use "Mark as Answer" if my post solved your problem and use "Vote As Helpful" if a post was useful.

Answer 11

Moin,

ich meinte natürlich nicht, dass dieselbe Operation schneller oder langsamer wird, je nach Recovery Model der DB, gegen die es geht, sondern dass das Verhältnis zwischen den verschiedenen Operationen mal so und mal so herum ausfällt.

Vielleicht erschließt sich das auch aus dem Minimal Logging, beim schnellen Durchlesen meine ich aber gesehen zu haben, dass beide Operationen - SELECT INTO und INSERT SELECT - minimal geloogt werden...

---

Evgenij Smirnov

I work @ msg services ag, Berlin -> http://www.msg-services.de
I blog (in German) @ http://it-pro-berlin.de
my stuff in PSGallery --> https://www.powershellgallery.com/profiles/it-pro-berlin.de/
Exchange User Group, Berlin -> http://exusg.de
Windows Server User Group, Berlin -> http://www.winsvr-berlin.de
Mark Minasi Technical Forum, reloaded -> http://newforum.minasi.com