SQL - Materialized Query Tables

[Fuerchau]

Da es sich hier ja um die UDF's handelt gewinne ich mit Views auch nicht mehr Performance als mit den ggf. jetzt schon durchgeführten Queries da die UDF's ja in die View eingebettet werden müssen.

Um schnell auf die Daten zuzugreifen muss man hier tatsächlich regelmäßig aus den Daten neue vollständige Tabellen aufbauen. Hierbei sind natürlich weitere Gesichtspunkte aus Performancegründen zu berücksichtigen:
- muss ich immer alles neu aufbauen?
- reicht ggf. ein partieller Neuaufbau mit vorherigem Delete eines bestimmten Zeitraumes?
Ein Beispiel:
Um den Umsatz in DWH zu schieben muss ich nicht immer alles neu aufbauen. Wenn ich weiß, das z.B. max. bis zum 1. des Monats rückwärts fakturiert wird, lösche ich die Daten ab 1. des Monates und bau ab da neu auf.
Der Auftragsbestand ändert sich ja grundsätzlich und ist daher eher immer vollständig neu aufzubauen.

Das ist z.B. das Prinzip, dass ich mit unserer BI-Lösung durchführe.

[B.Hauser]

Da es sich hier ja um die UDF's handelt gewinne ich mit Views auch nicht mehr Performance als mit den ggf. jetzt schon durchgeführten Queries da die UDF's ja in die View eingebettet werden müssen.

Ohne Analyse kannst Du das doch gar nicht wissen!!

Vielleicht sind die UDFs auch gar nicht der Grund, sondern fehlende Indices (Binary Radix Tree und/oder EVIs)
Vielleicht sind die UDFs nicht notwendig oder werden die Spalten mit den UDFs nicht ausgewählt.
Vielleicht können sowohl die SELECT als auch die UDFs für eine bessere Performance umgeschrieben werden (z.B. keine UDFs oder Skalaren Funktionen auf der linken Seite der Vergleichsoperatoren in den WHERE-Bedingnungen)

Birgitta

[Fuerchau]

"Allerdings ist es bei uns so, dass viele Daten über User Defined Functions (UDF) zu ermitteln sind."
Nun glaube ich KM in der Hinsicht, dass er bereits geprüft hat, in wie weit seine UDF's durch native SQL's ersetzt werden können.
Sicherlich mag die eine oder andere UDF inzwischen einfacher mit scalaren Funktionen realisierbar sein, aber das steht hier ja nicht zur Debatte.
Ich kann mir auch vorstellen, dass die UDF's selber inzwischen durch Cache-Funktionen zu beschleunigen sind. Aber auch das war hier nicht die Frage.

Dazu müsste KM z.B. eine UDF beschreiben, was diese denn nun leistet.
Betrachte ich z.B. die Infor-Anwendung, so baue ich hier gerne Wrapper um bestehende Infor-Routinen die mit Native-IO irgendwas zaubern und mir ein Ergebnis liefern (z.b. Preisfindung).
Dies werde ich nie und nimmer per scalaren SQL's realisieren können.

[KingofKning]

Mal so ganz blöd gefragt: Braucht ihr wirklich Tagesaktuelle Werte oder reichen nicht die vom Vorabend?

GG

[andreaspr@aon.at]

Wie Birgitta schon sagte, wenn man nicht die genaue Situation kennt ist es schwer genaue Aussagen zu treffen.
Manchmal - wenn man sich erst einmal ansieht, was das eigentliche Ergebnis sein soll - ist es durchaus möglich durch eine Umstrukturierung der gesamten Abfrage jede menge an Performance zu gewinnen.
Aber dazu ist es halt hin und wieder erforderlich eine etwas detailliertere Analyse zu starten.
Man muss nicht immer sofort mit Kanonen auf Spatzen schießen.

[Fuerchau]

Da hast du sicherlich recht, es ging aber nicht darum, auch noch die UDF's mit skalaren SQL-Funktionen zu ersetzen.
Und meine "Analyse" bezog sich nun mal ausschließlich auf die Aussagen von KM.
Eine Analyse der internen UDF's ist für diese Aufgabenstellung nicht erforderlich da dies die Komplexität der Aufgabenstellung sprengt.

