Het maandrapport duurt een half uur en iedereen wordt er traag van? Waarom dat gebeurt en hoe u het oplost

De laatste werkdag van de maand. Om acht uur ’s ochtends start de financiële afdeling het maandelijkse verkooprapport. Vijf minuten later kan het magazijn geen pakbonnen printen, en verkoop belt IT omdat het systeem plat zou liggen. Niemand weet wat er aan de hand is, dus klikt iemand op financiën nog een keer op “Genereren”. Nu draaien er twee rapporten.

Dit scenario is verzonnen, maar bijna elk bedrijf met een eigen bedrijfsapplicatie zal het herkennen. En meestal ligt het niet aan de server. Het ligt aan de manier waarop de rapportages zijn gebouwd.

1. 08:00Financiën start het maandrapport
2. 08:05Het magazijn kan geen pakbonnen printen
3. 08:20Verkoop meldt dat het systeem plat ligt
4. 08:25Iemand start het rapport opnieuw
5. 08:40Time-out, alles begint opnieuw

Waarom één rapport iedereen stillegt

Stel u een winkel voor die sluit voor de inventarisatie terwijl de klanten nog bij de kassa in de rij staan. Bedrijfsapplicaties doen dat voortdurend:

• Rapportages en het dagelijkse werk gebruiken dezelfde database. Het rapport leest miljoenen rijen, en dezelfde schijf, hetzelfde geheugen en dezelfde processor moeten tegelijk het magazijn, verkoop en alle anderen bedienen.
• Lezen houdt schrijven op. Op SQL Server kan een lange leesactie standaard iedereen ophouden die dezelfde gegevens wil wijzigen, en andersom. Het rapport wacht op het magazijn, het magazijn wacht op het rapport.
• Alles wordt telkens opnieuw berekend. Om het totaal van vorige maand te krijgen, telt het rapport elke keer vijf jaar facturen op. Zolang er weinig gegevens waren, merkte niemand het.
• De applicatie haalt alles in het geheugen. In plaats van de database te laten optellen en honderd rijen terug te geven, laadt de applicatie alle gegevens en rekent zelf.
• De gebruiker wacht voor het scherm. Het rapport draait terwijl de browser wacht. Geeft de browser het op, dan klikt de gebruiker nog een keer, en draait hetzelfde werk dubbel.

Wat het kost

Een eenvoudig voorbeeld met verzonnen, maar realistische cijfers:

Mensen die in de applicatie werken	40
Verloren tijd telkens als een rapport alles vertraagt	30 minuten
Hoe vaak dat gebeurt (maandafsluiting, btw, directieoverleg, ad hoc)	4 keer per maand
Verloren uren per maand	80
Tegen € 30 per uur	€ 2.400 per maand, bijna € 29.000 per jaar

Dat is alleen wat te tellen is. Daarbovenop: leveringen die te laat vertrekken, een financiële afdeling die ’s avonds blijft om niemand te storen, en beslissingen op oude cijfers omdat niemand het rapport tijdens werktijd durft te starten.

Oplossingen, de goedkoopste eerst

Het goede nieuws is dat u zelden een nieuw systeem nodig hebt. De volgorde is meestal dezelfde:

Oplossing	Wat het betekent	Inspanning
1. Query en indexen	Het rapport vraagt de database op de juiste manier en krijgt alleen wat het nodig heeft	Dagen
2. Lezen houdt schrijven niet op	Een database-instelling zodat rapport en magazijn niet meer op elkaar wachten	Uren tot dagen, met testen
3. Het rapport draait op de achtergrond	De gebruiker klikt, werkt door en krijgt een e-mail met een link als het rapport klaar is	Dagen
4. Vooraf berekende totalen	Totalen per dag, klant en artikel worden één keer berekend, ’s nachts	Dagen tot weken
5. Een aparte kopie voor rapportages	Rapportages lezen uit een kopie van de database, het dagelijkse werk gebruikt het origineel	Dagen, afhankelijk van het platform

