Performance-Problem mit Subselects

[Jasocul]

In einer SQL-Abfrage habe ich 2 Subselects. In beiden greife ich auf die selbe Tabelle zu, nur die Bedingungen sind ein wenig anders.

Ich habe jetzt folgendes Phänomen:
Wenn beide Subselects in der Gesamtabfrage enthalten sind, dauert es ewig.
Nehme ich eins der Subselects raus, ist die Anwort in wenigen Sekunden da. Dabei ist es egal, welches der Subselects ich dafür nehme.

Ich greife in den Selects nur auf Tabellen zu (also keine Views, Functions, o.ä.).
Indexe werden gezogen, auch wenn bei einem Subselect ein Index Scan entsteht. Es gibt kein Table Scan.

Gibt es hier jemanden, der noch eine gute Idee Idee hat, wie ich das beheben kann?

Folgendes habe ich bereits versucht:

• union
• Ein einzelnes Subselect daraus machen mit "or"
• Mit "with" schon die Datenmenge beschränken
• Mit case arbeiten, damit je nach Bedingung nur eine Abfrage ausgelöst wird

In allen Fällen das selbe Ergebnis. Sobald beide Subselects in der Abfrage stehen, ist die Performance im Nirwana.

[mkinzler]

Wirklich Subselects oder Joins?

[Jasocul]

Ja, Subselects.
Eine Umstellung auf Joins habe ich noch nicht getestet.

[hoika]

Hallo,
ein SubSelect wird ja für jeden Datensatz der Hauptabfrage durchgeführt,
das kann halt dauern.

Ich würde was anderes Probieren (wie oben schon gesagt, Outer Joins z.B.)

[Bernhard Geyer]

Was sagt der Query-Analyser bezüglich Ausführungsplan dazu?

Wir hatten mal das Problem (bei MS SQL Server) da hier der Query-Analyser in einer Version Mist gebaut hat
und für relativ einfache Abfrage meinte mindestens 1 GB an RAM zu benötigen und eine riesige Ergebnisse zu haben.

[Jasocul]

Zitat:

Zitat von hoika (Beitrag 1489247)

Hallo,
ein SubSelect wird ja für jeden Datensatz der Hauptabfrage durchgeführt,
das kann halt dauern.

Ich würde was anderes Probieren (wie oben schon gesagt, Outer Joins z.B.)

Genauso ist es richtig.
Da habe ich wohl den Wald vor lauter Bäumen nicht mehr gesehen.
Die Abfragen hatte ich ja schon mit with in den Tests vorbereitet, aber dann einfach nicht daran gedacht, dass es dann mit einem Outer join ganz einfach umzubauen ist.

Zitat:

Zitat von Bernhard Geyer (Beitrag 1489248)

Was sagt der Query-Analyser bezüglich Ausführungsplan dazu?

Wir hatten mal das Problem (bei MS SQL Server) da hier der Query-Analyser in einer Version Mist gebaut hat
und für relativ einfache Abfrage meinte mindestens 1 GB an RAM zu benötigen und eine riesige Ergebnisse zu haben.

Der hat mir nur den Hinweis auf auf den Index Scan geliefert.

Faszinierend finde ich es trotzdem, dass jedes Subselect alleine kein Problem verursacht und erst die gleichzeitige Nutzung die Performance einbrechen lässt.
Ich kann jetzt zwar das Problem beheben, aber die Ursache des Phänomens kenne ich immer noch nicht.

Vielen Dank für den richtigen Schubser

[Delphi.Narium]

Zitat:

Zitat von hoika (Beitrag 1489247)

Hallo,
ein SubSelect wird ja für jeden Datensatz der Hauptabfrage durchgeführt,
das kann halt dauern.

Ich würde was anderes Probieren (wie oben schon gesagt, Outer Joins z.B.)

Zitat:

Zitat von Jasocul

Faszinierend finde ich es trotzdem, dass jedes Subselect alleine kein Problem verursacht und erst die gleichzeitige Nutzung die Performance einbrechen lässt.
Ich kann jetzt zwar das Problem beheben, aber die Ursache des Phänomens kenne ich immer noch nicht.

Naja:

Man könnte in diesem Zusammenhang grob sagen:

Wenn ein Subselect schnell (10 ms) ist, aber für 1.000.000 Datensätze 1.000.000 mal schnell ausgeführt wird, so kann das dann insgesamt schon mal 'ne Weile dauern. -> 1.000.000 mal 10 ms -> ca. 2 Stunden und 45 Minuten.
Nur so als sehr grober Gedankengang, wenn man nach derartigen Performanzproblemen sucht.

Und nein: Das ist keine allgemeingültige Regel, sondern nur so ein Gedankenfetzen, den man im Hinterkopf behalten sollte.

