DATABASEONTWERP
Luteijn Automatisering
(KvK: 60276037, BtwNr: NL062594862B01)
Artikelen over automatisering
Uitgangspunten technisch ontwerp, Toekomstige softwareontwikkeling, SQL-Server, Hoofdpagina en Contact
Met de komst van relationele databases is het mogelijk geworden grotere hoeveelheden data te hanteren, dan voorheen mogelijk was met een recordgeoriënteerde database. Echter nog steeds blijft de database een belangrijke bron van problemen bij ontwikkelingen. Performance, locking, transacties, rapportages, batchprocessen, data-integriteit, keys, indexen, etc. zijn zaken waar het goed van is om er tevoren over na te denken. Aanpassingen achteraf zijn vaak onmogelijk, onbespreekbaar of uitermate kostbaar. Bij het ontwerpen van een database is kennis van relationele databases in het algemeen en het gebruikte systeem in het bijzonder onontbeerlijk. De ontwerper moet niet proberen ‘de vorige oorlog’ te winnen. Net zoals voorheen bij recordgeoriënteerde databases ontkomt de ontwerper er niet aan specifieke kennis van gebruikte databases in de programma’s te verwerken.
Relationele databases vereisen een heel ander denkraam dan recordgeoriënteerde databases. Een belangrijk verschil zit hem in de benadering van records. Bij een sommatie was voorheen de enige oplossing alle masterrecords een voor een te bezoeken bijbehorende details te lezen en zelf op te tellen wat opgeteld moest worden (tandwielkasttechniek). Bij een SQL-database kan een query met een JOIN tussen betrokken tabellen in een keer alle benodigde data ophalen. SQL benadert informatie niet noodzakelijkerwijs volgens de klassieke indexen. Het lezen van een enkel record is relatief langzaam. Sommeren op de database zelf is razendsnel. Vermijdt daarom tandwielkasttechniek bij SQL-databases.
Een vaak vergeten aspect van databaseontwerp is performance. Deze blijkt achteraf altijd onvoldoende. In het begin van de ontwikkeling zijn er weinig records en vliegt elk proces over het scherm. Maar als er met realistische data gewerkt wordt, blijken vaak kostbare aanpassingen nodig om aanvaardbare responsetijden te krijgen. Een rapportage moet binnen enkele minuten op de printer liggen. Een batchproces ook in de nacht, mag zeker geen uren (of dagen) duren. Bij definitie van tabellen dient een evenwicht te worden gevonden tussen normaliseren en toestaan van redundantie om rapportages sneller op de printer te krijgen. Het is verstandig na te denken over statische tabellen (weinig mutaties) en dynamische transactietabellen met miljoenen records, die in bijna ieder proces doorzocht worden.
Saldi kun je opslaan of telkens herberekenen. Herberekenen kan bijlanger bestaande databases performanceproblemen geven, maar is qua normalisatie en scheiding tussen dynamische en statische data verre te prefereren. Het is verstandig om met name energie te stoppen in de performance van dynamische tabellen. Van nature hebben deze de neiging erg groot te worden. Breng geen onnodige toeters en bellen aan. Indien mogelijk kan opbreken van data over meerdere tabellen helpen. Het vooraf in kaart brengen van aantal records en zoekvragen kan helpen om de beste oplossing te vinden.
Werken met databases is onderhoudsgevoelig. Veel kosten kunnen bespaard worden door business-rules centraal op één plaats in het programma aan te brengen. Enige voorzichtigheid daarbij is op zijn plaats. Een oplossing, die geen rekening houdt met performance problemen zal niet werken. Een oplossing, die te veel onderhoud geeft ook niet. Een recente ontwikkeling is onderhoud van database vanuit 3-GL ontwikkelomgeving. Volledig automatisch kunnen objecten gegenereerd worden, die de communicatie tussen programma en database verzorgen. Nieuwe velden worden automatisch aangemaakt in de database en bekend gemaakt aan de interfaces van het object. Binnen het object kunnen eenmalig de business-rules, die het object besturen geïmplementeerd worden.
Het klinkt gewoon te mooi om waar te zijn en dat is ook zo. Het is typisch een oplossing om de vorige oorlog te winnen. Zulke objecten zijn namelijk van nature recordgeoriënteerd en daarmee uitermate slecht voor de performance van een SQL-systeem. Voorbeeld: Klantrecords worden binnen het object automatisch gelinkt aan bijbehorende rekeningen en orders. Bij opvragen van één klant haalt het systeem automatisch via losse queries bijbehorende gegevens op. Bij bepalen van de maandomzet, moeten alle klanten één voor één opgevraagd worden. Daarbij wordt veel overbodige informatie opgehaald. Bij 10.000 klanten geeft dat tenminste 10.000 + 10.000 grotendeels overbodige queries.
De situatie, dat het handig is om de boel per klant te bekijken is tamelijk zeldzaam. In de praktijk blijken mutatieschermen zich te verhouden tot rapportages in een verhouding van 1 staat tot 10. Alleen in onderhoudschermen wordt gewerkt met één record per keer. In vrijwel alle andere processen is behoefte aan een subset van gegevens over meerdere records. Voor een relationele database zijn kleine queries weinig optimaal. Veel beter qua performance is een JOIN van klanten met rekeningen om in een keer alle benodigde data op te halen. Daarmee komen echter business-rules buiten het object terecht. Zodra het uit performanceoverwegingen nodig is om speciale queries te schrijven, zit binnen de kortste keren het hele programma vol met business-rules en is het voordeel van objecten verloren gegaan.
Een heel andere benadering is het verplaatsen van business-rules naar de database. Bij gebruik van triggers, constraints, defaults en berekende velden kunnen een regels toch op een centrale plaats in het systeem (de database) ondergebracht en onderhouden worden. Echter ook deze benadering heeft zijn prijs. Het vereist vaak verbazingwekkend veel databaseonderhoud en dat is kostbaar. Niet alle databases hebben evenveel faciliteiten voor planmatig onderhoud. Soms zijn er hinderlijke uitzonderingen. Het is jammer, dat triggers, constraints, defaults en stored procedures in vele opzichten primitieve hulpmiddelen zijn. Hergebruik van code is lastig. Foutopsporing is primitief. Generieke code is slecht voor de optimizer (herberekenen executieplannen).
Een oplossing dient gezocht te worden, waarbij het beste van beide werelden samengevoegd wordt. Het is qua performance altijd handig om zoveel mogelijk op de database te doen. Dan is er de minste belasting op het netwerk. Databases zijn geoptimaliseerd naar het hanteren van grote hoeveelheden data. De servers zijn kannibalen. Ze pakken alle resources, die ze vinden kunnen. Het in geheugen halen van complete tabellen is niet ongewoon. Ingewikkelde delen van processen daarentegen kunnen het beste in de derde generatie taal geïmplementeerd worden. De prestaties van optimizers lopen dramatisch terug bij ingewikkelde queries. Het onderhoud van complexe queries op de database is peperduur vanwege gebrekkige programmeerhulpmiddelen. Het opsplitsen van ingewikkelde processen in een aantal simpele queries kan de performance en de onderhoudbaarheid van het geheel enorm verbeteren.
In een multiuser omgeving dient iets verzonnen te worden om te voorkomen, dat twee gebruikers tegelijk hetzelfde record wijzigen en dat een van de wijzigingen blijft bestaan zonder enige vorm van waarschuwing aan de verliezer. Databases ondersteunen daartoe een groot aantal vormen van locking, maar helaas zit de ‘ideale oplossing’ daar meestal niet tussen. Batches, rapportages en onderhoud van statische gegevens hebben elk behoefte aan een geheel eigen regiem. Botsingen tussen processen moeten zoveel mogelijk vermeden worden. Het is denkbaar statische en dynamische gegevens grotendeels te scheiden. Statische gegevens kunnen readonly zijn voor verwerkingsprocessen en omgekeerd. Rapportages hebben geen locking nodig, omdat alleen gelezen wordt in beide soorten tabellen.
Bij muteren van statische klantgegevens ontstaan zelden conflicten met andere processen. Eenmalig een brief naar het verkeerde adres is geen drama. Bij een verwerking daarentegen worden records aangemaakt in dynamische tabellen. Alle aangemaakte gegevens zijn kritisch. Een batch dient in zijn geheel uitgevoerd te worden of verworpen. Geen enkele fout of interruptie door andere processen is gewenst of toegestaan. In batchprocessen tijdens werktijd hoeven beide soorten mutaties elkaar niet te bijten. Alleen als redundante data zoals saldi bij statische klantgegevens opgeslagen wordt, kunnen conflicten ontstaan tussen dynamische en statische gegevens. Goed ontworpen tabellen voorkomen onnodige locking problemen. Per systeem dienen eisen van tevoren in kaart gebracht worden om een zo goed mogelijk compromis te realiseren. Wijzigingen achteraf zijn zelden mogelijk.
De meeste ontwerpers kiezen bij muteren van statische gegevens voor optimistic locking. Er wordt niet gelockt tijdens het lezen en bij het wegschrijven van statische records wordt gekeken aan de hand van een systeemveld of iemand anders het record gewijzigd heeft. Echter bewaking van dit systeemveld moet helaas meestal zelf geïmplementeerd worden. De meeste databases leveren daar geen standaardoplossingen voor. Dynamische tabellen zijn vooral het domein van verwerkingsprocessen. Het beste kan er sprake zijn van impliciete locking. Dat wil zeggen gewone gebruikers mogen nooit handmatig records wijzigen. Bij voorkeur is ‘Insert’ (en ‘Delete’) de enige vorm van mutatie op zulke tabellen. Een ‘Update’ in samenhang met een grote overkoepelende ‘transactie’ werkt verstorend op andere bedrijfsprocessen.
Plaatsing van enige vorm van dynamische gegevens in statische tabellen is onverstandig en geeft onvermijdelijk nare bijverschijnselen. Batchprocessen kunnen om performanceredenen niet wachten op andere processen en schrijven vaak zonder locking hun mutaties weg. Als iemand in de tussentijd een telefoonnummer aan het wijzigen is, kunnen kritische gegevens ongemerkt en onbedoeld overschreven worden.
Op relationele databases is meestal een of ander transactiesysteem aanwezig. Bij het mislukken van een databaseoperatie wordt de integriteit van de data gegarandeerd door de complete operatie terug te draaien. Deze transactiesystemen werken niet altijd, zoals je dat graag zou willen. De volgende problemen tref je daarbij aan:
· Een transactie rond een compleet proces duurt erg lang en blokkeert het hele systeem.
· Een transactie wordt slechts gedeeltelijk teruggerold en de complete database ligt in puin.
· Een triviale waarschuwing wordt niet goed afgevangen en leidt onnodig tot terugrollen.
· Alle door het proces geraakte records worden onzichtbaar voor de rest van het systeem.
· Meerdere soortgelijke processen kunnen niet naast elkaar draaien.
Steeds vaker wordt je tegenwoordig geconfronteerd met de wens om bepaalde verwerkingen tijdens kantooruren te kunnen doen. Het is voorts ongewenst, dat een dergelijk proces half wordt uitgevoerd. Wanneer er ook maar iets fout gaat, dan moet alles volledig teruggerold worden. Het gevolg is dat er transactie rond het complete proces gezet moeten worden, die meerdere minuten tot een uur kunnen duren. Dergelijke langdurige transacties worden niet geadviseerd door de databaseleveranciers. Maar soms kan je er gewoon niet onderuit.
Bij een administratiesysteem met meerdere boekhouders, die tegelijkertijd grote aantallen boekingen aanmaken dienen gegevens op een bepaald moment doorgeschoven te worden naar grootboeken. Dat kan meerdere minuten duren. Het is ongewenst anderen te laten wachten vooral omdat er automatische (interpay) boekingen bestaan, die nog veel langer bezig zijn. Het probleem zit meestal in de boekstuknummers. Deze zijn traditioneel gezien opeenvolgend. In ouderwetse systemen worden daartoe twee werkwijzen aangetroffen:
1. Het gebruik van recordaantal in de tabel om het volgende nummer te bepalen (?)
2. Het korte tijd exclusief locken van een tabel met unieke boekstuknummers.
Het eerste systeem zal zelfs op een recordgeoriënteerd semi multiuser systeem slecht werken, maar ook het tweede systeem is op een relationele database moeizaam, vanwege onbedoelde exclusiviteit. Het ophalen van een uniek boekstuknummer uit een tabel via een exclusieve lock hoeft weliswaar niet lang te duren, maar omdat de omsluitende transactie de locks pas vrijgeeft bij het committen van de complete transactie, heeft het ophalen van het unieke boekstuknummer als bijverschijnsel, dat feitelijk niemand anders meer iets kan doen. Hier zou overwogen moeten worden opeenvolgendheid van boekstuknummers los te laten en automatisch zogenaamde ‘identity’ nummers te laten genereren. Opeenvolgendheid is op een multiuser systeem toch al een vrijwel onhaalbare doelstelling. Er ontstaan gaten, zodra iemand besluit zijn boeking niet op te slaan of naderhand een boeking verwijderd.
Twee soorten transacties bestaan, t.w. impliciete en expliciete transacties. Bij elke mutatie wordt door het systeem automatisch een impliciete transactie opgestart. Als er iets misgaat binnen de operatie, dan worden alle tussenliggende handelingen teruggerold. Als meerdere mutaties samengebracht worden in één expliciete transactie, dan is adequaat reageren op het terugrollen van deeltransactie gewenst. Anders resteert na afloop een halve verwerking en een compleet verwoeste database. De database houdt een teller bij om aan te geven hoe diep de nesting van transacties is. Pas als de teller op nul staat en geen fout is opgetreden wordt de hele operatie werkelijk doorgevoerd. Alle tussenliggende COMMIT commando’s beïnvloeden slechts de teller. Dit mechanisme blijkt in de praktijk gebrekkig te werken. Regelmatig komt het voor, dat de count niet klopt. Het is voorgekomen dat een irrelevante waarschuwing onbedoeld de eerste helft van het proces terugrolde, terwijl de rest van het proces gewoon verderging.
Vrijwel elke derde generatie applicatie, die gebruik maakt van het transactiesysteem laat de transactie count niet over aan de database, maar creëert speciale voorzieningen binnen het programma. Pas als de eigen teller op nul is staat, wordt een echte COMMIT gedaan. Dat stelt hoge eisen aan de foutafhandeling. Foutafhandeling in moderne 3 GL omgevingen is tegenwoordig gebaseerd op het genereren van zogenaamde ‘exceptions’. Bij het begin van een foutgevoelig proces wordt de toestand van het programma vastgelegd. Na het optreden van een willekeurige fout wordt teruggesprongen naar de opgeslagen situatie. Dat stelt hoge eisen aan bijhouden van tellertjes. Sommige gekochte onderdelen van derden gedragen zich afwijkend tijdens foutafhandeling.
Begin of einde van een tabel wordt wel eens doorgegeven aan de onderliggende software via ‘exceptions’. Een bekend probleem bij ADO is het optreden van time-out’s. Bij het opvragen van een record is het redelijk na 15 seconde een foutmelding te geven. Bij het opvragen van 10.000 records is het logisch dat dit wat langer duurt. Zowel bij lezen als schijven van records komen time-out problemen voor. Standaard voor lezen is 15 seconden en voor schrijven 30 seconden. Bij lezen moet binnen 15 seconden iets binnenkomen. Een time-out kan soms voorkomen worden door de ‘ORDER BY’ clause weg te laten. Sorteren kan immers pas gedaan worden als alle data verzameld is op de database. Ook kunnen gegevens in tranches opgevraagd worden. Een truc is om via een loze query de tabel vooraf in geheugen te lezen. Bij wegschrijven van mutaties moet vooral gedacht worden aan meerdere kleinere tranches of aanpassingen in het databaseontwerp.
Het probleem met meerdere bekende relationele databases is, dat de time-out niet aangepast kan worden. De ene ontwerper kiest er dan voor geen ADO-connectie te gebruiken. De andere past zijn queries erop aan. Het is een probleem dat mettertijd vanzelf zal verdwijnen door aanpassingen in ADO of de databases. Tot die tijd is het een behoorlijk obstakel dat vaak pas ontdekt wordt op het moment dat het te laat is.
Om records te kunnen terugvinden binnen tabellen zijn sleutels nodig. Bij voorkeur zijn deze uniek. Op sommige tabellen staan meerdere sleutels. Sommige uniek en sommige niet. Bij een niet unieke primaire sleutel worden records uniek gemaakt via een verborgen recordnummer. Systemen afkomstig uit het verleden hebben vaak een gecombineerde sleutel over meerdere velden. In nieuwe systemen wordt vaak gebruik gemaakt van ‘identity’ kolommen met door het systeem gegenereerde unieke (vier byte lange) volgnummers. De keuze tussen beide systemen is een wezenlijke ontwerpbeslissing.
Bij de sleutels horen indexen die efficiënter zijn naar gelang de lengte van de unieke key korter is. Deze worden gebruikt als referentie naar andere tabellen. Foreignkey constraints zijn in feite indexen en daarvoor geldt hetzelfde: Hoe korter de key hoe efficiënter de JOIN. Oudere databases met samengestelde key zijn minder optimaal, maar herschrijven is meestal onbespreekbaar. Een bezwaar van identity kolommen is, dat het door het systeem gegenereerde waarden zijn. Alle verbindingen van de ene tabel naar de andere worden gelegd via ‘toevallige’ waarden. Als delen van de ene database naar de andere gekopieerd moeten worden, dan is dat een probleem. Bij groot databaseonderhoud kan het mechanisme tijdelijk uitgezet worden. In alle andere gevallen dienen gegevens ‘gestructureerd’ aangeleverd te worden.
Een slecht begrepen onderdeel van relationele databases zijn idenxen. Programmeurs afkomstig uit de recordgeoriënteerde wereld hebben moeite indexen te zien als performance verbeterende maatregel. Tunen van databases via indexen is het werk van specialisten. Het is zelden relevant tijdens het ontwikkelen van het programma. Tegen het einde van het ontwikkeltraject zou met realistische data nader naar dit aspect van databaseontwerp gekeken kunnen worden. In relationele databases bepaalt de optimizer hoe gegevens opgehaald worden. Vrijwel altijd zal de optimizer de voorkeur geven aan een tablescan boven een indexscan als veel records nodig zijn. Meestal is de gewone index slechts een kopie van de hoofdtabel, waarin sneller gezocht kan worden omdat hij kleiner is. Echter na het vinden van de juiste key moet vervolgens nog de rest van de data opgehaald worden uit de hoofdtabel en dat is een kostbaar proces.
De optimizer kiest alleen voor gewone indexen als minder dan 10% van de records nodig zijn. Wel kunnen indexen ‘effectiever’ gemaakt worden via toevoegen van extra velden waardoor alle gezochte informatie in de index aanwezig is. Een bijzondere variant van indexen zijn de zogenaamde clustered indexen. Deze wordt veel gebruikt bij opwaarderen van bestaande systemen. De database houdt dan de data in volgorde van een gegeven (samengestelde) key. Deze index is aanmerkelijk sneller en handiger in het gebruik dan een normale indexen en worden al gebruikt zodra minder dan 20% van de records nodig is. Lange sleutels geven daardoor minder performanceproblemen, hoewel invoegen van gegevens wat langzamer is.
Het systeem kan geforceerd worden om een bepaalde index te gebruiken. Dit wordt niet geadviseerd aangezien de optimizer dit veel beter doet. Soms blijft het systeem echter hardnekkig een tabelscan doen bij ophalen van slechts enkele records. Het is dan zaak om via de Query Analyser het executieplan te bestuderen. Soms is het voldoende om de statistieken, die het systeem bijhoudt aan te passen. Standaard worden statistieken t.b.v. een samengestelde index bijgehouden op de eerste kolom. Als dit een Ja/Nee veld is, dan is de nuttigheidsgraad van de index beperkt. Meer velden kunnen betrokken worden in de statistiek. Ook kan de index aangepast worden, zodat een onderscheidend veld vooraan komt te staan.
