up

UDFs und Performance

UDFs sind ein tolles Spielzeug und können SQL Statements wesentlich vereinfachen, was ja erst einmal nicht schlecht ist, aber (und ohne aber bräuchte es diesen Artikel nicht) sind diese auch wirklich sinnvoll. Jeder, oder zumindest viele, kennen das Problem von Huddle-Datums-Feldern, sprich: numerische oder gar Alpha Felder in Dateien, die hoffentlich Datümer und keine Irrtümer enthalten. Da gibt es zwar Standard SQL Funktionen zur Umwandlung von solchen Feldern in Datumsfelder, aber zuweilen muss man sich dann die eigene Variante des Huddels erst aufbereiten und bindet man das in Views ein, stirbt das Query sobald es auf Irrtum statt Datum trifft. Ein klassischer Fall für eine UDF meinen viele und erfinden dieses Rad zum xten Male. Clevere unter uns greifen dann auf fertige Komponenten zurück, die es sogar Open Source gibt.

Ein Klassiker hiervon kann das alles auf elegante Weise entscheidend vereinfachen, da ich mal ausnahmsweise niemand auf die Füße treten mag, nenne ich die Funktion mal date400 und verschweige den Autor. Da kann ich in einem Select statement einfach select date400(meinHuddleDatum) schreiben und die Funktion gibt ein Datum zurück, wenn es eindeutig bestimmt und gewandelt werden kann, oder null, wenn das nicht geht. Das Ding kann noch mehr, man kann auch in einem zweiten Parameter mitgeben welches Datumsformat das Huddledatum hat, dann klappt das noch besser und richtig fehlerfrei. Auf den ersten Blick fantastisch!

Bei Risiken und Nebenwirkungen fragen Sie den Programmierer

Nun können wir uns ja diese ganzen Huddle Felder vom Leibe halten und machen doch mal eine View, in etwa so:
CREATE VIEW OHNE_HUDDEL AS (SELECT NAME, VORNAME, DATE400(MY_HUDDLE_DATUM, '*DMY') GEBURTS_DATUM) FROM PERSON
Sieht toll einfach aus, funktioniert auch phantastisch. Nun brauchen wir auch eine Geburtstagsliste, die soll natürlich nach Geburtstagen sortiert sein - eine meiner leichtesten Übungen, select * from [haste nicht gesehen] order by GEBURTS_DATUM et voila! Oder, wer hat denn morgen Geburtstag: select * from ... where GEBURTS_DATUM = CURRENT_DATE + 1 DAY, dafür haben wir das schließlich ja gemacht! Dass ersteres zu einem nicht verhinderbaren temporären Indexaufbau und letzteres zu einem full Tablescan führt macht bei den paar tausend Sätzen sicherlich nix, aber es gibt ja auch richtig große Tabellen und die haben auch Datumsfelder! Bei ein paar hundert Millionen Auftragsdaten ist so ein Tablescan, oder ein temporärer Index nicht mehr egal, das kann bei mehreren involvierten Joins Stunden und mehr dauern.
Dieser Effekt liegt schlicht daran, dass der Optimizer nicht erkennen kann, was innerhalb der UDF vor sich geht und das Resultat erst erkennt, wenn er die UDF ausgeführt hat; ist die UDF wenigstens als deterministic deklariert reduziert sich dadurch unter Umständen der Aufwand, aber der full Tablescan und die Sortierproblematik bleiben bestehen.

Kennen Sie die Lösung, oder sind Sie Teil des Problems?

Die eigentliche Ursache des Problems ist hier, wie so oft, das unzureichende Design, in diesem Fall der Datenbank. Huddelfelder haben in einer Datenbank nix zu suchen und mit richtigen Datumsfeldern und einer ordentlichen Normalisierung, hält man sich die meisten Probleme dieser Art vom Hals; hat man dann noch primary keys und referential constraints sind auch schon viele Index Probleme nicht präsent. Nun sind aber gerade viele Softwareprodukte im AS/400 Umfeld historisch geschrumpft, meist ist dann beschönigend von historisch gewachsen die Rede und/oder es gibt politische Restriktionen schlechtes Design beizubehalten, aber auch dann gibt es Möglichkeiten die Eleganz von SQL voll zu nutzen, die auch von der Performance her akzeptabel sind. Patentlösungen sind hier selten, Probleme müssen erkannt und Fall adäquat bearbeitet werden.
Im bisher diskutierten Beispiel der Huddeldatümer kommt man da schon mit ein wenig Überlegung zum Ziel. Pro Jahr gibt es in der Tat lediglich 360 gültige Datümer, die man auf verschiedene Art und Weise verhuddeln kann. In den wenigsten kaufmännischen Anwendungen braucht man mehr als 100 Jahre, sodass man von weniger als 40.000 gültigen Datümern ausgehen kann, das ist für eine Datenbank nicht viel, die kann man bequem in eine Tabelle packen, die man zur Konvertierung einfach dazu joined (ein Joint hilft hier nicht weiter!). CREATE TABLE LEDDUH(TRUE_DATE DATE, DMY_HUDDLE CHAR ( 7), CYMD_HUDDLE CHAR ( 8) , CCYMD_HUDDLE DEC ( 8, 0) , YMD_HUDDLE NUMERIC ( 6, 0), CONSTRAINT ELDDUH_P PRIMARY KEY (TRUE_DATE))
TRUE_DATEDMY_HUDDLECYMD_HUDDLECCYMD_HUDDLEYMD_HUDDLE
1940-01-01010140040010119400101400101
...............
2014-05-25250514114052520140525140525
...............
2039-12-31311239139123120391231391231
Diese Tabelle könnte nach Bedarf neben weiteren Huddelformaten auch noch andere Zeithierarchiedaten, wie Ultimo, Eierwoche etc. enthalten. Die weiter oben angeführte View bekäme dann folgendes Aussehen:
CREATE VIEW OHNE_HUDDEL AS (SELECT NAME, VORNAME, TRUE_DATE GEBURTS_DATUM) FROM PERSON p LEFT OUTER JOIN LEDDUH l on p.GEBURTSDATUM = l.YMD_HUDDLE
(hierbei muss dann beim join das passende Huddelformat ausgewählt werden). Vom Ergebnis ist diese View mit der vorherigen identisch, vermeidet aber Full tablescans. Damit das dann ordentlich brummt, definiert man noch über alle Huddeldatumsfelder einen passenden Index, sowohl auf den eigenen verhuddelten Tabellen, als auch auf der Datumstabelle zum enthuddeln.