Performanceoptimalisatie van SQL-databases

Performanceoptimalisatie

van SQL-databases

Hoe prettig een bedrijfsinformatiesysteem ook werkt: vroeg of laat doemen database performanceproblemen op. Dit heeft consequenties voor de verwerking van gegevens in een datawarehouse, en het maken van analyses wordt er ook inefficiënt door. Verschillende nieuwe in-memorytechnieken kunnen de SQL-instructies, query’s dus, versnellen. Maar de oorzaak van het overgrote deel van performanceproblemen is het inefficiënt schrijven van query’s. Grote winst valt te behalen door gebruik te maken van nieuwere instructies, zoals Common Table Expressions en window-functies. Ook eindgebruikers zoals bedrijfsanalisten kunnen hun analyses versnellen door hun query’s efficiënter te schrijven.

“De oorzaak van het overgrote deel van performanceproblemen is het inefficiënt schrijven van query’s.”

In deze blog laten we zien hoe eindgebruikers – zoals bedrijfsanalisten- hun analyses kunnen versnellen door hun query’s efficiënter te schrijven. Deze techniek is ook van toepassing op andere terreinen van gegevensverwerking binnen een database. Door slimme query’s te schrijven kunnen verwerkingstijden sterk omlaag worden gebracht en kan de hardware de verwerkingstaken beter aan, zodat er minder storingen en uitval ontstaan.

Common Table Expressions (CTE’s)

Common Table Expressions (CTE’s) zijn te vergelijken met views in de database, alleen wordt de definitie ervan slechts eenmalig gebruikt en niet opgeslagen. Het biedt onder andere het voordeel dat in dezelfde SQL-instructie meerdere keren naar de resulterende tabel verwezen kan worden, terwijl de inhoud van die tabel maar één keer wordt opgehaald.

De kracht van CTE’s

Wat een CTE zo krachtig maakt, is dat het vroegtijdig al overbodige records kan uitfilteren, nog voordat ze bijvoorbeeld in een JOIN-instructie moeten worden geëvalueerd. Dit kan grote performancewinst opleveren. Een CTE wordt geschreven in de vorm van een WITH-instructie, zoals het voorbeeld hieronder laat zien:

Meer weten?
Bel met Jeroen Breugelmans
+31 (0) 655 836 540

Window-functies

De meeste performance winst voor query’s wordt echter behaald door het gebruik van window-functies. Window-functies maken deel uit van de ANSI SQL standaard. Het zijn krachtige aggregatiefuncties binnen de SQL-taal die vaak vele malen sneller werken dan de traditionele methode van groeperen en aggregeren. Ook de bestaande aggregatiefuncties als COUNT() en SUM() zijn als window-functie beschikbaar.

Window-functies evalueren in het geheugen van de database, wanneer de benodigde data al is ingelezen in het geheugen, maar nog voordat andere operaties als ‘uniek maken’, ‘projecteren’ en ‘sorteren’ plaatsvinden. Ze worden vaak gebruikt in combinatie met CTE’s. Hieronder staat allereerst een traditionele GROUP BY query. Deze wordt daarna vergeleken met de versie met window-functies:

Window-functies schrijven

Window-functies schrijven we door na de functienaam het sleutelwoord OVER in te typen en vervolgens optioneel PARTITION BY en vaak, maar niet altijd verplicht, ook ORDER BY. Als we de window-functie COUNT() gebruiken, doen we dat op de manier zoals hieronder is voorgedaan. Merk op dat we ook de window-functie ROW_NUMBER() gebruiken. Dit doen we om te laten zien waar het record zich in het window bevindt en om in de WHERE-clausule het aantal geretourneerde records te beperken:

Verschillen tussen traditionele functies en window-functies

Wat opvalt aan het resultaat van de versie met de window-functie, is dat het resultaat helemaal niet gegroepeerd is zoals in de traditionele versie. Wat we zien, is dat de window-functies inderdaad alleen worden toegepast op een window in het queryresultaat en er geen andere operaties zoals GROUP BY nodig zijn. We zien ook dat we een window kunnen opdelen in partities, net zoals we een traditionele query met GROUP BY kunnen groeperen. Tot slot valt op dat er een ROW_COUNT() window-functie is die het regelnummer teruggeeft van de regel waarop het zich binnen de window-partitie bevindt.

Volgorde van queryverwerking

Omdat de window-functies rechtstreeks in het geheugen van SQL Server plaatsvinden, zijn er geen extra fysieke read-operaties nodig om de bewerkingen uit te voeren. Dit versnelt in veel gevallen de uitvoering van de query. Window-functies worden ongeveer halverwege het queryverwerkingsproces uitgevoerd. De volgorde van queryverwerking is als volgt:

FROM, JOIN
WHERE
GROUP BY
aggregatiefuncties
HAVING
window-functies
SELECT
DISTINCT
UNION, INTERSECT, EXCEPT
ORDER BY
OFFSET
LIMIT, FETCH, TOP

Omdat window-functies middenin het queryproces worden aangeroepen, betekent het ook dat ze alleen in een SELECT, ORDER BY en latere verwerkingsslagen kunnen worden gebruikt, niet in een FROM, WHERE of GROUP BY clausule. Als dat toch nodig zou zijn, is het aan te raden een CTE te gebruiken en daarna pas een SELECT statement met de window-functie erin, net zoals in ons voorbeeld.

Groeperen van het queryresultaat

Er zijn verschillende manieren om het queryresultaat net zo gegroepeerd weer te geven als in de traditionele vorm. In ons voorbeeld kunnen we dat doen door de clausule WHERE p.RowNumber < 4; te vervangen door WHERE p.RowNumber = 1;.

De lijst van window-functies is uitgebreid. Het beperkt zich niet tot tellingen, het kan ook een resultaat uit een vorig of volgend record ophalen, een rangorde aangeven, de eerste of de laatste waarde binnen de partitie teruggeven, en meer.

Vaak is het nodig om een eenduidige sorteervolgorde binnen deze functies aan te geven, zodat de volgorde van de records altijd voorspelbaar is. In enkele gevallen is dit niet relevant, maar moet vanwege de syntax van de functie toch een sorteerexpressie worden opgegeven. In dat geval kan een subquery worden doorgegeven die een constante waarde retourneert, zoals (SELECT 1) of (SELECT NULL).

Conclusie

Iedere organisatie die werkt met data krijgt te maken met database performance issues, hetzij voor verwerken van gegevens of het maken van analyses. Window-functies helpen enorm bij het versnellen van veel query’s, omdat zij op een efficiënte plek tijdens het queryproces worden uitgevoerd.

Wie al bekend was met window-functies, zal mogelijk verbaasd zijn over de performancewinst die wordt gehaald als wordt overgestapt op SQL Server 2019. Deze versie van SQL Server kent een nieuwe ‘batch mode’ manier van werken. Veel aggregatiefuncties profiteren dan van het feit dat het totale aantal records in een groep (of partitie om in termen van window-functies te blijven) niet meer van tevoren hoeft te worden berekend, bijvoorbeeld om een rang of gemiddelde uit te rekenen. Dit is dus nog een extra reden om over te stappen op SQL Server 2019, of SQL Azure.