Seite 2 von 4

Re: Firebird 5 geht in den Beta-Test

Verfasst: Do 4. Mai 2023, 08:34
von martin.koeditz
Ein anderer Aspekt ist ja der Speicherverbrauch der partiellen Indizes. Statt die gesamte Tabelle abzugrasen, werden nur noch die benötigten Daten vorgehalten.

Bein einem Archivkennzeichen sind z.B. nur 10% der Daten aktiv und somit relevant. Die anderen 90% benötigen meist keinen Index. Wenn man hier eine Suche durchführt, dann eh sequenziell über die Tabelle.

Das lässt dann insgesamt mehr Platz für andere Daten. Von daher ist das schon ein interessantes Feature.

Gruß
Martin

Re: Firebird 5 geht in den Beta-Test

Verfasst: Do 4. Mai 2023, 09:44
von bfuerchau
Das ist für mein Dafürhalten auch an der Praxis vorbei.
Heute werden auch Daten, die ja angeblich nicht mehr aktuell sind, für Auswertungen trotzdem noch benötigt.
Gerade da Power-BI immer stärker im Kommen ist, ich versuche noch mit unserer Lösung dagegen zu halten, sind Indizes das A ond O der Abfragen.
Wir nutzen zwar die FB nicht für eine ERP-Anwendung sondern als DataWareHouse (DWH). Somit laufen sehr viele Abfragen gegen die DB nach verschiedenen Filtern und Kriterien.
Dabei werden benötigte Indizes ständig geprüft und bei Bedarf neu erstellt. Somit haben wir z.t. über 50 oder auch mal 100 Indizes für 1 Tabelle.
Abfragetechnisch lohnt sich das. Die DB wird zwar größer, aber bei langsamen Abfragen ist der Frust der Anwender noch größer.
Einzig beim ETL müssen die Indizes wieder deaktiviert werden, denn statt 2-3000 Inserts/Sekunde passieren bei 100 Indizes nur noch 3-10 Inserts/Sekunde.
Und Platz ist doch heute kaum noch ein Argument. Unsere DWH's werden bei Kunden schon schnell mal 70 - 150GB groß. Da heben wir sogar noch bis zu 3 Backups zusätzlich auf. Und aus Reorg-Gründen machen wir 1 Mal wöchentlich einen Backup/Restore, was die DB im Schnitt um 20% verkleinert.

Wenn man dann mit Power-BI daher kommt und z.T. unqualifizierte Abfragen gegen eine DB macht, kann das sogar Nachteile einer laufenden ERP-Anwendung bedeuten, da eben häufig per Tablescan alles gelesen werden muss. Beim Microsoft-SQL-Sever führt das im Zweifel sogar zu einer Tabellensperre (Lock-Escalation), die das gesamte ERP lahmlegt bis der Ladeprozess abgeschlossen ist.

Queryzeiten:
Bei einer komplexen Aggregat-Abfrage über eine Tabelle mit 1. Mio. Zeilen benötigt die FB auf einem I7 mit 20CPU's, 64GB Speicher, 2,6GHz und SSD ca. 70 Sekunden.
Diese Abfrage wird in verschiedenen Varianten 3 Mal geladen.
Dies macht 210 Sekunden.
Mache ich diese Abfragen über parallele Connections, benötigt jede Abfrage ca. 135 Sekunden. Durch die Parallelität bleibt es aber bei 135 zu 210 Sekunden eben besser.
Die Frage ist nun, warum 1 Abfrage über 1 Tabelle einzeln 70 und parallel 135 Sekunden dauert.

Re: Firebird 5 geht in den Beta-Test

Verfasst: Do 4. Mai 2023, 11:36
von martin.koeditz
Hallo bfuerchau,

das von dir beschriebene Szenario ist bei entsprechender Hardware sicherlich sinnvoll. Bei kleineren ERP-Systemen muss ich natürlich schauen, dass der vorhandene Speicher optimal genutzt wird. Ist dieser jedoch mit Daten gefüllt, die nie/selten abgefragt werden, habe ich u.U. ein Problem.

Nach meiner Erfahrung haben die Benutzer Verständnis für langsamere Archivabfragen. Die tägliche Arbeit selbst muss natürlich zügig ablaufen. Da sind wir uns in jedem Falle einig. ;)

Ich denke, es hängt mal wieder von den jeweiligen Anforderungen und Rahmenbedingungen ab ab.

Gruß
Martin

Re: Firebird 5 geht in den Beta-Test

Verfasst: Do 4. Mai 2023, 12:43
von bfuerchau
Von welchen Größenordnungen sprichst du?
Also 1 SSD mit 1TB gibts inzwischen für unter 60€.
Selbst eine 2TB HDD gibts für unter 50€.
Da sind die Lizenzkosten ja inzwischen erheblich größer.

Aber meine Erfahrrung besagt auch: Es wird immer am falschen Ende gespart. :roll:

Re: Firebird 5 geht in den Beta-Test

