Performance-Totalausfall durch Filterkriterium

ukulele

Datenbank-Guru
Beiträge
5.131
Heute habe ich mal eine Merkwürdigkeit die ich gerne verstehen würde, vielleicht hat von euch jemand eine Idee (außer auf Postgres zu wechseln).

Der Sachverhalt ist leider sehr kompliziert aber ich versuche es mal einfach zu beschreiben. Ich kann leider nicht genau sagen in wie weit das überhaupt alles relevant ist.

Gegeben sind zwei Systeme zur Zeiterfassung, jedes hat eine eigene Datenbank. In System A / Datenbank A landen Aufwände, die später auch abgerechnet werden können. Hier gibt es im wesentlichen Datensätze mit Tagesdatum und einer Angabe Zeit in Minuten. System B ist eine Anwesenheitszeiterfassung, quasi eine klassische Stempeluhr mit einer Stempelung pro Datensatz. Beide Systeme sind auf ihren Zweck ausgelegt, das heißt es gibt gute Indexe auf z.B. Mitarbeiter FK oder Datum. System A ist leider mehr oder weniger eine Blackbox in der viel fehlt (z.B. FKs) und bei der ich nur begrenzten Zugriff auf die DB habe (DATEV EODB wem das was sagt). Ich kann zwar mit viel Mühe die Datenbank offline nehmen und auf einem Testsystem als Kopie einhängen aber naja...

Ich komme jetzt mit einer dritten Auswertungsdatenbank und linked servers daher und will eine Live-Auswertung bauen. Dazu habe ich bereits zwei Views angelegt:
a) Fragt aus DB A alle Benutzer ab, generiert über CTE eine Zeitreihe mit allen Datumswerten der letzten 3 Jahre (für alle Benutzer) und filtert diese so das jeder Benutzer nur eine Zeitreihe für alle Tage seit der Erfassung des ersten Aufwands bis zur Erfassung des letzten Aufwands bekommt. Das kostet natürlich ein bisschen mehr als irgendwelche fixen Werte aber eigentlich sollte das drinn sein. Konkret für alle Datensätze aller Nutzer 31.919 rows, CPU-Zeit = 109 ms, verstrichene Zeit = 1295 ms.

b) Holt aus DB B alle Buchungen und ordnet jedem Beginn über Window-Functions ein Ende zu (sofern möglich), berechnet die Zeitdauer und liefert mir eine Gruppierung und Sortierung. Ich habe dann pro Tag 0 bis n Zeiträume statt einzelne Stempelungen, mehr eigentlich nicht. 66625 rows affected, CPU-Zeit = 1281 ms, verstrichene Zeit = 1578 ms.

c) Eine dritte View soll jetzt a und b kombinieren (später noch mehr). Es soll der ganze Zeitstrahl per LEFT JOIN mit den Zeiträumen 0 bis n aus DB B gejoint werden, das ergibt 53318 rows affected, CPU-Zeit = 1781 ms, verstrichene Zeit = 3137 ms. Schon irgendwie etwas langsam aber sind auch alle Datensätze auf einmal, beim Auswerten später reduziert sich das i.d.R. auf einzelne Anwender pro Auswertung. c ergibt weniger Datensätze als b weil in a bereits inaktive Benutzer gefiltert werden und in b einige "Systembenutzer" stecken die in A nicht existieren, das ist also erstmal soweit so gut.

Problem: Wenn ich jetzt c ausführe und um einen Filter auf den Benutzernamen erweitere dann steigt meine Laufzeit massiv an auf irgendwas oberhalb von 30 Minuten! Der Ausführungsplan besagt das die Kosten beim Remotequery von DB A entstehen. Die Spalte Benutzername auf die gefiltert wird ist Teil der Benutzertabelle in DB A die möglicherweise keinen Index hat aber mit ~50 Einträgen auch keine echte Herausforderung ist. Leider mag ich dem MS Ausführungsplan keine wirkliche Ursache entlocken und bekomme von DB A nicht sonderlich viele Informationen zur Ausführung des remote querys.

Das ganze erscheint mir schon verrückt genug aber es kommt noch besser. Ich plane mit Row Level Security (RLS) für die Auswertung so das jeder nur seine eigenen Zeiten sieht. Das geht ziemlich gut (SQL Server ist 2022). a ist dabei etwas komplexer und joint noch eine fixe Tabelle auf die ich RLS machen kann und auf die ich mein filter predicate anwenden kann. Eigentlich nichts als ein Filter auf Benutzername = Windows Benutzer, also faktisch ein Filter auf die gleiche Spalte die mein Problem macht nur mit dem Unterschied das das deutlich schneller geht.

RLS ist kein leichtes Thema aber auch eigentlich nicht das Problem, das Problem besteht wenn ich im WHERE-Teil auf einen Benutzernamen filtern möchte. Das kommt in der Praxis nicht vor, da macht das RLS, aber ich verstehe einfach nicht wie das Problem so massiv zustande kommt. Hier mal ein Vergleich, das selbe Query (LEFT JOIN a und b) im Kontext des Benutzers xy mit aktivem RLS. Es kommt die selbe Anzahl an Datensätzen zurück, nur im zweiten Durchlauf steht ein WHERE benutzername = 'xy' zusätzlich im Query.

Ohne WHERE-Bedingung:
1636 rows affected, CPU-Zeit = 1235 ms, verstrichene Zeit = 1374 ms.
Mit WHERE-Bedingung:
1636 rows affected, CPU-Zeit = 1113234 ms, verstrichene Zeit = 1185529 ms (~20 Minuten).

Ein Verzicht auf RLS ändert nichts an diesen Zeiten. Ein WHERE-Filter auf eine andere Spalte aus View a funktioniert problemlos.

Ich habe innerlich mit dem Thema noch nicht abgeschlossen, mir fällt aber auch kein Ansatz mehr ein. Ich hoffe das Problem fällt mir nicht an anderer Stelle auf die Füße...
 
Zuletzt bearbeitet:
Werbung:
Ich hab das nur überflogen. Meine erste Idee bei Linked Servern (ich kenne das bei MS nur sehr minimal aus der Praxis, ich habe das mit verschiedenen Oracle Versionen gehabt) ist folgender Gedanke, der vermutlich allgemeingültig ist.
Die Verlinkung bringt die Optimizer ins Schwimmen, nicht grundsätzlich, aber sehr viel eher als bei einem Single System.
Du musst einen Weg finden, die Where Bedingung zu "vereinfachen".
Das können z.B. Materialized Views sein (im Zielsystem). Ausprobieren.
Das können aber z.B. auch Views sein, die auf den Linked System liegen und vorfiltern- so viel wie geht und so simple, dass nicht wieder der Effekt eintritt. Bspw. in der Linked Quelle einen View anlegen, der die Basistabelle auf den benötigten, maximalen Zeitraum einschränkt. Also biete schon auf Remoteseite nur die letzten 3 Jahre der Daten an, nicht alle 17 existierenden Datenjahre.
Verschiebe die Where Kriterien wenn möglich auf die lokalen Daten.

(Es ist wahrscheinlich Fummelei, auszuprobieren, was hilft. Solange Du nicht per Ausführungsplan valide Infos über die Umsetzung bei den Remotetabellen hast, bleibt nur, bestimmte Strategien zu versuchen. Ich hab keine Ahnung, was MS da bei Linked Servern liefert.)

So und warum das Ganze: Der Optimizer kann die Abfrage nicht sinnvoll umsetzen und macht es sich "einfach", er legt lokal eine temporäre Kopie der Linked Server Tabelle an und zwar vollständig, wenn es schlecht läuft. (muss nicht, kann aber passieren, Erfahrungswert) Bei kleineren Tabellen ist das ein super Konzept. Bei großen nicht, zumindest wenn nur ein geringer Teil der Tabelle benötigt wird.

Und noch weiteres Halbwissen/ Ideen:
Ein Problem des Optimizers kann sein, dass er nicht wie 'gewohnt' das Table Dictionary, Indizierung, Statistiken .. im Remote System anzapfen kann. Das ist leicht erklärbar, wenn man sich vorstellt, dass ein Remotezugriff idR strengen Berechtigungsrestriktionen unterliegt.
Mglw. gibt es dafür in MS auch Konzepte, dieses Problem zu umgehen.

Ich würde mal den pragmatischen Ansatz ausprobieren und mit Materialized Views oder Remote Views /- Filtern versuchen.
 
Deine Theorie teile ich, der Optimizer muss hier irgendwas tun. Das kuriose ist das man eher erwarten würde das RLS alles langsam macht aber da klappt es bzw. hat es den selben Effekt und das rettet mir erstmal den Arsch weil ich das WHERE Kriterum gar nicht brauche außer das ich gerne damit testweise meine eigenen Daten abfragen würde. Tatsächlich ist es so das die View aus Benutzername eine Spalte Windows-Anmeldename macht (in dem es die Domain davor setzt) und darauf basiert RLS. Wenn ich diese berechnete Spalte (berechnet innerhalb des Auswertungsservers) mit WHERE filtere ist alles schnell. Nur diese eine Spalte verursacht den Super-GAU.

