SQL-query optimaliseren: van 30 seconden naar 30 ms

Een SQL-query optimaliseren begint met meten: je laat de database tonen hoe hij de query uitvoert, zoekt de stap die de meeste tijd kost en lost precies die op. Meestal gaat het om een ontbrekende of verkeerde index, een query die veel meer rijen ophaalt dan nodig, of een berekening die voor elke rij opnieuw gebeurt. Dat de winst groot kan zijn, zagen we bij een klant in de logistiek: een rapportage-query ging van 30 seconden naar 30 milliseconden. Hieronder lees je hoe je zelf de trage query vindt, welke technieken het meeste opleveren en wanneer je een specialist inschakelt.

Waarom is een SQL-query traag?

Een SQL-query is traag als de database meer werk doet dan nodig is om het antwoord te vinden. Een database leest gegevens van schijf of uit het geheugen, combineert tabellen en sorteert de uitkomst. Elke stap die op een grotere hoeveelheid gegevens werkt dan nodig, kost tijd.

De oorzaken die je het vaakst tegenkomt:

• Geen bruikbare index. De database moet de hele tabel doorlopen om een paar rijen te vinden.
• Te veel gegevens ophalen. Een query vraagt alle kolommen en alle rijen op, terwijl het scherm er maar twintig toont.
• Een functie op een kolom in de WHERE-voorwaarde. Bijvoorbeeld zoeken op het jaar van een datum. Een gewone index op die kolom wordt dan vaak niet gebruikt.
• Een query per rij. De applicatie haalt eerst een lijst op en vraagt daarna voor elke regel apart extra gegevens op. Bij duizend regels zijn dat duizend extra query's.
• Verouderde statistieken. De database schat verkeerd hoeveel rijen er zijn en kiest daardoor een trage aanpak.
• Groei. Een query die met duizend rijen snel was, is dat met tien miljoen rijen niet meer.

Dat laatste punt verklaart waarom een applicatie die jaren goed werkte, ineens traag wordt. Er is niets veranderd aan de code, maar wel aan de hoeveelheid gegevens.

Hoe vind je de trage query?

Je vindt de trage query door eerst te meten welke query's de meeste tijd kosten, en daarna per query het uitvoeringsplan te bekijken. Het uitvoeringsplan (query plan) laat zien welke stappen de database neemt: welke tabellen hij leest, of hij een index gebruikt en hoe hij tabellen aan elkaar koppelt.

Volgens de documentatie van PostgreSQL maakt de database voor elke query een plan, en is de keuze van het juiste plan bepalend voor de snelheid. Met het commando EXPLAIN zie je het plan dat de database van plan is te gebruiken. Met EXPLAIN ANALYZE voert de database de query ook echt uit en toont hij per stap het werkelijke aantal rijen en de werkelijke tijd. Andere databases hebben hetzelfde hulpmiddel onder een andere naam. Microsoft Learn spreekt bij SQL Server van een geschat en een werkelijk uitvoeringsplan.

Zo pak je het aan:

1. Verzamel de traagste query's. De meeste databases houden statistieken bij van uitgevoerde query's, met de totale en gemiddelde tijd.
2. Kies de query met de grootste impact. Een query van 2 seconden die duizend keer per dag draait, kost meer dan een rapport van 30 seconden dat één keer per week draait.
3. Bekijk het uitvoeringsplan. Zoek de stap met de hoogste tijd of het hoogste aantal rijen.
4. Vergelijk schatting en werkelijkheid. Wijkt het geschatte aantal rijen sterk af van het werkelijke, dan kloppen de statistieken waarschijnlijk niet.

Let op: EXPLAIN ANALYZE voert de query echt uit. Bij een query die gegevens wijzigt, zoals UPDATE of DELETE, test je dat op een kopie of binnen een transactie die je daarna terugdraait.

SQL-query optimaliseren: welke indexen helpen?

Een index helpt als hij past bij de kolommen waarop je zoekt, koppelt of sorteert. Een index werkt als het register achter in een boek: de database hoeft niet elke pagina te lezen om te vinden wat hij zoekt. Volgens de PostgreSQL-documentatie zijn indexen een gebruikelijke manier om de prestaties te verbeteren, maar zorgen ze ook voor extra werk voor de database. Ze moeten dus verstandig worden ingezet.