1. Query en indexen. Dit is bijna altijd de eerste stap, en vaak ook de laatste. Een rapport dat de hele tabel leest omdat een index ontbreekt, of een rapport dat voor één pagina duizenden queries stuurt, kan van een half uur naar minder dan een minuut gaan. De meest voorkomende oorzaken beschreven we in de artikelen over trage applicaties en EF Core performance.

2. Lezen houdt schrijven niet op. SQL Server heeft een optie (Read Committed Snapshot Isolation) waarmee een rapport een consistent beeld van de gegevens leest zonder iemand op te houden die schrijft. In Azure SQL Database staat die standaard aan, op uw eigen SQL Server uit. Het is één instelling, maar die verandert hoe de database zich gedraagt, dus wordt ze getest voordat ze live gaat.

3. Het rapport draait op de achtergrond. In plaats van dat iemand voor het scherm wacht, gaat het rapport in een wachtrij en wordt het gemaakt als er capaciteit is, één voor één. Wie het aanvroeg, krijgt een e-mail als het klaar is. Geen dubbele klikken en time-outs meer, en de zwaarste rapporten kunnen tot de avond wachten.

4. Vooraf berekende totalen. De meeste rapporten vragen hetzelfde: hoeveel, per dag, klant, artikel of regio. Die totalen kunnen één keer worden berekend, ’s nachts of na elke wijziging, en het rapport leest dan een paar duizend rijen in plaats van een paar miljoen.

5. Een aparte kopie voor rapportages. Gebruikt u Azure SQL Database in de laag Premium of Business Critical, dan betaalt u al voor een alleen-lezen kopie van de database die de applicatie niet gebruikt. Rapportages worden daarheen gestuurd met één instelling in de connection string:

"ConnectionStrings": {
  "App":     "Server=tcp:company.database.windows.net;Database=Erp;...",
  "Reports": "Server=tcp:company.database.windows.net;Database=Erp;ApplicationIntent=ReadOnly;..."
}

Het dagelijkse werk gebruikt App, de rapportages gebruiken Reports, en ze vechten niet meer om dezelfde capaciteit. Gegevens kunnen op de kopie een moment later aankomen dan in het origineel, wat voor een maandrapport niet uitmaakt. Op uw eigen SQL Server is iets vergelijkbaars mogelijk, maar dat hangt af van de editie en de licenties, dus dat wordt eerst gecontroleerd.

In welke volgorde

1. MetenWelke rapporten pijn doen, en waarom
2. QueriesIndexen en minder aanroepen
3. AchtergrondNiemand wacht voor het scherm
4. Totalen en kopieAlleen als het nog nodig is

Eerst wordt er gemeten, zoals altijd. De meting laat zien welke drie of vier rapporten het grootste deel van de problemen veroorzaken, en vaak is het minder werk dan het leek. Pas als de queries op orde zijn, heeft het zin om te praten over verwerking op de achtergrond, vooraf berekende totalen en een aparte database. Een nieuwe rapportagetool of een zwaardere server komt als laatste, als het al nodig is.

Hoe wij werken

ProCoding is een .NET-studio uit Split, Kroatië. Trage rapportages en een database die iedereen ophoudt, zijn precies wat wij oplossen: in .NET-applicaties, SQL Server en Azure SQL, van queries en indexen tot verwerking op de achtergrond en alleen-lezen kopieën van de database. Meer over wat we op Azure doen, leest u op de pagina Azure voor .NET-applicaties.

We beginnen met een meting van uw rapportages, en u krijgt een lijst van oorzaken en oplossingen, gerangschikt naar wat het meeste oplevert. Daarna lossen wij ze op, of doet uw team dat, wat u het beste uitkomt. De eerste stap is een gratis gesprek van 30 minuten.

Bronnen

• Read queries on replicas (read scale-out), Microsoft Learn
• SET TRANSACTION ISOLATION LEVEL, Microsoft Learn
• Background tasks with hosted services in ASP.NET Core, Microsoft Learn