Verfasst: Fr 5. Mai 2023, 05:06
von vr2
bfuerchau hat geschrieben: Do 4. Mai 2023, 09:44 Das ist für mein Dafürhalten auch an der Praxis vorbei.
Heute werden auch Daten, die ja angeblich nicht mehr aktuell sind, für Auswertungen trotzdem noch benötigt.
Klar, weil es üblich ist, dass auch historische Daten gelegentlich noch geändert werden. Sollte zwar nicht sein, ist aber so. Aber ich glaube, ihr redet ein bisschen aneinander vorbei. Historische Daten sind was anderes als Daten mit schlechter Selektivität, und für letztere können partielle Indizes sinnvoll sein.
Gerade da Power-BI immer stärker im Kommen ist, ich versuche noch mit unserer Lösung dagegen zu halten, sind Indizes das A ond O der Abfragen.
Jaja, Microsoft macht jetzt jetzt auch auf BI für Endanwender und alles in der Cloud natürlich. Die Versuche beobachte ich schon so lange, dass es langweilig wird. Vorher wars Cliqview oder Tableau. Diese Systeme haben ihre Berechtigung, nur befreit es den Endanwender nie davon, die Datenzusammenhänge zu verstehen und auch nicht davon, dafür zu sorgen, dass die Daten und speziell deren Verknüpfungen valide sind. Man kann da trendy Grafiken zusammenklicken, aber ob die wiedergeben, was tatsächlich Sache ist, steht auf einem anderen Blatt. Denn dafür muss die Datenbasis passen, und das können die allerwenigsten Endanwender, egal welches Tool Du ihnen gibst. Selbe Strategie wie bei Excel, ihr könnt mit unserem Ranz alles machen, und ihr könnts auch grandios vor die Wand fahren, ist dann aber nicht unser Problem. Derweil steckt die IT-Abteilung regelmäßig terabyteweise Platten nach, weil das Controlling die hundertste Datenvariante einpflegt und keiner mehr durchblickt, welcher Datenstand denn gültig ist, abgesehen davon, dass ein Excel-Aufruf 10 Minuten dauert. In so einer Situation wenden sich Firmen an Dienstleister, die sowas professionell machen. Das Frontend ist dann vergleichsweise egal.
Wir nutzen zwar die FB nicht für eine ERP-Anwendung sondern als DataWareHouse (DWH). Somit laufen sehr viele Abfragen gegen die DB nach verschiedenen Filtern und Kriterien.
Dabei werden benötigte Indizes ständig geprüft und bei Bedarf neu erstellt. Somit haben wir z.t. über 50 oder auch mal 100 Indizes für 1 Tabelle. Abfragetechnisch lohnt sich das. Die DB wird zwar größer, aber bei langsamen Abfragen ist der Frust der Anwender noch größer.
Ui. Habt ihr so breite Tabellen? Man braucht nicht für jede Feldkombination einen zusammengesetzten Index, Firebird kombiniert die einzelnen, wenn möglich.
Einzig beim ETL müssen die Indizes wieder deaktiviert werden, denn statt 2-3000 Inserts/Sekunde passieren bei 100 Indizes nur noch 3-10 Inserts/Sekunde.
Logisch. Habt ihr mal das batch-API von Firebird ausprobiert? Um Daten aus Drittsystemen ins DWH zu saugen. Und hier wäre die parallele Indexaktivierung von Firebird 5 interessant.
Wenn man dann mit Power-BI daher kommt und z.T. unqualifizierte Abfragen gegen eine DB macht, kann das sogar Nachteile einer laufenden ERP-Anwendung bedeuten, da eben häufig per Tablescan alles gelesen werden muss. Beim Microsoft-SQL-Sever führt das im Zweifel sogar zu einer Tabellensperre (Lock-Escalation), die das gesamte ERP lahmlegt bis der Ladeprozess abgeschlossen ist.
Das Problem hat Firebird nicht.
Queryzeiten:
Bei einer komplexen Aggregat-Abfrage über eine Tabelle mit 1. Mio. Zeilen benötigt die FB auf einem I7 mit 20CPU's, 64GB Speicher, 2,6GHz und SSD ca. 70 Sekunden.
Diese Abfrage wird in verschiedenen Varianten 3 Mal geladen.
Dies macht 210 Sekunden.
Mache ich diese Abfragen über parallele Connections, benötigt jede Abfrage ca. 135 Sekunden. Durch die Parallelität bleibt es aber bei 135 zu 210 Sekunden eben besser.
Die Frage ist nun, warum 1 Abfrage über 1 Tabelle einzeln 70 und parallel 135 Sekunden dauert.
Die Abfrage würde ich gerne mal sehen, das kommt mir sehr hoch vor bei der HW, die Du nennst.

Re: Firebird 5 geht in den Beta-Test

Verfasst: Fr 5. Mai 2023, 08:56
von bfuerchau
Die Probleme der Datenqualität sind bei allen BI-Tools dieselben. Daher sorgen wir bei unserem ETL dafür, dass auf diese geachtet wird.
Unser Self-Service-BI hat daher eine andere Qualität.

Der SQL-Ist relativ simpel: :D
Ca. 10 - 30 Tabellen verjoint via Derived Tables, alle mit Primary Key, eine Where-Klausel beliebiger Tiefe, teilweise Security-Where auf den einzelnen Tabellen.
Je nach Anfrage zwischen 5 und 30 Group By Feldern sowie 5 - 30 oder mehr Aggregate;-). Das Ergebnis sind dann Resultsets mit bis zu 1 Mio Zeilen.
Einen Key für Group By habe ich auch sein gelassen, da der Key für Where effektiver ist.
Das mit den 1-Feld-Schlüsseln habe ich aufgegeben, da diese bei komplexen Where-Bedingungen nicht schneller als ein Table-Scan sind.

Das Batch-API können wir nicht nutzen, da wir auch hier die Daten nicht einfach nur hochladen sondern auch per Multi-Feld-Primary Key Update/Inserts betreiben, was jedoch mit unserem BulkLoad (mehrere parametrierte Update or Inserts in einem Execute Block) kein Problem darstellt.

Bei Interesse können wir das gerne präsentieren. :D

