Nieuwsbrief oktober 2021

Na twee eerdere nieuwsbrieven voor de zomer, hier een nieuwe met hopelijk een paar interessante onderwerpen uit mijn praktijk.

Wil je de nieuwsbrieven niet ontvangen, laat het me weten.

Met hartelijke groet,

Paul van Oordt

Het gebruik van cursoren en andere loops

De meeste ontwikkelaars weten wel dat het gebruik van cursoren en while-loops niet leidt tot goed presterende code. Doorgaans zijn deze constructies ook niet nodig, en meestal zijn ze een teken van onvoldoende vertrouwdheid met set-based denken en programmeren. Iedere ontwikkelaar die SQL code schrijft hoort goed thuis te zijn in het set-based denken en programmeren en de verleiding te weerstaan om de complexiteit van het probleem terug te brengen van een verzameling van regels naar een enkele regel door er een loop omheen te zetten. De SQL taal is rijk genoeg om in één statement meerdere rijen te inserten, te updaten of te deleten, ook in complexe gevallen. SQL Server is ervoor gemaakt om dit soort code efficiënt uit te voeren en doet dat over het algemeen veel sneller dan het herhaald uitvoeren van kleine statements, zelfs al is dat zonder server roundtrips.

Niettemin zijn er situaties waar het gebruik van cursoren of andere loops nodig of wenselijk is. Een loop is nodig bijvoorbeeld als je vanuit SQL Server mails wilt sturen naar adressen in een tabel. Het aanroepen van de procedure sp_send_dbmail voor ieder van de regels kan alleen in een loop, niet in een enkel statement.

Het gebruik van een loop leidt tot tragere code, maar kan tóch handig zijn bij dataconversies. Als voorbeeld neem ik het denormaliseren van het orderbedrag in een zeer grote tabel orders als de som van de bijbehorende orderregels. Of dit wenselijk is, is een andere discussie. Voor andere, minder simpele dataconversies, en ook bijvoorbeeld voor het archiveren van data geldt het onderstaande eveneens.

Het vullen van het nieuwe veld orders.bedrag kan voor de hele tabel eenvoudig met dit statement:

with Q as
(select O.orderid, sum(R.bedrag) bedrag
  from orders O
    left join orderregels R on O.orderid = R.orderid
  group by O.orderid)
update orders
  set bedrag = Q.bedrag
  from Q
  where orders.orderid = Q.orderid;

Deze code is snel geschreven, en executeert sneller dan de alternatieven, maar heeft één groot nadeel: locking. De gehele update vindt plaats binnen één transactie en beide tabellen worden voor de duur van de transactie geheel gelockt. Dat betekent dat er een onderhouds-window zal moeten zijn voor deze operatie. Dan nog is het riskant, want komt het einde van het window in zicht, dan kan de query niet zomaar even gestopt worden. Alles wat tot dan gedaan is, zal worden ge-rollbacked, wat zeer veel tijd kan kosten.

Met een cursor over orders is dit probleem te vermijden. Per order wordt het gedenormaliseerde bedrag geschreven, alsmede een bit dat aangeeft of voor deze order de denormalisatie is uitgevoerd.

update orders
  set bedrag = 
       (select sum(orderregels.bedrag) 
          from dbo.orderregels 
          where orderid = @orderid)
     ,bedrag_denormalized = 1 
  where orderid = @orderid;

Het bit bedrag_denormalized wordt uitgelezen door iedere routine die het orderbedrag wil weten. Dergelijke code moet toch worden aangepast als gevolg van de modelwijziging, en werkt in dit geval altijd, of de dataconversie nu wel, niet of deels is uitgevoerd. De conversiecode kan makkelijk gestopt en hervat worden door bedrag_denormalized = 0 op te nemen in de cursor-definitie. Locking is slechts zeer kort en van een langdurige rollback is geen sprake. De gehele dataconversie zal met een cursor langer duren dan met een enkel statement, maar is met cursor volledig 'online'. Reguliere operaties hebben geen last van de dataconversie en kunnen gewoon doorgaan.

Uiteraard is een index op orderregels.orderid met bedrag incuded noodzakelijk voor het goed verwerken van deze werklast. Die index is sowieso aan te raden, zeker ook wanneer het bedrag niet gedenormaliseerd is.

Another day at somebody's office

Gelukkig kan er weer gewerkt worden samen met de klant achter een laptop of met een paar mensen in een zaaltje met een groot scherm. Dat is de manier waarop ik het liefst werk, één of enkele dagen. Op locatie. Ik zeg altijd dat ik er niet op uit ben om lang bij een klant te blijven rondlopen, en dat meen ik. Ik ben royaal in het delen van mijn kennis, en alles wat ik jullie kan vertellen, hoef ik niet zonodig ook nog zelf bij jullie uit te voeren. Liever heb ik dat je me er weer bijroept als je een nieuw issue hebt waar je niet goed uitkomt. Dat ik je andermaal inzicht kan geven in wat SQL Server kan, hoe het werkt onder de motorkap, en hoe ik, op basis van mijn ervaring met honderden systemen, het probleem zou aanpakken.

Met de klant troubleshooten, performanceproblemen ontleden, de mogelijkheden doordenken van nieuwe functionaliteit, het zijn intensieve dagen. Samen zitten we achter die laptop of in dat zaaltje, en we zijn in een constante dialoog. Ik vertel wat ik doe, waarom ik nu hier zoek, dan dat wil controleren, dit echt even wil uitschrijven en testen omdat de devil in the details is. Jullie geven aan wat de eisen zijn, waar wat staat, wat het belangrijkst is, wat je zelf al hebt bedacht en uitgezocht. Het is leuk, intensief en leerzaam.

We wisselen exploratie in de breedte af met diepte. Zonder breedte is er het gevaar een makkelijke oplossing over het hoofd te zien. En zonder diepte blijft het allemaal 'mogelijk' en 'in principe'. Bij performance-issues pak ik meestal op zijn minst één query waarvan ik echt laat zien hoe die door middel van indexering en/of herschrijving helemaal geoptimaliseerd kan worden. Zodat jullie dat zelf kunnen gaan doen voor het hele systeem. Denken we na over nieuwe functionaliteit, dan maken we samen een proof of principle.

Ruim 20 jaar werk ik met SQL Server voor zo'n 200 opdrachtgevers en het blijft een genoegen om mooie nieuwe code te maken of bestaande code op te schonen, te verbeteren en te vereenvoudigen, om een soepel snorrend systeem achter te laten. Ik reis graag naar je toe, vanuit Utrecht of vanuit Antwerpen.

Bij Kegro Deuren in Groesbeek.

oktober 2021; de nieuwsbrief verschijnt enkele keren per jaar; aanmelden / afmelden