Wenn schon mal die Frage nach Performance gestellt wird verlangt ja auch keiner ein Redesign des Datenbankmodells.

[KM]

Danke schon mal für die zahlreichen Anworten. Hier sind meine Anmerkungen dazu:

Ein Live-Update der Daten ist für unsere Zwecke nicht nötig und ist auch nicht unbedingt der Sinn eines BI-Systems. Ein abgeschlossener Zeitraum (z.B. bis einschließlich Vortag) ist völlig ausreichend. Und so wie ich die MQTs auf der AS/400 verstanden habe, gibt es auch gar keinen "REFRESH IMMEDIATE" bzw. "MAINTAINED BY SYSTEM". Ich würde hierfür einfach Planungsjobs einbauen, die dann komfortabel den "Refresh Table" auf die MQTs machen.

Ich habe gestern dann noch herausgefunden, dass diese ganzen Einschränkungen bei den Abfragen in der MQT nur zutreffen, wenn man "ENABLE QUERY OPTIMIZATION" verwendet. Ich habe dann mal auf "DISABLE" umgestellt und konnte meine MQT problemlos erstellen. Da wir die MQTs auch nicht in weiteren komplexen Abfragen verwenden, sondern komplett so wie sie sind ins BI-System laden, haben wir auch keine Performance-Einbußen.

Ich hab das jetzt mal getestet. Die ursprüngliche SQL-Abfrage (mit den UDFs) dauert in meinem Beispiel ca. 70 Sekunden. Gerade die UDFs sind es, die für die lange Laufzeit verantwortlich sind. Allerdings können wir die schlecht umstrukturieren, denn sie sind recht komplex. Deshalb haben wir sie ja in UDF ausgelagert. Nachdem ich die Daten in eine MQT geladen habe, dauert die Abfrage derselben Daten aus der MQT nur noch 0,3 Sekunden. Ich glaube ehrlich gesagt nicht, dass ich durch diverse Umstrukturierungen auf diese Antwortzeiten komme. Da finde ich es schon sinnvoller diese Daten in MQTs auszugliedern.

@Fuerchau: Was genau meinst Du mit "echtem BI-System"? Etwa die großen (Cognos, Hyperion, SAS, Board, etc.), die preislich im hohen 5-stelligen oder 6-stelligen Bereich liegen? Das kommt für uns sicher nicht in Frage. Ich verwende ja ein "echtes" BI-System und das kostet gar nix bzw. nur Kleinbeträge. Und was ich so in der ersten Testphase herausgefunden habe, ist es sehr mächtig und für unsere Zwecke völlig ausreichend.

Viele Grüße,
KM

[andreaspr@aon.at]

Wenn du noch weiter Tunen willst, kannst du über die MQTs dann auch noch Indice anlegen.

[Fuerchau]

Rufe mich gerne dazu an, wir bewegen uns da im 4-stelligen bis max. unteren 5-stelligen Bereich.
Meine Erfahrung ist einfach, dass die kostenlosen BI's nicht wirklich was können.
Die Anforderungen sind z.B. Integration von Daten verschiedener Quellen in einem Bericht mit gleichzeitigem Anzeigen der Bezeichnungen zu den vorhanden Schlüsseln.
Das schaffe ich auch nicht in in Qlikview und schon gar nicht in BIRT.

Ach ja, und MQT werden bei uns auch nicht benötigt.

[KM]

Meine Erfahrung ist einfach, dass die kostenlosen BI's nicht wirklich was können.

OK, dann schau Dir doch das neue Power BI von Microsoft an und vergleiche mal was Dein Tool kann und Power BI nicht. Vergleiche aber auch was Power BI kann und Dein Tool nicht ;-)

Ach ja, und MQT werden bei uns auch nicht benötigt.

Und wie kommst Du dann performant an die Daten? Und zwar genauso performant wie mit MQTs?

[Fuerchau]

