Nieuwsbrief mei 2023

De grootste problemen met performance en schaalbaarheid ontstaan door het niet optimaal gebruiken van SQL Server. Op mijn site vind je een uitgebreide lijst met aanbevelingen voor het programmeren van SQL Server. In deze nieuwsbrief ga ik in op de taakverdeling tussen applicatie- en databasecode.

Wil je mijn nieuwsbrief niet ontvangen, laat het me weten.

Met hartelijke groet,

Paul van Oordt

Blijf op de server met stored procedures

'PL/SQL is Oracle's antwoord op de client-server architectuur,' las ik ooit, eind jaren '80, en ik begreep er niet veel van. Ik leg het even uit. 😉

In de client-server opzet bestaat een applicatie uit meerdere communicerende processen, doorgaans op afzonderlijke computers. Eén van de (server)processen implementeert de data-laag, en met PL/SQL of T‑SQL als procedurele uitbreiding op SQL kan dat proces geprogrammeerd worden. Vóór client server was dat niet nodig, want was de data in-proces beschikbaar en kon direct aangesproken worden door Cobol of PL/1. Dat soort programmatuur is overigens nog steeds in gebruik, en converteer je dit 1-op-1 naar een client server architectuur, dan krijg je een zéér slecht presterend systeem, omdat er vaak per row een database call wordt gedaan. Dit is het slechtste scenario voor communicatie met de database.

Beter is SQL queries als string opbouwen in de applicatiecode en naar de databaseserver sturen. De resultaten worden in bijvoorbeeld een DataGrid object geplaatst en zijn dan beschikbaar in de applicatie. SQL Server voert set-based queries uit, en in die zin wordt gebruik gemaakt van de kracht van de database-server. Maar er wordt ook nog veel ongebruikt gelaten.

Er is een soort geautomatiseerde variant hiervan in de vorm van object-relational mappers (ORM), of code first. De calls naar het database-proces worden niet meer zelf geschreven, maar gegenereerd door een tool zoals Entity Framework. De gegenereerde code is slecht te lezen en moeilijk te tunen. Sowieso is de eenvoud van ORM's enigszins bedrieglijk. Er moet nog steeds goed nagedacht worden over het datamodel en queries om een goed werkende en schaalbare data-laag te verkrijgen.

Voor alle bovenstaande opties geldt dat er onnodig veel inter-proces verkeer is, zowel in termen van roundtrips als in termen van de hoeveelheid data die over de lijn gaat.

Dat gaat beter door data-gerelateerde taken, bijvoorbeeld de afhandeling van een betaling, geheel in het database-proces te draaien. Er wordt dan geprogrammeerd in T‑SQL en de code wordt in de database opgeslagen als stored procedure. De voordelen hiervan zijn:

geen onnodige roundtrips tussen client en server
geen onnodige data over de lijn
transacties blijven zo kort mogelijk en worden gestart en beëindigd binnen één procedure call
complexe data-operaties worden uitgevoerd door het proces dat daar in gespecialiseerd is: SQL Server met zijn geavanceerde optimizer, indexes, statistics, monitoring en (auto-)tuning features, leidend tot optimale performance, schaalbaarheid en beheersbaarheid
scherpe interfaces in de vorm van procedure calls met parameters, returncode en datasets
door deze minimal coupling afzonderlijk te coderen en te verbeteren, te tunen, te testen en te documenteren
de data kan vergaand worden afgeschermd door deze alleen via procedures toegankelijk te maken (hier schrijf ik een volgende keer meer over)

Veel systemen worden opgezet zonder echte kennis van SQL Server of T‑SQL. En soms, gebruikmakend van een ORM, zelfs praktisch zónder zulke kennis. Dat kan een prima manier zijn om te beginnen, maar het kan ook makkelijk een beperkende factor worden voor groei. Veel toepassingen die ik in mijn praktijk tegenkom lopen tegen deze grens. Het herprogrammeren van delen van de logica in T‑SQL wordt dan een interessante optie.

Overweeg je deze stap, of wil je meer zicht op de prestaties en schaalbaarheid of de beperkingen van je huidige systeem, dan sta ik je graag terzijde. We kunnen bijvoorbeeld een stuk functionaliteit in T‑SQL coderen bij wijze van proof of principle. Ik deel mijn kennis graag, on the job of in een klaslokaal.

En triggers?

Naast procedures zijn er ook stored queries, beter bekend als views, en stored functions. Inline table valued functions zijn een soort geparametriseerde views en kunnen net als views handig zijn bij het hergebruik van code en het verbergen van complexiteit. Scalar en multi-statement table valued functions kunnen onverwachte nadelige effecten hebben op performance en worden best met enige voorzichtigheid gebruikt.

En dan zijn er nog triggers, net als stored procedures modules met T-SQL code, die executeren bij een insert, update en/of delete op een tabel. Ook deze worden beter spaarzaam gebruikt. Eén van de nadelen is dat ze lastig te testen zijn - nu juist een voordeel van procedures. Triggers zijn aanbevolen om denormalisatie van data te beheren, al is denormalisatie zelf vaak geen goed idee en zelden nodig.

Triggers kunnen ook worden gebruikt voor het realiseren van business logica, maar mijn voorkeur heeft het niet. Stel we hebben een eenvoudige toepassing voor een bibliotheek, waarbij een lid gewoon uit de tabel wordt verwijderd wanneer een lidmaatschap wordt opgezegd. Er gelden twee regels: Als het lid nog boeken thuis heeft kan de opzegging niet worden uitgevoerd en eventuele reserveringen worden verwijderd. Dit kan in een delete-trigger geheel server-side worden geprogrammeerd. Een rollback wordt gedaan als er voor het lid nog open uitleningen zijn en de relevante regels uit reserveringen worden verwijderd. Mijn voorkeur is echter om dit allemaal te doen in een procedure waarbij de logica direct zichtbaar, testbaar, te tunen en te documenteren is. Zie de versimpelde code hieronder.

Dank voor het lezen.


create procedure dbo.member_delete @memberid int
as
if exists (select * from dbo.loan where memberid = @memberid)
begin
  raiserror('member has books at home', 16, 1);
  return(-1);
end;
begin try
  begin tran
    delete dbo.reservation
      where memberid = @memberid;
    delete dbo.member
      where memberid = @memberid;
    raiserror('member deleted', 10, 1);
  commit tran;
end try
begin catch
  rollback tran;
  throw;
end catch;

mei 2023; de nieuwsbrief verschijnt enkele keren per jaar; aanmelden / afmelden