[Jasocul]

Zitat:

Zitat von Delphi.Narium (Beitrag 1489254)

Man könnte in diesem Zusammenhang grob sagen:

Wenn ein Subselect schnell (10 ms) ist, aber für 1.000.000 Datensätze 1.000.000 mal schnell ausgeführt wird, so kann das dann insgesamt schon mal 'ne Weile dauern. -> 1.000.000 mal 10 ms -> ca. 2 Stunden und 45 Minuten.
Nur so als sehr grober Gedankengang, wenn man nach derartigen Performanzproblemen sucht.

Und nein: Das ist keine allgemeingültige Regel, sondern nur so ein Gedankenfetzen, den man im Hinterkopf behalten sollte.

Da sind wir völlig einer Meinung.

In diesem Fall muss aber noch ein anderes Problem vorliegen.
Ich habe hierbei folgende Laufzeiten erhalten (Test mit 1000 Datensätzen als Ergebnismenge):
Mit beiden Subselects gleichzeitig: ca. 1 Minute 10 Sekunden
Nur Subselect 1: ca. 0,7 Sekunden
Nur Subselect 2: ca. 1,1 Sekunden

Dass ich dann nicht davon ausgehen kann, dass es nur 1,8 Sekunden dauert, ist mir klar. Aber in diesem Fall ist das um den Faktor 60 langsamer.
Es ist auch nicht das erste SQL, das ich optimiere. Eigentlich laufen solche Sachen immer auf meinem Tisch auf, wenn die Kollegen nicht mehr weiterwissen. Daher ist es mir schon peinlich, dass ich auf die banale Lösung mit dem Join nicht gekommen bin.

Ich habe mir die bisherige Abfrage gespeichert. Vielleicht habe ich irgendwann mal Zeit, das genauer zu untersuchen.

[Delphi.Narium]

Zitat:

Zitat von Jasocul (Beitrag 1489273)

Eigentlich laufen solche Sachen immer auf meinem Tisch auf, wenn die Kollegen nicht mehr weiterwissen.

Oh, da bist Du aber nicht zu beneiden, den "Job" hatte ich auch mal ein paar Jahre, das ist nicht immer unbedingt spaßig, vor allem dann nicht, wenn man da gewaltige Laufzeitunterschiede herausarbeitet und das dann auch noch regelmäßig ;-)

Bezüglich SubSelect:

Mein Ansatz war immer:

Gibt es in einem Select ein Subselect dann lautet die erste Frage:

Wie bekomme ich das weg?

Nur wenn das nicht gelingt:

Wie bekomme ich das schneller?

[DasWolf]

Was für Unterabfragen sind das denn genau? Unterabfragen im SELECT- oder WHERE-Teil?
Beinhalten die Unterabfragen

Delphi-Quellcode:

a in (select a from...)

oder

Delphi-Quellcode:

exists(select 1 from...)

.

[Jasocul]

@Delphi.Narium:
Die Vorgehensweise ist bei mir ähnlich. Allerdings ist unser DWH in die Jahre gekommen und inzwischen ziemliches Stückwerk. Ich habe das auch nicht erstellt. Aber ich darf das DWH in ein paar Monaten komplett neu aufbauen. Veranschlagte Zeit 1 bis 2 Jahre. Mein Chef meinte nur, dass ich dann bitte auch alles normalisieren soll, damit das endlich was brauchbares wird. :-D

Zitat:

Zitat von DasWolf (Beitrag 1489283)

Was für Unterabfragen sind das denn genau? Unterabfragen im SELECT- oder WHERE-Teil?
Beinhalten die Unterabfragen

Delphi-Quellcode:

a in (select a from...)

oder

Delphi-Quellcode:

exists(select 1 from...)

.

Das sind Subselects im Select, um den Inhalt eines Feldes zu ermitteln. Das ganze läuft täglich in einem Script, um eine Tabelle zu füllen. Bisher war das kein Problem, da es nur um 100k Datensätze ging. Aber vor etwa 2 Wochen gab es eine Anpassung und jetzt geht es um knapp 500k Datensätze. Da machen sich dann auch Kleinigkeiten bemerkbar.

[TigerLilly]

Schau mal, ob deine SubSelects Locks erzeugen + diese ev. eskalieren.
Schau mal, ob "with (nolock)" etwas ändert.

[Jasocul]

Zitat:

Zitat von TigerLilly (Beitrag 1489420)

Schau mal, ob deine SubSelects Locks erzeugen + diese ev. eskalieren.
Schau mal, ob "with (nolock)" etwas ändert.

