upUDFs 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_DATE | DMY_HUDDLE | CYMD_HUDDLE | CCYMD_HUDDLE | YMD_HUDDLE |
1940-01-01 | 010140 | 0400101 | 19400101 | 400101 |
... | ... | ... | ... | ... |
2014-05-25 | 250514 | 1140525 | 20140525 | 140525 |
... | ... | ... | ... | ... |
2039-12-31 | 311239 | 1391231 | 20391231 | 391231 |
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.