Also falls es interessiert hier der SQL.
Queryzeit parallel, also bis zum 1. Satz 197 Sekunden, Downloadzeit 85 Sekunden, Result 675.490 Zeilen.
Queryzeit einzeln 51 Sekunden, Downloadzeit 48 Sekunden.

SQL:
SELECT COALESCE( T89_LEVEL1, '') AS F56, COALESCE( T89_LEVEL13, '') AS F102, COALESCE( T89_LEVEL9, '') AS F104, COALESCE( T89_LEVEL11, '') AS F106, T88_LEVEL8 AS F48, COALESCE( T89_LEVEL3, '') AS F58, T88_LEVEL6 AS F49, COALESCE( T89_LEVEL4, '') AS F60, T88_LEVEL7 AS F75, T88_INFO3 AS F100, COALESCE( T91_LEVEL18, '') AS F61, COALESCE( T91_LEVEL25, '') AS F63, COALESCE( T91_LEVEL19, '') AS F64, COALESCE( T91_LEVEL28, '') AS F117, COALESCE( T91_LEVEL27, '') AS F65, T88_LEVEL5 AS F50, COALESCE( T91_LEVEL32, '') AS F121, COALESCE( T91_LEVEL22, '') AS F67, COALESCE( T89_LEVEL14, '') AS F110, T88_LEVEL2 AS F51, T88_LEVELDATE AS F47, T88_LEVEL0 AS F52, T88_LEVEL10 AS F83, T88_LEVEL11 AS F84, T88_LEVEL12 AS F85, SUM(T88_VALUE30) AS F53, SUM(T88_VALUE29) AS F54, SUM(T88_VALUE35) AS F55, COUNT('*') AS "##RowCount", MIN(F48T) AS F48T, MIN(F49T) AS F49T, MIN(F75T) AS F75T, MIN(F50T) AS F50T, MIN(F51T) AS F51T, MIN(F83T) AS F83T, MIN(F84T) AS F84T, MIN(F85T) AS F85T, MIN(F102T) AS F102T, MIN(F58T) AS F58T, MIN(F60T) AS F60T, MIN(F110T) AS F110T, MIN(F121T) AS F121T, MIN(F67T) AS F67T FROM (SELECT COALESCE(XT117_TEXT, '') AS F48T, COALESCE(XT118_TEXT, '') AS F49T, COALESCE(XT119_TEXT, '') AS F75T, COALESCE(XT120_TEXT, '') AS F50T, COALESCE(XT121_TEXT, '') AS F51T, COALESCE(XT122_TEXT, '') AS F83T, COALESCE(XT123_TEXT, '') AS F84T, COALESCE(XT124_TEXT, '') AS F85T, COALESCE(XT125_TEXT, '') AS F102T, COALESCE(XT126_TEXT, '') AS F58T, COALESCE(XT127_TEXT, '') AS F60T, COALESCE(XT128_TEXT, '') AS F110T, COALESCE(XT129_TEXT, '') AS F121T, COALESCE(XT130_TEXT, '') AS F67T, T88.*, T89.*, T91.* FROM (SELECT T88.LEVEL0 AS T88_LEVEL0, T88.LEVEL1 AS T88_LEVEL1, T88.LEVEL2 AS T88_LEVEL2, T88.LEVEL3 AS T88_LEVEL3, T88.LEVEL16 AS T88_LEVEL16, T88.LEVEL17 AS T88_LEVEL17, T88.LEVEL18 AS T88_LEVEL18, T88.LEVEL15 AS T88_LEVEL15, T88.LEVEL4 AS T88_LEVEL4, T88.LEVEL5 AS T88_LEVEL5, T88.LEVEL6 AS T88_LEVEL6, T88.LEVEL7 AS T88_LEVEL7, T88.LEVEL8 AS T88_LEVEL8, T88.VALUE29 AS T88_VALUE29, T88.VALUE30 AS T88_VALUE30, T88.VALUE28 AS T88_VALUE28, T88.LEVEL9 AS T88_LEVEL9, T88.VALUE31 AS T88_VALUE31, T88.VALUE27 AS T88_VALUE27, T88.VALUE32 AS T88_VALUE32, T88.VALUE33 AS T88_VALUE33, T88.VALUE34 AS T88_VALUE34, T88.VALUE35 AS T88_VALUE35, T88.VALUE19 AS T88_VALUE19, T88.LEVEL19 AS T88_LEVEL19, T88.LEVEL10 AS T88_LEVEL10, T88.LEVEL11 AS T88_LEVEL11, T88.LEVEL12 AS T88_LEVEL12, T88.INFO0 AS T88_INFO0, T88.INFO1 AS T88_INFO1, T88.LEVEL13 AS T88_LEVEL13, T88.INFO2 AS T88_INFO2, T88.LEVEL14 AS T88_LEVEL14, T88.INFO3 AS T88_INFO3, T88.LEVELDATE AS T88_LEVELDATE, T88.LEVELWEEK AS T88_LEVELWEEK, T88.LEVELMONTH AS T88_LEVELMONTH, T88.LEVELYEAR AS T88_LEVELYEAR, T88.INFO4 AS T88_INFO4, T88.INFO5 AS T88_INFO5, T88.VALUE5 AS T88_VALUE5, T88.VALUE6 AS T88_VALUE6, T88.VALUE17 AS T88_VALUE17, T88.VALUE0 AS T88_VALUE0, T88.VALUE18 AS T88_VALUE18, T88.VALUE1 AS T88_VALUE1, T88.VALUE2 AS T88_VALUE2, T88.VALUE3 AS T88_VALUE3, T88.VALUE4 AS T88_VALUE4, T88.IMPORTID AS T88_IMPORTID FROM FTISDATA000162 T88) T88 LEFT JOIN (SELECT T89.LEVEL0 AS T89_LEVEL0, T89.LEVEL1 AS T89_LEVEL1, T89.LEVEL2 AS T89_LEVEL2, T89.LEVEL3 AS T89_LEVEL3, T89.LEVEL4 AS T89_LEVEL4, T89.LEVEL5 AS T89_LEVEL5, T89.LEVEL6 AS T89_LEVEL6, T89.LEVEL7 AS T89_LEVEL7, T89.LEVEL8 AS T89_LEVEL8, T89.LEVEL9 AS T89_LEVEL9, T89.VALUE0 AS T89_VALUE0, T89.VALUE1 AS T89_VALUE1, T89.VALUE2 AS T89_VALUE2, T89.LEVEL10 AS T89_LEVEL10, T89.LEVEL11 AS T89_LEVEL11, T89.LEVEL12 AS T89_LEVEL12, T89.LEVEL13 AS T89_LEVEL13, T89.LEVEL14 AS T89_LEVEL14, T89.VALUE3 AS T89_VALUE3, T89.LEVEL15 AS T89_LEVEL15, T89.IMPORTID AS T89_IMPORTID FROM FTISDATA000168 T89) T89 ON T88_LEVEL6 = T89_LEVEL0 LEFT JOIN (SELECT T91.LEVEL21 AS T91_LEVEL21, T91.LEVEL18 AS T91_LEVEL18, T91.LEVEL23 AS T91_LEVEL23, T91.LEVEL24 AS T91_LEVEL24, T91.LEVEL25 AS T91_LEVEL25, T91.LEVEL26 AS T91_LEVEL26, T91.LEVEL27 AS T91_LEVEL27, T91.LEVEL28 AS T91_LEVEL28, T91.LEVEL22 AS T91_LEVEL22, T91.LEVEL20 AS T91_LEVEL20, T91.LEVEL29 AS T91_LEVEL29, T91.LEVEL30 AS T91_LEVEL30, T91.LEVEL16 AS T91_LEVEL16, T91.LEVEL32 AS T91_LEVEL32, T91.LEVEL37 AS T91_LEVEL37, T91.LEVEL31 AS T91_LEVEL31, T91.LEVEL19 AS T91_LEVEL19, T91.LEVEL38 AS T91_LEVEL38, T91.LEVEL39 AS T91_LEVEL39, T91.LEVEL17 AS T91_LEVEL17, T91.LEVEL40 AS T91_LEVEL40, T91.IMPORTID AS T91_IMPORTID FROM FTISDATA000212 T91) T91 ON T88_LEVEL5 = T91_LEVEL21 LEFT JOIN (SELECT XT117.LEVEL1 AS XT117_TEXT, XT117.LEVEL0 AS XT117_LEVEL0 FROM FTISDATA000004 XT117) XT117 ON XT117_LEVEL0 = T88_LEVEL8 LEFT JOIN (SELECT XT118.LEVEL1 AS XT118_TEXT, XT118.LEVEL0 AS XT118_LEVEL0 FROM FTISDATA000010 XT118) XT118 ON XT118_LEVEL0 = T88_LEVEL6 LEFT JOIN (SELECT XT119.LEVEL0 AS XT119_TEXT, XT119.LEVEL2 AS XT119_LEVEL2 FROM FTISDATA000061 XT119) XT119 ON XT119_LEVEL2 = T88_LEVEL7 LEFT JOIN (SELECT XT120.LEVEL1 AS XT120_TEXT, XT120.LEVEL0 AS XT120_LEVEL0 FROM FTISDATA000008 XT120) XT120 ON XT120_LEVEL0 = T88_LEVEL5 LEFT JOIN (SELECT XT121.LEVEL0 AS XT121_TEXT, XT121.LEVEL1 AS XT121_LEVEL1 FROM FTISDATA000006 XT121) XT121 ON XT121_LEVEL1 = T88_LEVEL2 LEFT JOIN (SELECT XT122.LEVEL0 AS XT122_TEXT, XT122.LEVEL1 AS XT122_LEVEL1 FROM FTISDATA000079 XT122) XT122 ON XT122_LEVEL1 = T88_LEVEL10 LEFT JOIN (SELECT XT123.LEVEL0 AS XT123_TEXT, XT123.LEVEL1 AS XT123_LEVEL1 FROM FTISDATA000079 XT123) XT123 ON XT123_LEVEL1 = T88_LEVEL11 LEFT JOIN (SELECT XT124.LEVEL0 AS XT124_TEXT, XT124.LEVEL1 AS XT124_LEVEL1 FROM FTISDATA000079 XT124) XT124 ON XT124_LEVEL1 = T88_LEVEL12 LEFT JOIN (SELECT XT125.LEVEL1 AS XT125_TEXT, XT125.LEVEL0 AS XT125_LEVEL0 FROM FTISDATA000015 XT125) XT125 ON XT125_LEVEL0 = T89_LEVEL13 LEFT JOIN (SELECT XT126.LEVEL0 AS XT126_TEXT, XT126.LEVEL1 AS XT126_LEVEL1 FROM FTISDATA000011 XT126) XT126 ON XT126_LEVEL1 = T89_LEVEL3 LEFT JOIN (SELECT XT127.LEVEL1 AS XT127_TEXT, XT127.LEVEL0 AS XT127_LEVEL0 FROM FTISDATA000014 XT127) XT127 ON XT127_LEVEL0 = T89_LEVEL4 LEFT JOIN (SELECT XT128.LEVEL0 AS XT128_TEXT, XT128.LEVEL1 AS XT128_LEVEL1 FROM FTISDATA000018 XT128) XT128 ON XT128_LEVEL1 = T89_LEVEL14 LEFT JOIN (SELECT XT129.LEVEL1 AS XT129_TEXT, XT129.LEVEL0 AS XT129_LEVEL0 FROM FTISDATA000008 XT129) XT129 ON XT129_LEVEL0 = T91_LEVEL32 LEFT JOIN (SELECT XT130.LEVEL0 AS XT130_TEXT, XT130.LEVEL1 AS XT130_LEVEL1 FROM FTISDATA000021 XT130) XT130 ON XT130_LEVEL1 = T91_LEVEL22) T88 GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25

Plan:
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (T88 T88 T88 NATURAL, T88 T89 T89 INDEX (PK_FTISDATA000168)), T88 T91 T91 INDEX (PK_FTISDATA000212)), T88 XT117 XT117 INDEX (PK_FTISDATA000004)), T88 XT118 XT118 INDEX (PK_FTISDATA000010)), T88 XT119 XT119 INDEX (PK_FTISDATA000061)), T88 XT120 XT120 INDEX (PK_FTISDATA000008)), T88 XT121 XT121 INDEX (PK_FTISDATA000006)), T88 XT122 XT122 INDEX (PK_FTISDATA000079)), T88 XT123 XT123 INDEX (PK_FTISDATA000079)), T88 XT124 XT124 INDEX (PK_FTISDATA000079)), T88 XT125 XT125 INDEX (PK_FTISDATA000015)), T88 XT126 XT126 INDEX (PK_FTISDATA000011)), T88 XT127 XT127 INDEX (PK_FTISDATA000014)), T88 XT128 XT128 INDEX (PK_FTISDATA000018)), T88 XT129 XT129 INDEX (PK_FTISDATA000008)), T88 XT130 XT130 INDEX (PK_FTISDATA000021)))

Re: Firebird 5 geht in den Beta-Test

Verfasst: Fr 5. Mai 2023, 12:45
von martin.koeditz
Einen Key für Group By habe ich auch sein gelassen, da der Key für Where effektiver ist.
Das mit den 1-Feld-Schlüsseln habe ich aufgegeben, da diese bei komplexen Where-Bedingungen nicht schneller als ein Table-Scan sind.
Das ist genau der Fall, den ich oben beschrieben habe. Durch die partiellen Indizes habe ich die Möglichkeit, nur relevante Daten im Speicher vorzuhalten. In anderen Fällen kann der Performance-Verlust durch einen Table-Scan häufig vernachlässigt werden.

Danke für das Beispiel. Sieht in jedem Falle interessant aus. Ich denke, dass man aus dem geschilderten Fall einiges lernen kann.

Mit Download ist die Gesamtausgabe der Daten bis zum letzten Datensatz gemeint, korrekt?

Dank und Gruß
Martin

Re: Firebird 5 geht in den Beta-Test

Verfasst: Fr 5. Mai 2023, 13:10
von bfuerchau
Genau. Ich starte die Messung beim ExecuteReader und gebe dann vor dem ersten Read die Zeit aus.
Für das Lesen in meine In-Memory-DB starte ich die Zeit neu und gebe sie nach der letzten Zeile aus.
Meine In-Memory-DB, die auch als Cache dient, legt 2 zusätzliche Globale Objekte an:
a) ein Dictionary<Object, Int> zur Verwaltung eines Mappings Object->Index
b) ein List<Object> der geladenen divesen Objekte.
Jeder Zellwert wird im Dictionary gesucht und als Ergebnis der Int-Wert ausgelesen. Ist der Wert nicht vorhanden, wird dieser an die Liste drangehängt. Der ListIndex ergibt nun den neuen Wert für das Dictionary in dem das Object jetzt gespeichert wird.
Dadurch speichere ich in einer Collection nur Arrays int[n], was weniger Platz bedeutet. Die Liste wird in einer Datei gespeichert, so dass beim nächsten Programmstart diese nicht mühsam aufgebaut werden muss, da dies die meiste Zeit kostet.
Die Collection ist eine ITypedList, so dass alle Elemente (ASPx, WinForms) die Daten per PropertyDescriptor auslesen und die Getter-Funktion den tatsächlichen Wert aus der Liste über den Index zurück gibt.
Dies ist sehr speichereffektiv und auch schnell. Z.B. kann ein Grid aus 1 Mio Zeilen mit 30 Spalten und einem Filter über 5 Felder innerhalb von < 3 Sekunden 200.000 Zeilen als Ergebnis filtern.
Für BI-Auswertungen ideal, da ich dann die 200.000 Zeilen nicht wieder aus der DB laden muss.

Fazit: Z.B. bei einer Sammlung von 29 Abfragen mit 4,5 Millionen Zeilen und ca. 10-30 Spalten werden ca. 750MB Speicher belegt, das entspricht ca. 188 Millionen INT-Werten, aber in der MappingTabelle stehen nur 854.000 unterschiedliche Werte (Strings, Zahlen, DateTime).
Daran kann man mal statistisch sehen, mit wie wenigen Werten man insgesamt auskommt.

Re: Firebird 5 geht in den Beta-Test

Verfasst: Sa 13. Mai 2023, 03:26
von vr2
bfuerchau hat geschrieben: Fr 5. Mai 2023, 08:56 Der SQL-Ist relativ simpel: :D
Das stimmt. Innen eine Basismenge, an die 16 Mengen left drangejoint werden für die Spaltenverteilung und außenrum wird aggregiert. Hätteste ruhig mal formatieren können :D

Code: Alles auswählen

SELECT COALESCE(T89_LEVEL1, '') AS F56, COALESCE(T89_LEVEL13, '') AS F102, COALESCE(T89_LEVEL9, '') AS F104, COALESCE(T89_LEVEL11, '') AS F106, T88_LEVEL8 AS F48, COALESCE(T89_LEVEL3, '') AS F58, T88_LEVEL6 AS F49, COALESCE(T89_LEVEL4, '') AS F60, T88_LEVEL7 AS F75, T88_INFO3 AS F100, COALESCE(T91_LEVEL18, '') AS F61, COALESCE(T91_LEVEL25, '') AS F63, COALESCE(T91_LEVEL19, '') AS F64, COALESCE(T91_LEVEL28, '') AS F117, COALESCE(T91_LEVEL27, '') AS F65, T88_LEVEL5 AS F50, COALESCE(T91_LEVEL32, '') AS F121, COALESCE(T91_LEVEL22, '') AS F67, COALESCE(T89_LEVEL14, '') AS F110, T88_LEVEL2 AS F51, T88_LEVELDATE AS F47, T88_LEVEL0 AS F52, T88_LEVEL10 AS F83, T88_LEVEL11 AS F84, T88_LEVEL12 AS F85, SUM(T88_VALUE30) AS F53, SUM(T88_VALUE29) AS F54, SUM(T88_VALUE35) AS F55, COUNT('*') AS "##RowCount", MIN(F48T) AS F48T, MIN(F49T) AS F49T, MIN(F75T) AS F75T, MIN(F50T) AS F50T, MIN(F51T) AS F51T, MIN(F83T) AS F83T, MIN(F84T) AS F84T, MIN(F85T) AS F85T, MIN(F102T) AS F102T, MIN(F58T) AS F58T, MIN(F60T) AS F60T, MIN(F110T) AS F110T, MIN(F121T) AS F121T, MIN(F67T) AS F67T
FROM (
  SELECT COALESCE(XT117_TEXT, '') AS F48T, COALESCE(XT118_TEXT, '') AS F49T, COALESCE(XT119_TEXT, '') AS F75T, COALESCE(XT120_TEXT, '') AS F50T, COALESCE(XT121_TEXT, '') AS F51T, COALESCE(XT122_TEXT, '') AS F83T, COALESCE(XT123_TEXT, '') AS F84T, COALESCE(XT124_TEXT, '') AS F85T, COALESCE(XT125_TEXT, '') AS F102T, COALESCE(XT126_TEXT, '') AS F58T, COALESCE(XT127_TEXT, '') AS F60T, COALESCE(XT128_TEXT, '') AS F110T, COALESCE(XT129_TEXT, '') AS F121T, COALESCE(XT130_TEXT, '') AS F67T, T88.*, T89.*, T91.*
  FROM (
    SELECT T88.LEVEL0 AS T88_LEVEL0, T88.LEVEL1 AS T88_LEVEL1, T88.LEVEL2 AS T88_LEVEL2, T88.LEVEL3 AS T88_LEVEL3, T88.LEVEL16 AS T88_LEVEL16, T88.LEVEL17 AS T88_LEVEL17, T88.LEVEL18 AS T88_LEVEL18, T88.LEVEL15 AS T88_LEVEL15, T88.LEVEL4 AS T88_LEVEL4, T88.LEVEL5 AS T88_LEVEL5, T88.LEVEL6 AS T88_LEVEL6, T88.LEVEL7 AS T88_LEVEL7, T88.LEVEL8 AS T88_LEVEL8, T88.VALUE29 AS T88_VALUE29, T88.VALUE30 AS T88_VALUE30, T88.VALUE28 AS T88_VALUE28, T88.LEVEL9 AS T88_LEVEL9, T88.VALUE31 AS T88_VALUE31, T88.VALUE27 AS T88_VALUE27, T88.VALUE32 AS T88_VALUE32, T88.VALUE33 AS T88_VALUE33, T88.VALUE34 AS T88_VALUE34, T88.VALUE35 AS T88_VALUE35, T88.VALUE19 AS T88_VALUE19, T88.LEVEL19 AS T88_LEVEL19, T88.LEVEL10 AS T88_LEVEL10, T88.LEVEL11 AS T88_LEVEL11, T88.LEVEL12 AS T88_LEVEL12, T88.INFO0 AS T88_INFO0, T88.INFO1 AS T88_INFO1, T88.LEVEL13 AS T88_LEVEL13, T88.INFO2 AS T88_INFO2, T88.LEVEL14 AS T88_LEVEL14, T88.INFO3 AS T88_INFO3, T88.LEVELDATE AS T88_LEVELDATE, T88.LEVELWEEK AS T88_LEVELWEEK, T88.LEVELMONTH AS T88_LEVELMONTH, T88.LEVELYEAR AS T88_LEVELYEAR, T88.INFO4 AS T88_INFO4, T88.INFO5 AS T88_INFO5, T88.VALUE5 AS T88_VALUE5, T88.VALUE6 AS T88_VALUE6, T88.VALUE17 AS T88_VALUE17, T88.VALUE0 AS T88_VALUE0, T88.VALUE18 AS T88_VALUE18, T88.VALUE1 AS T88_VALUE1, T88.VALUE2 AS T88_VALUE2, T88.VALUE3 AS T88_VALUE3, T88.VALUE4 AS T88_VALUE4, T88.IMPORTID AS T88_IMPORTID
    FROM FTISDATA000162 T88) T88
  LEFT JOIN (SELECT T89.LEVEL0 AS T89_LEVEL0, T89.LEVEL1 AS T89_LEVEL1, T89.LEVEL2 AS T89_LEVEL2, T89.LEVEL3 AS T89_LEVEL3, T89.LEVEL4 AS T89_LEVEL4, T89.LEVEL5 AS T89_LEVEL5, T89.LEVEL6 AS T89_LEVEL6, T89.LEVEL7 AS T89_LEVEL7, T89.LEVEL8 AS T89_LEVEL8, T89.LEVEL9 AS T89_LEVEL9, T89.VALUE0 AS T89_VALUE0, T89.VALUE1 AS T89_VALUE1, T89.VALUE2 AS T89_VALUE2, T89.LEVEL10 AS T89_LEVEL10, T89.LEVEL11 AS T89_LEVEL11, T89.LEVEL12 AS T89_LEVEL12, T89.LEVEL13 AS T89_LEVEL13, T89.LEVEL14 AS T89_LEVEL14, T89.VALUE3 AS T89_VALUE3, T89.LEVEL15 AS T89_LEVEL15, T89.IMPORTID AS T89_IMPORTID
             FROM FTISDATA000168 T89) T89 ON T88_LEVEL6 = T89_LEVEL0
  LEFT JOIN (SELECT T91.LEVEL21 AS T91_LEVEL21, T91.LEVEL18 AS T91_LEVEL18, T91.LEVEL23 AS T91_LEVEL23, T91.LEVEL24 AS T91_LEVEL24, T91.LEVEL25 AS T91_LEVEL25, T91.LEVEL26 AS T91_LEVEL26, T91.LEVEL27 AS T91_LEVEL27, T91.LEVEL28 AS T91_LEVEL28, T91.LEVEL22 AS T91_LEVEL22, T91.LEVEL20 AS T91_LEVEL20, T91.LEVEL29 AS T91_LEVEL29, T91.LEVEL30 AS T91_LEVEL30, T91.LEVEL16 AS T91_LEVEL16, T91.LEVEL32 AS T91_LEVEL32, T91.LEVEL37 AS T91_LEVEL37, T91.LEVEL31 AS T91_LEVEL31, T91.LEVEL19 AS T91_LEVEL19, T91.LEVEL38 AS T91_LEVEL38, T91.LEVEL39 AS T91_LEVEL39, T91.LEVEL17 AS T91_LEVEL17, T91.LEVEL40 AS T91_LEVEL40, T91.IMPORTID AS T91_IMPORTID
             FROM FTISDATA000212 T91) T91 ON T88_LEVEL5 = T91_LEVEL21
  LEFT JOIN (SELECT XT117.LEVEL1 AS XT117_TEXT, XT117.LEVEL0 AS XT117_LEVEL0 FROM FTISDATA000004 XT117) XT117 ON XT117_LEVEL0 = T88_LEVEL8
  LEFT JOIN (SELECT XT118.LEVEL1 AS XT118_TEXT, XT118.LEVEL0 AS XT118_LEVEL0 FROM FTISDATA000010 XT118) XT118 ON XT118_LEVEL0 = T88_LEVEL6
  LEFT JOIN (SELECT XT119.LEVEL0 AS XT119_TEXT, XT119.LEVEL2 AS XT119_LEVEL2 FROM FTISDATA000061 XT119) XT119 ON XT119_LEVEL2 = T88_LEVEL7
  LEFT JOIN (SELECT XT120.LEVEL1 AS XT120_TEXT, XT120.LEVEL0 AS XT120_LEVEL0 FROM FTISDATA000008 XT120) XT120 ON XT120_LEVEL0 = T88_LEVEL5
  LEFT JOIN (SELECT XT121.LEVEL0 AS XT121_TEXT, XT121.LEVEL1 AS XT121_LEVEL1 FROM FTISDATA000006 XT121) XT121 ON XT121_LEVEL1 = T88_LEVEL2
  LEFT JOIN (SELECT XT122.LEVEL0 AS XT122_TEXT, XT122.LEVEL1 AS XT122_LEVEL1 FROM FTISDATA000079 XT122) XT122 ON XT122_LEVEL1 = T88_LEVEL10
  LEFT JOIN (SELECT XT123.LEVEL0 AS XT123_TEXT, XT123.LEVEL1 AS XT123_LEVEL1 FROM FTISDATA000079 XT123) XT123 ON XT123_LEVEL1 = T88_LEVEL11
  LEFT JOIN (SELECT XT124.LEVEL0 AS XT124_TEXT, XT124.LEVEL1 AS XT124_LEVEL1 FROM FTISDATA000079 XT124) XT124 ON XT124_LEVEL1 = T88_LEVEL12
  LEFT JOIN (SELECT XT125.LEVEL1 AS XT125_TEXT, XT125.LEVEL0 AS XT125_LEVEL0 FROM FTISDATA000015 XT125) XT125 ON XT125_LEVEL0 = T89_LEVEL13
  LEFT JOIN (SELECT XT126.LEVEL0 AS XT126_TEXT, XT126.LEVEL1 AS XT126_LEVEL1 FROM FTISDATA000011 XT126) XT126 ON XT126_LEVEL1 = T89_LEVEL3
  LEFT JOIN (SELECT XT127.LEVEL1 AS XT127_TEXT, XT127.LEVEL0 AS XT127_LEVEL0 FROM FTISDATA000014 XT127) XT127 ON XT127_LEVEL0 = T89_LEVEL4
  LEFT JOIN (SELECT XT128.LEVEL0 AS XT128_TEXT, XT128.LEVEL1 AS XT128_LEVEL1 FROM FTISDATA000018 XT128) XT128 ON XT128_LEVEL1 = T89_LEVEL14
  LEFT JOIN (SELECT XT129.LEVEL1 AS XT129_TEXT, XT129.LEVEL0 AS XT129_LEVEL0 FROM FTISDATA000008 XT129) XT129 ON XT129_LEVEL0 = T91_LEVEL32
  LEFT JOIN (SELECT XT130.LEVEL0 AS XT130_TEXT, XT130.LEVEL1 AS XT130_LEVEL1 FROM FTISDATA000021 XT130) XT130 ON XT130_LEVEL1 = T91_LEVEL22) T88
GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25



PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (T88 T88 T88 NATURAL, T88 T89 T89 INDEX (PK_FTISDATA000168)), 
T88 T91 T91 INDEX (PK_FTISDATA000212)), T88 XT117 XT117 INDEX (PK_FTISDATA000004)), T88 XT118 XT118 INDEX (PK_FTISDATA000010)), T88 XT119 XT119 INDEX (PK_FTISDATA000061)), 
T88 XT120 XT120 INDEX (PK_FTISDATA000008)), T88 XT121 XT121 INDEX (PK_FTISDATA000006)), T88 XT122 XT122 INDEX (PK_FTISDATA000079)), T88 XT123 XT123 INDEX (PK_FTISDATA000079)), 
T88 XT124 XT124 INDEX (PK_FTISDATA000079)), T88 XT125 XT125 INDEX (PK_FTISDATA000015)), T88 XT126 XT126 INDEX (PK_FTISDATA000011)), T88 XT127 XT127 INDEX (PK_FTISDATA000014)), 
T88 XT128 XT128 INDEX (PK_FTISDATA000018)), T88 XT129 XT129 INDEX (PK_FTISDATA000008)), T88 XT130 XT130 INDEX (PK_FTISDATA000021)))
Also falls es interessiert hier der SQL.
Queryzeit parallel, also bis zum 1. Satz 197 Sekunden, Downloadzeit 85 Sekunden, Result 675.490 Zeilen.
Queryzeit einzeln 51 Sekunden, Downloadzeit 48 Sekunden.
Der Plan sieht eigentlich gut aus. Meinst Du mit den Zeiten 51 sek execute und 48 sek fetch für die gezeigte Abfrage? Martin hat es schon gefragt, aber die DL-Zeit ist kleiner als die Queryzeit, dann kann sie nicht die Gesamtzeit sein.

