Filtern von numerischen Werten <> NaN

Themen rund um den praktischen Einsatz von Firebird. Fragen zu SQL, Performance, Datenbankstrukturen, etc.

Moderator: thorben.braun

bfuerchau
Beiträge: 545
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Sind ja vergleichbare Ergebnisse zu meinen.
Interessant ist ja nur, dass dieser Ausdruck im Aggregat und nicht im Where ist und du somit mehrere Aggregate parallel verarbeiten kannst.
Zusätzlich, wenn value NULL ist, liefert der gesamte Case ebenfalls NULL und die werden ja eh ausgeschlossen.
Und das Ganze ohne UDR.
vr2
Beiträge: 244
Registriert: Fr 13. Apr 2018, 00:13

Es kommt drauf an, was man priorisiert, Durchsatz oder Flexibilität in den Aggregaten oder minimale Komplexität/Wartungsaufwand. In diesem Fall gehen die Aggregate 1. alle auf die selbe Spalte (value_double), und 2. ist die Verteilung der Werte, wenn man den Screenshot zugrunde legt, gut 1/3 NaN. Wenn die Filterbedingung statt im where-Part in den Aggregatausdrücken steckt, muss jedes Aggregat die gleiche Filterung nochmal machen, und über die gesamte Datenmenge. Der Ansatz kam deshalb auf 560ms/140K, braucht 5.6 mal so lang wie das Original und der UDR/Blob-Ansatz und 2.5 mal so lang wie der Ansatz mit NaN-Literal im where-Part. Platz 3 bzgl Durchsatz, Platz 2 bzgl Komplexität - wegen der bezogen auf die Fragestellung unnötigen Wiederholung der Filterbedingung in den Aggregaten.

Das ungefilterte Ausgangsstatement

Code: Alles auswählen

SELECT min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
kam auf 100ms/140K Sätze, vom Durchsatz her die Messlatte, aber mit falschen Ergebnissen. Die Version mit Blobzerlegung und Aggregatberechnung in der UDR kam auf den gleichen guten Durchsatz, mit richtigen Ergebnissen, aber deutlich höherer Komplexität. Platz 1 bzgl Durchsatz, Platz 3 bzgl Komplexität.

Der Test auf NaN-Literal

Code: Alles auswählen

SELECT min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
where CAST(VALUE_DOUBLE as VARCHAR(20)) <> 'nan(ind)'
kam auf 220ms/140K, braucht 2.2 mal so lang wie das Original und UDR/Blob-Ansatz, aber ohne Erhöhung von Komplexität. Platz 2 bzgl Durchsatz, Platz 1 bzgl Komplexität, weniger als diese eine Bedingung geht nicht, wenn man korrekte Ergebnisse haben will.

Ulrich, wenn Dich der Anteil der NaN-Werte nicht interessiert, könntest Du sie auch mit einem before-insert-Trigger frühzeitig rauswinken oder null setzen, ein Kriterium hast Du ja jetzt. Das ist dann zwar auch geringfügig komplexer (kein Vergleich zur Blob-UDR), aber die Auswertung wird wegen der reduzierten Datenmenge noch schneller sein als das Original oder (falls null setzen) wegen der nicht mehr nötigen Filterung genauso schnell, und ohne UDR oder Filterbedingung korrekte Ergebnisse liefern.

Grüße, Volker
Groffy
Beiträge: 80
Registriert: Do 12. Apr 2018, 23:14

Moin Volker,

