Filtern von numerischen Werten <> NaN

[bfuerchau]

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]

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]

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]

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]

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]

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]

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]

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]

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]

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