Nun, das Thema ist hier eigentlich falsch, aber was solls:
Power BI kostenlos:
Max. 1GB / User, max. 10.000 Datensätze / Stunde
Power BI 10$ je User und Monat:
Max. 10GB / User, max. 1.000.000 Datensätze / Stunde
Generell:
Importieren von Daten und Berichten aus Excel-, CSV- und Power BI-Desktop-Dateien

Das verfügbare Volumen je User ist schon etwas seltsam und die Datenbegrenzung ebenso.
Nehme ich also einen Umsatzbericht der aus der AS/400 500.000 Zeilen benötigt, kann ich den ggf. in der kostenlosen Version gar nicht ansehen?
Oder muss ich da 5 Stunden auf die Daten warten?

Die wesentliche Einschränkung ist aber der Import auf wenige Formate.
Wie sieht es mit anderen Datenquellen aus (Oracle, Sybase, Access, SQL-Server)?

Auch hier sieht es i.W. so aus, dass die Daten für den Bericht jedes mal wieder neu aus dem Quellsystem geladen werden. Dies macht BIRT, Web/Query und Qlikview ja genauso.
Kann ein Bericht Daten aus unterschiedlichen Quellen verknüpfen?

Beispiel: Ich habe den Umsatz auf der AS/400 und die Umsatzplanung in Excel. Kann ich nun einen Vergleich zwischen diesen Daten in einem Bericht darstellen?

Wie sieht es mit der Drilldown-Funktion aus?
Findet die innerhalb des Berichts statt oder muss ich hier Sub-Berichte erstellen?

Kann ich das Ganze im Batch auf einem Server machen und die Berichtsausgabe per Mail versenden?
Es gibt da schon sehr viele Fragen und Anforderungen an ein "echtes" BI-System.

Und warum keine MQT'?
Ganz einfach:
Ein "vernünftiges" BI lädt die Daten in eine eigene Datenbank.
Begründung:
- lokale DB-Größe bis die Platte platzt
- Anzahl User unbeschränkt
- Konsolidierung, also zusammenführung, von Daten aus verschiedenen Quellen
- Strukturierung und Anpassen der Daten, insbesonders der Datentypen bei verschiedenen Quellen
- Performante Abfrage der Daten unabhängig vom Hostsystem
- u.v.m.
D.h., die MQT's mit den täglichen Updates werden nicht benötigt, da man nur die neuen Daten mit simplem SQL (also incl. der UDF's) importiert.
Da nur aktualisiert, also ergänzt wird, ist die Abfragedauer nicht so entscheidend.
Deine Abfrage von 70 Sekunden ließe sich im Batch als Import also durchaus alle 5 Minuten durchführen um die Daten noch aktueller zu halten.

Beispiel:
Ein Kunde ist zum 1.1.2014 von MAS90 auf SAP umgestiegen.
Nun befinden sich die Umsätze vor 2014 in MAS90 und danach in SAP.
Mit einem "echten" BI kannst du diese Daten nun halt gemeinsam auswerten.

"Ich habe gestern dann noch herausgefunden, dass diese ganzen Einschränkungen bei den Abfragen in der MQT nur zutreffen, wenn man "ENABLE QUERY OPTIMIZATION" verwendet. Ich habe dann mal auf "DISABLE" umgestellt und konnte meine MQT problemlos erstellen. Da wir die MQTs auch nicht in weiteren komplexen Abfragen verwenden, sondern komplett so wie sie sind ins BI-System laden, haben wir auch keine Performance-Einbußen."
Diese Antwort hätte ich bzgl. deines Problems eigentlich von Birgitta erwartet, dann wäre der Thread allerdings nicht so interessant geworden.

[andreaspr@aon.at]

Um auch noch meinen Senf dazu zugeben:
Die Argumentation warum NICHT MQTs sind für mich jetzt keine "Wow"-Argumente. Vor allem da sie teilweise auch nichts mit MQTs zu tun haben.
Ich kann ja MQTs am Server erstellen und im BI mit den zusammengefassten Daten weiter arbeiten.
Ob ich die Logik jetzt am Server oder in die BI verlagere ist für mich da mehr Geschmackssache bzw. wird es auch Situationsabhängig sein.