danke für Deine Analyse und Zusammenfassung. Ich bin bei dem Thema noch dabei mögliche Optionen zu erarbeiten und dann mal zu schauen was vom Aufwand Sinn macht. Ich hab zwar Spaß daran herauszufinden, was ich alles mit UDRs machen kann (kann ich endlich mal wieder in Object Pascal programmieren) und was ich doch lieber Clientenseitig (c#) mache. Die Designentscheidung bestimmte Werte als double.NaN in die Datenbank abzulegen ist vor Jahren getroffen worden, dass evtl. NULL die bessere Option gewesen wäre - möglich.

VG Ulrich
bfuerchau
Beiträge: 545
Registriert: Mo 7. Mai 2018, 18:09
Kontaktdaten:

Bzgl. der UDR würde ich dann eher ein NanToNull() schreiben.
Das hat den Vorteil, dass

where not NanToNull(value) is Null

oder das Aggregat(NanToNull(value)) verwendet werden kann.
Die Udr macht dann nur einen
return Value == Double.Nan ? null : value;

Ich glaube, schneller gehts nicht mehr.

Was die Performance angeht so ist das nun generell so, dass Where-Prüfungen immer schneller sind als die anschließende Aggregierung. Auch muss man wissen, ob man halt auch andere Felder nebenläufig benötigt. Dann kann halt eine NanToNull im Aggregat effektiver sein, da man die whereklausel für was anderes braucht.
Groffy
Beiträge: 80
Registriert: Do 12. Apr 2018, 23:14

bfuerchau hat geschrieben: Fr 20. Okt 2023, 16:14 Bzgl. der UDR würde ich dann eher ein NanToNull() schreiben.
Das hat den Vorteil, dass

where not NanToNull(value) is Null

oder das Aggregat(NanToNull(value)) verwendet werden kann.
Die Udr macht dann nur einen
return Value == Double.Nan ? null : value;

Ich glaube, schneller gehts nicht mehr.
Hallo,

vielen Dank für den Vorschlag, werde ich probieren und hier berichten.

Gruß Ulrich
Groffy
Beiträge: 80
Registriert: Do 12. Apr 2018, 23:14

Erste Ergebnisse:

Code: Alles auswählen

select min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
from results
where not nan2null(VALUE_DOUBLE) is null
erziehlt mit ca. 330ms/140k Werten keine wesentliche Verbesserung.

Ich denke der Overhead zum Aufruf der UDR ist hier der Hauptfaktor.

Gruß Ulrich
vr2
Beiträge: 244
Registriert: Fr 13. Apr 2018, 00:13

Ulrich, ich hätte noch zwei Vorschläge:

1. Kannst Du den NaN-Tester mal testweise in eine udf packen? Die sind zwar deprecated, haben aber nicht den Overhead. udfs gehen auch bei Firebird 5 noch, Du müsstest dafür firebird.conf so konfigurieren:

Code: Alles auswählen

#UdfAccess = None
UdfAccess = Restrict UDF
Falls das ähnlich gut performt wie das original-Statement, wäre das ein überzeugendes Argument und ein praxisnaher test case, um einen Feature Request bzgl UDR-Overhead einzureichen. NaN in Aggregaten sind ein Grenzfall, aber da die Unterscheidung gemacht wird, muss es auch einfache, universelle Mechanismen zur performanten Auswertung geben.

2. Falls Du die Struktur der Messwerttabelle erweitern kannst, hättest Du noch die Möglichkeit, die Messwerte in eine weitere Spalte zu denormalisieren, mit einem NaNToNull-Trigger, und dann diese Spalte auszuwerten. Oder gleich in eine separate Auswertungstabelle filtern. Beides aber Konstrukte, die ihren Preis haben.

Grüße, Volker
Groffy
Beiträge: 80
Registriert: Do 12. Apr 2018, 23:14

vr2 hat geschrieben: So 22. Okt 2023, 04:14 Kannst Du den NaN-Tester mal testweise in eine udf packen? Die sind zwar deprecated, haben aber nicht den Overhead. udfs gehen auch bei Firebird 5 noch, Du müsstest dafür firebird.conf so konfigurieren:

Code: Alles auswählen

#UdfAccess = None
UdfAccess = Restrict UDF
Hallo Volker,

ich denke, die UDFs werden solange beibehalten werden, bis die Firebird Entwickler sich überlegt haben, wie das mit den BlobFiltern weitergehen soll. Meines Wissens können die bislang in der neuen UDR Schnittstelle nicht implementiert werden.

Die Funktionen DoubleIsNaN/NaN2Null implementiere ich heute oder morgen als UDF, dann werde ich berichten.

Gruß Ulrich
Groffy
Beiträge: 80
Registriert: Do 12. Apr 2018, 23:14

Moin Zusammen,

hat tatsächlich etwas gedauert, bis ich mich wieder an die Parameterübergabe Mechanismen von UDFs gewöhnt hatte, einen Boolean als Rückgabewert habe ich nicht hinbekommen, weder by Value noch by Reference.
"data type not supported"
kam als Rückantwort. Statt boolean habe ich dann Integer genommen.

Sowohl

Code: Alles auswählen

select min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
from results
where udf_nan_2_null(VALUE_DOUBLE) is not null
als auch

Code: Alles auswählen

select min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
from results
where udf_double_is_nan(VALUE_DOUBLE) <> 1
sind mit ca. 145ms/140K Werten gleich schnell.

Das heißt, gegenüber einem als UDR implementierten

Code: Alles auswählen

select min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
where not double_is_nan(VALUE_DOUBLE)
mit ca. 360ms/140k Werten um den Faktor 2.5 schneller.


Gruß Ulrich
vr2
Beiträge: 244
Registriert: Fr 13. Apr 2018, 00:13

Groffy hat geschrieben: Mo 23. Okt 2023, 07:06 hat tatsächlich etwas gedauert, bis ich mich wieder an die Parameterübergabe Mechanismen von UDFs gewöhnt hatte, einen Boolean als Rückgabewert habe ich nicht hinbekommen, weder by Value noch by Reference.
"data type not supported"
kam als Rückantwort. Statt boolean habe ich dann Integer genommen.
Versuch mal by descriptor, damit müsste das gehen.
Das heißt, gegenüber einem als UDR implementierten

Code: Alles auswählen

select min(VALUE_DOUBLE), max(VALUE_DOUBLE), avg(VALUE_DOUBLE)
FROM RESULTS
where not double_is_nan(VALUE_DOUBLE)
mit ca. 360ms/140k Werten [ist die udf-Implementierung] um den Faktor 2.5 schneller.
100ms Referenz
145ms udf
360ms UDR

Faktor 2.5 Aufrufoverhead ist deutlich, danke!

Grüße, Volker
Antworten