ukulele
Datenbank-Guru
- Beiträge
- 5.170
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...
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:
