Om data uit een database te halen kun je SQL gebruiken. Het is vrij snel te leren en ontzettend krachtig in gebruik. Daarnaast is de taal haast universeel: SQL wordt in veel applicaties gebruikt om data te ontsluiten, structureren en beheren. Wat we echter vaak zien gebeuren, bij zowel startende gebruikers maar ook bij meer ervaren analisten en ontwikkelaars, is dat de performance van SQL-operaties te wensen over laat. In dit artikel beschrijven we, een paar veelvoorkomende oorzaken voor een gebrek aan performance en leer je hoe je deze het beste kunt aanpakken.
Welk probleem
Zoals gezegd is SQL ontzettend handig voor het ontsluiten, bewerken en structureren van data. Misschien wel de belangrijkste taken zijn het gestructureerd opslaan van gegevens en het bepalen van de aanpak voor het ontsluiten van gegevens; het uitvoeren van een SQL-query. Zolang een SQL-query leidt tot resultaat heeft de gebruiker er geen omkijken meer naar, zo lijkt het.
Echter, bij het bepalen van de aanpak voor het ontsluiten van gegevens worden gegevens fysiek van disk gelezen, een relatief dure en trage actie. Het spreekt voor zich dat dit efficiënt moet gebeuren. Ook voor meer ervaren gebruikers is er geen reden om nu te stoppen met lezen. Het verwerken van miljoenen records in een datawarehouse is immers van een andere orde dan het updaten van één record. Onze ervaring is dan ook dat er op veel plaatsen winst te behalen is.
Heb ik een probleem?
Op het moment dat de doorlooptijd van een SQL-instructie ongewenst lang is, spreken we van een performance probleem. Bij performance problemen is het handig om te starten bij de aanpak die de software kiest voor het ontsluiten of bewerken van gegevens. Afhankelijk van jouw software is dit het execution plan of explain plan, zie hier een voorbeeld. Waar ik zeker naar kijk is of de schatting van de aantallen klopt. Zie je veel meer records dan verwacht, dan wordt er mogelijk een suboptimale aanpak gekozen. Dit kan komen door de structuur van de SQL-query, de joins tussen tabellen, de gebruikte sleutels, of iets anders. Sommige programma’s geven je zelfs hints voor het verbeteren van de performance. Hieronder geven we vijf voorbeelden en tips voor performance verbetering gebaseerd op onze dagelijkse praktijk.
SORT-operations
Wat SQL zo krachtig maakt is de mogelijkheid om te sorteren en groepen te maken. De mogelijkheid om snel gegevens over meerdere records te verzamelen en de resultaten te groeperen zit bijvoorbeeld onder de motorkap van veel dashboards. Het kent meerdere verschijningsvormen waarbij de GROUP BY en de DISTINCT het meest bekend zijn. De reden om hier oplettend te zijn is dat de hoeveelheid werk (voor de database) veel sneller toeneemt dan de toename van het aantal records; namelijk exponentieel. Dit maakt dat met de groei van de database het managen van de performance en de kosten van resources op termijn problemen kan opleveren. Eén oplossing is het verhogen van de rekenkracht of geheugen, tegenwoordig makkelijk te doen als je werkt in een cloud omgeving. Maar hiermee verschuift het probleem alleen maar naar andere resources en wordt het niet in de kern aangepakt.
Ter illustratie van die groei het volgende voorbeeld. Haal uit een spel kaarten alle klaveren, en leg deze zo snel mogelijk in volgorde van A, H, …, 3, 2. Deze activiteit bestempel ik als één eenheid werk. Schudt vervolgens het complete spel kaarten en doe hetzelfde per kleur. Vier keer zoveel kaarten, en toch meer dan vier keer het werk. Immers er moest eerst worden gefilterd op kleur. Je kunt je voorstellen hoeveel meer werk en complexer het wordt als je dit met twee of meer spellen kaarten probeert te doen.
Beoordeel dus of je de GROUP BY of DISTINCT echt nodig hebt. Een tip dit ik heel lang geleden kreeg, gaat over de kolommen en expressies in de GROUP BY-clause. Wanneer ik allerlei details wil selecteren, en ik selecteer ook de primaire sleutel (PK) van die details dan hoef ik alleen de PK in de GROUP BY op te nemen en kan ik de details met een (MIN) functie toch tonen. Bij dezelfde PK zijn alle details immers identiek. Het voordeel van deze aanpak is dat de (interne) werkruimte van de SQL-interpreter kleiner wordt met performance winst tot gevolg. Hieronder een voorbeeld, het klinkt ingewikkelder dan dat het is.
Indexes of Partitioneren
Een index is een behoorlijk efficiënte zoekstructuur gebaseerd op de ordening van de key (één kolom, of combinatie van meerdere kolommen). Een record opzoeken met een key kan in een klein aantal stappen, en leidt naar een referentie van het interne, fysieke adres van het gezochte record. Ideaal wanneer er een hele kleine set records moet worden opgezocht.
In datawarehouse-omgevingen is vaak sprake van grote(re) datasets. Zou de verwerking via een index lopen, dan wordt die vaak geraadpleegd met meerdere stappen per record. De overhead wordt hierdoor erg groot. Bij Oracle ligt het omslagpunt op 2% of lager. Hiermee wordt bedoeld dat een index loont wanneer er minder als 2% van alle records in de tabel worden geselecteerd. Om die reden ben ik zeker geen voorstander van het gebruik van indexen om queries te laten doen via indices.
Idealiter maak ik gebruik van het partitioneren van tabellen, het opdelen van de totale tabel op basis van voorkennis, eigenschap of predikaat zoals het jaar van inschrijving of een productgroep. Stop je alle rode knikkers in bak 1, alle groene in bak 2 dan weet je precies waar je moet zoeken als je een groene knikker zoek. De andere bakjes hoef ik dan niet te bekijken, wel zo efficiënt. Dit bewust overslaan van delen van een tabel wordt ook wel pruning genoemd. Gebruik dan bij voorkeur eigenschap te gebruiken voor de partitionering die vaak in joins terugkomt. Wanneer twee tabellen zijn gepartitioneerd op dezelfde eigenschap, dan kan de join (en de rest van de query) ook in stukjes worden gedaan (partition wise joining). Dit kan die exponentiële resourcebehoefte, genoemd bij GROUP BY tegengaan. Let op: pruning en partion wise joining kunnen niet of nauwelijks worden gebruikt wanneer er “non operationele” eigenschappen worden gebruikt, zoals datum of een hash.
Joins
Triviaal is het zorgen dat met het joinen van tabellen (en views e.a.) de volledige join-conditie wordt gebruikt. Dit betekent dat het join type wordt gespecificeerd, de tabellen en de sleutels. Dit is wellicht het beste voorbeeld om het totaal aantal records (ook wel de cardinaliteit) onder controle te houden. Om goed op de join-condities te kunnen letten ben ik een voorstander van ANSI-notatie. Dit betekent dat de JOIN wordt gespecificeerd in de FROM-clause en niet in een tabellenlijst met de join-condities in de WHERE-clause. Noodzakelijk gereedschap hierbij zijn de definities van primaire sleutels, de unieke sleutels en een goed datamodel waar de relaties tussen entiteiten dan wel tabellen duidelijk zijn.
Wantrouw gegenereerde query’s
Staar je bij performance problemen niet blind op de uitvoer van de query zelf, maar richt je ook op de omgeving. Ik heb op verschillende opdrachten gewerkt met een zogenaamde ETL-tool met client-server architectuur. En opvallend op al deze opdrachten was het voorschrift om “geen eigen SQL te schrijven”; de tool “schrijft” zelf de SQL. Met andere woorden: haal alle data naar de tool, die laadt het in het werkgeheugen en doet zijn ding. Vaak is dit niet efficiënt.
Een recent voorbeeld is een toepassing waarbij twee sets records worden gematched, beide twintig miljoen records in omvang. Voor elk record uit de ene set moet het record uit de andere set worden gevonden. Echter, de gegenereerde query kiest voor het sorteren van alle records, een dure tijdsintensieve oplossing. Door een eigen query te schrijven en gebruik te maken van bestaande indices en partitionering kan dit veel effectiever worden uitgevoerd.
Testen
Ben je aan het verbeteren en wil je de performance winst meetbaar maken, let dan goed op. Met grote regelmaat worden (potentiële) performance verbeteringen getest door te doorlooptijden te vergelijken van de oude en nieuwe code. Echter, de uitkomsten kunnen in hoge mate worden beïnvloed door onderstaande situaties:
- Andere processen kunnen resources claimen van de servers en het netwerk zodat de metingen onderling niet goed te vergelijken zijn;
- Tijdelijk opslag van gegevens in het geheugen (caching) waardoor veel leesopdrachten van de fysieke schijf (de fysieke IO) niet plaats hoeft te vinden.
- In het algemeen hecht ik meer waarde aan de verschillen in de explain plans, dan aan dit soort idle system testing. Wanneer het echt niet anders kan probeer ik alle SQL-versies tegelijk te starten en is de belasting van de machine min of meer gelijk.
Parallelle executie van SQL
Is er in de SQL-queries geen performance winst meer te behalen dan resteert er nog een optie: het parallel uitvoeren van een query. Het voordeel van het parallel uitvoeren van SQL is het reduceren van de doorlooptijd. De optie heeft een beperkte houdbaarheid, het werkt totdat er andere resources de bottleneck worden, zoals een schrijfbuffers of het geheugen. Parallelle uitvoer heeft echter ook nadelen, de korte termijn belasting van de server is hoger; er moet immers meer worden gecoördineerd en worden gecommuniceerd.
In het kort de tips voor performance winst op een rij:
- Beperk het gebruik van GROUP BY, DISTINCT of ORDER BY.
- Gebruik ANSI-joins, join geen onnodige tabellen en gebruik zo veel mogelijk inner joins.
- Schrijf je join-condities volledig uit in het FROM-statement en hou je totaal aantal records (cardinaliteit) onder controle.
- Gebruik indices wanneer ze goed werken; bij grotere sets geniet het de voorkeur tabellen te partitioneren (meerdere tabellen en met dezelfde eigenschappen)
- Wantrouw gegenereerde queries, zij bevatten vaak beperkte kennis van het datamodel en zijn suboptimaal.
- Overweeg de uitvoer van parallelle executie van SQL-queries. Deze zijn op korte termijn een grotere belasting voor de omgeving maar leveren wel sneller resultaat.