Een eenvoudig voorbeeld. Deze query zoekt de zendingen van één klant in een bepaalde periode:

SELECT zending_id, status, geleverd_op
FROM zendingen
WHERE klant_id = 4711
  AND geleverd_op >= '2026-09-01'
ORDER BY geleverd_op;

Zonder index op deze kolommen leest de database de hele tabel. Met een samengestelde index op klant_id en geleverd_op springt hij direct naar de juiste rijen, en staan ze ook al in de goede volgorde:

CREATE INDEX idx_zendingen_klant_datum
  ON zendingen (klant_id, geleverd_op);

De tabel laat zien welke soorten indexen je het vaakst inzet en waarvoor.

Veeg opzij om de hele tabel te zien

Elke index maakt schrijven iets trager, omdat de database bij elke wijziging ook de index bijwerkt. Voeg daarom niet op elke kolom een index toe, maar alleen waar het uitvoeringsplan laat zien dat het nodig is.

Wanneer helpen caching en paginering?

Caching en paginering helpen als de query zelf al zo goed mogelijk is, maar te vaak of te veel tegelijk wordt uitgevoerd. Ze lossen een ander probleem op dan een index.

• Paginering. Haal alleen de rijen op die op het scherm passen, bijvoorbeeld 50 per keer. Een overzicht met 100.000 regels in één keer laden is traag in de database, over het netwerk en in de browser.
• Alleen de nodige kolommen. Vraag geen SELECT * op als je drie velden toont. Minder gegevens betekent minder lezen en minder versturen.
• Caching. Gegevens die vaak worden opgevraagd en zelden veranderen, zoals een lijst met landen of producten, kun je tijdelijk bewaren in het geheugen van de applicatie.
• Vooraf berekenen. Een rapport met totalen per maand hoeft niet elke keer opnieuw alle losse regels op te tellen. Je kunt de totalen periodiek berekenen en opslaan.

Tip: los eerst de query zelf op en voeg pas daarna caching toe. Caching op een trage query verbergt het probleem tot de cache leeg is, en dan wacht de eerste gebruiker alsnog.

Wat leerde de case van 30 seconden naar 30 milliseconden?

De case bij een klant in de logistiek laat zien dat de grootste winst vaak in één query zit. Een rapportage-query deed er 30 seconden over en draaide na de optimalisatie in 30 milliseconden. Dat is een factor 1.000 sneller. Voor de gebruikers betekende het dat een rapport niet meer traag laadde, maar direct op het scherm stond.

Welke techniek het meeste oplevert, verschilt per query. Daarom begint elke optimalisatie bij het uitvoeringsplan en niet bij een gok. Zonder meting voeg je makkelijk een index toe die niets doet, of herschrijf je een query die niet het probleem was.

De les voor andere bedrijven:

• Meet eerst. Het uitvoeringsplan wijst de trage stap aan.
• Pak de grootste eerst aan. Eén query met veel impact levert meer op dan tien kleine verbeteringen.
• Controleer na de wijziging. Kijk opnieuw naar het plan en de tijd, en houd het daarna in de gaten. Gegevens groeien door.

Wanneer schakel je een specialist in?

Een specialist schakel je in als de traagheid je werk merkbaar ophoudt en het uitvoeringsplan niet direct een duidelijke oorzaak laat zien. Denk aan rapportages die minuten duren, schermen die vastlopen bij drukte of een database die steeds meer serverkracht nodig heeft.

Bij ICT Productions kijken we eerst naar de query's met de meeste impact en het uitvoeringsplan. Daarna stellen we voor wat er moet veranderen: een index, een herschreven query, een aanpassing in de applicatie of beter onderhoud van de database. Meer over hoe we databases beheren lees je op de pagina databasebeheer. Zit het probleem in de applicatie zelf, dan helpt ons team voor maatwerksoftware.

Kies je voor een nieuwe applicatie, dan lees je in React vs Angular waar je op let bij de voorkant. Wil je weten wat er aan jouw database te verbeteren valt? Neem contact op of bel 010 261 40 06. Meer over ICT Productions lees je op de homepage.