Ich muss natürlich erwähnen das ich auf das Quellsystem keinen Einfluss habe, ich kann / darf in der Datenbank nichts anlegen / ändern, keine materialized view oder Indexe oder so. Ich musste mir die Datenbank schon in einem anderen Server einhängen um mal INFORMATION SCHEME sehen zu können und was es sonst noch so gibt. Ich darf mir keine View Definitionen ansehen auf der Produktivumgebung das ist maximal verdongelt.

Ich baue an dem ganzen jetzt erstmal weiter und kehre dann noch mal in Ruhe dahin zurück. Wenn es kein ähnliches Problem mehr gibt erreiche ich allein durch das RLS mein Ziel.
 
Ich muss natürlich erwähnen das ich auf das Quellsystem keinen Einfluss habe, ich kann / darf in der Datenbank nichts anlegen / ändern, keine materialized view oder Indexe oder so.
Ja, hab ich überlesen. Aber es beschreibt im Prinzip auch anschaulich was ich meine, Du hast da "nichts zu suchen" und für Deinen Prozess- der eigentlich etwas genau wissen sollte, womit er es zu tun hat, um es gut zu machen- ist vermutlich auch ein schwarzes Loch. (Rechtemangel auf Metainformationen)
Könnte sein, dass RLS genau an der Stelle für Lockerung sorgt. Ich vermute, dass es irgendwie installiert / aktiviert werden muss und dann höher priviligierte Prozesse die Nase etwas tiefer reinstecken dürfen. Aber das ist meinerseits pure Spekulation.

Eine windige Sache ist mir noch eingefallen (kommt auch von der Oracle Zeit, gibt's mglw aber ebenfalls in MSSQL):
Optimizer Hints
Das ist die Brechstange für den Optimizer. Oder etwas netter, "lass uns mal Klartext reden".
Du gibst innerhalb des SQL Statements Annotiationen an, wie der Optimizer vorgehen soll. Das geschieht natürlich auf der Seite des Konsumenten, als auf dem System, wo Du alles darfst.
 
ok, hier ist noch mehr, der Author scheint aber nicht recht überzeugt zu sein:
 
1. Aussage von Brent Ozar über Linked Server: Don't use them. 2. Warum so kompliziert? Importier die Tabellen aus A nach B und mach die Abfragen auf B mit den Daten aus A. Mach ich so bei meinen Systemen.
 
Oder die Daten einfach importieren, was deutlich schneller geht...
Gut, klar kann man das machen. Auf dem Niveau ist es dann auch nicht mehr weit bis zum Fax.

Bei Zeiterfassung auch sehr beliebt, die Nutzer tragen es selbst in verschiedene Systeme ein.
Die Chief Time Recording Manager der Standorte können dann aushandeln, welche Version richtig ist, das sichert Arbeitsplätze. Und Fachkräfte sind zum Glück nicht involviert. Chief Time Recording Manager Assistants machen die Drecksarbeit. Würde Hubertus Heil wahrscheinlich gefallen.

Was spricht dagegen, intelligente Lösungen zu suchen?
 
Werbung:
1. Aussage von Brent Ozar über Linked Server: Don't use them. 2. Warum so kompliziert? Importier die Tabellen aus A nach B und mach die Abfragen auf B mit den Daten aus A. Mach ich so bei meinen Systemen.
Hier geht es tatsächlich um Live-Daten. Ich könnte mir vorstellen einige Dinge nicht "live" zu machen, also Rahmendaten wie z.B. die Liste der Mitarbeiter als Kopie zu ziehen. Das werde ich vermutlich noch bauen, das bedarf natürlich einiger Planung und sauberer Umsetzung. Aber wenn die Leute Pause stempeln, wollen die eventuell nach der Pause Zeiten erfassen. Die Idee hinter einer Auswertung für jeden ist das er seine Zeiten (und Tätigkeiten) zwischen mehreren Systemen abgleichen kann. Das macht wirklich nur live Sinn.

Mein Chef will auch noch eine Auswertung mit Umsätzen angehen, so wie ich ihn kenne werden da auch grade fakturierte Rechnungen mit einfließen. Da erst auf einen Auswertungsbestand zu warten ist aber auch wirklich nichts was man als zeitgemäß empfindet.
 
Zurück
Oben