Locks können zu dem Zeitpunkt nicht vorliegen. Ich habe es jetzt aber trotzdem mal geprüft. Es liegen wirklich keine Locks vor, aber:
Im Lock-Protokoll (sp_lock) taucht in der Ressource ein "encryption scan" auf, wenn beide Subselects aktiv sind. Wenn nur eins davon aktiv ist, sieht alles normal aus. Die Datenbank ist aber gar nicht verschlüsselt!

Da muss ich mich jetzt wohl mal etwas weiter einsteigen. Oder kennt hier schon jemnd dieses Phänomen?

[Papaschlumpf73]

Wir hatten ein ähnliches Problem nach dem Umstieg auf MS SQL Server 2019. Einige Abfragen mit Sub-Selects waren plötzlich deutlich langsamer, als auf dem alten SQL-Server 2012.

Wir haben dann den Kompatibilitätsgrad der Datenbank auf 110 (SQL-Server 2012) herunter gesetzt und siehe da - die Abfragen flitzen wieder wie früher. Mit jeder höheren Version des Kompatibilitätsgrads werden die Abfragen immer langsamer. Eine langfristige Lösung haben wir aber noch nicht; müssen wohl alle Sub-Selects mal umbauen…

[Jasocul]

Das ist gut zu wissen. Ich bin mit dem Problem noch auf SQL Server 2008. Im Laufe des Jahres soll die DB nach Möglichkeit noch auf 2014 oder 2019 umziehen.

[mkinzler]

Bis dann am Besten die Subselects umbauen

[Jasocul]

Zitat:

Zitat von mkinzler (Beitrag 1489436)

Bis dann am Besten die Subselects umbauen

:thumb:

[TigerLilly]

Zitat:

Zitat von Papaschlumpf73 (Beitrag 1489433)

Wir hatten ein ähnliches Problem nach dem Umstieg auf MS SQL Server 2019. Einige Abfragen mit Sub-Selects waren plötzlich deutlich langsamer, als auf dem alten SQL-Server 2012.

Damit würde ich aber jedenfalls zum MS-Support bzw in die Foren zum SQl Server gehen.

Aber vielleicht stecken auch da Infos für dich:
https://stackoverflow.com/questions/...n-and-subquery
https://dba.stackexchange.com/questi...sub-query-tsql
https://www.sqlservercentral.com/for...-with-subquery

[jobo]

Zitat:

Zitat von Jasocul (Beitrag 1489422)

aber:
Im Lock-Protokoll (sp_lock) taucht in der Ressource ein "encryption scan" auf, wenn beide Subselects aktiv sind. Wenn nur eins davon aktiv ist, sieht alles normal aus. Die Datenbank ist aber gar nicht verschlüsselt!

Zu Encryption locks
Die locks bzw. dieser spezielle Locktyp dient wohl primär für TDE, aber eben nicht nur
https://docs.microsoft.com/de-de/arc...n-a-sql-server
Also hier im Fall relativ harmlos, außer, der Optimizer verhaut sich und setzt sie ein, ohne dass es Not tut.
Ein Downgrade der Compatibility als Gegenmaßnahme würde ja dazu führen, dass dieses und alle anderen neuen Features nicht mehr zur Verfügung stehen, die Holzhammermethode.
Dann lieber Optimizer Hints, die scheint es auch unter MSSQL Server zu geben, als Query Hints (u.a.)
https://docs.microsoft.com/en-us/sql...ql-server-2016
Damit kann man sicher gezielt und effizient einzelne Queries tunen, ohne dass man die komplette Abfrage neu schreibt.
https://www.sqlshack.com/query-optim...ps-and-tricks/

Umschreiben ist sicher die bessere Alternative, aber kostet auch mehr Feinarbeit/Zeit. Dazu sei mal noch die CTE genannt, die gerade bei wiederkehrender Nutzung gleicher Datenbestände sehr gut helfen kann.

Beides, Query Hints und CTE sind sicher auch abhängig von der SQL Server Version unterschiedlich mächtig oder verfügbar. Das habe ich nicht im Detail nachgeschaut. 2008 ist natürlich schon recht betagt, plus Downgrade ist man fast schon im vorigen Jahrtausend. GgF wäre hier zu bedenken, ob solche Features in Express Versionen verfügbar sind, falls ihr sowas auch nutzt.

[Jasocul]

Danke jobo.

Der 2008 soll ja abgelöst werden, kann aber noch dauern.
Umgeschrieben ist das SQL-Statement schon. Läuft stabil und mit erheblich besserer Performance.

Die Erläuterungen für das Encryption scan sind interessant. Bei mir werden es da wohl die Sort Spills sein.
Die Optimizer-Hints schaue ich mir auf jeden Fall in Ruhe an. Danke für den Link.