Performance-Problem mit Subselects

[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.

Zitat von DasWolf:

Was für Unterabfragen sind das denn genau? Unterabfragen im SELECT- oder WHERE-Teil?
Beinhalten die Unterabfragen a in (select a from...) oder 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 von TigerLilly:

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 von mkinzler:

Bis dann am Besten die Subselects umbauen

[TigerLilly]

Zitat von 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.

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 von Jasocul:

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.