Was war der Grund, die XT<nnn>-joins in ein select zu verpacken? Einfachere Generierbarkeit?

Code: Alles auswählen

-- warum
LEFT JOIN (SELECT XT117.LEVEL1 AS XT117_TEXT, XT117.LEVEL0 AS XT117_LEVEL0 FROM FTISDATA000004 XT117) XT117 ON XT117_LEVEL0 = T88_LEVEL8
-- und nicht 
LEFT JOIN FTISDATA000004 XT117 ON XT117.LEVEL0 = T88_LEVEL8

Re: Firebird 5 geht in den Beta-Test

Verfasst: Sa 13. Mai 2023, 12:00
von bfuerchau
Es ist eine klassische Abfrage unseres DWH's.
Es gibt Bewegungsdaten, Stammdaten sowie Texte.
Um das Datenvolumen zu reduzieren, mit dem Charts und Grids so umgehen, wird eben per Left Join alles dazugelinkt, was per Database Model definiert ist. Left join daher, dass es neben Stammdaten auch sog. Enrichment Daten gibt, wo nicht zu jeder Bewegungssatz einen Enrichmentsatz hat.
Die XT-Joins sind dann diese Texte zu Dimensionen wie Artikelnummer, Kundennummer, Produktgruppen u.v.m. Zu jeder Dimension kann es Textdaten geben und nicht zu jedem Dimensionsschlüssel, vor allem Leerzeichen, gibt es einen Text. Nun sind diese Textdaten aber z.T. eben größer als die Dimensionen der gerade abgefragten Daten.
Dimensionen werden grundsätzlich mit Coalesce abgefragt, da diese eben aus einer Jointabelle kommen kann.
Es gibt eine Optimierung, dass u.U. aus dem Left ein Inner Join wird da ggf. die Whereklausel immer ein Ergebnis bringen muss.
Was die Zeiten angeht, meine ich es geschrieben zu haben:
- Queryzeit: Zeit vom Start der Abfrage bis zum Laden des 1. Datensatzes
- Downloadzeit: vom 1. bis zum letzten Datensatz
Beides zusammen ergibt dann halt die benötigte Zeit bis das Dashboard die Daten berechnen und anzeigen kann.

Ja, und breite Tabellen gibt es tatsächlich, da sowohl Stammdaten als auch Bewegungsdaten aus ERP-Systemen eine vielzahl von Spalten haben mit den unterschiedllichsten Informationen.
Nur bei der Microsoft Northwind DB habe ich ein komplexes, ID-orientiertes Modell gesehen, dass allerdings eher nur rudimentär als ERP eingesetzt werden könnte.

Wenn man sich z.B. Dashboards (auch bei Power-BI) so ansieht, hat man ja nicht nur die Listen und Charts sondern was ebenso wichtig ist, viele Filtermöglichkeiten.
Z.B. nach Artikeldimensionen die von der Nummer, über Klassifizierungen bis hin zu Kontoinformation (Buchhaltung gehen). Für Kunden, Lieferanten, Vertreter u.v.m. gibts das jeweils ebenso.
Um aber nicht ständig die Daten aus der DB nachladen zu müssen, lädt ein Query alle benötigten Informationen in eine In-Memory-DB in eine ziehmlich breite Tabelle.