SQL-queryoptimalisatie: 15 technieken voor betere prestaties

6. Optimaliseer WHERE-clausules

De WHERE-clausule is essentieel in SQL-queries omdat we hiermee data kunnen filteren op basis van specifieke voorwaarden, zodat alleen relevante records worden geretourneerd. Dit verbetert de efficiëntie doordat minder data wordt verwerkt, wat erg belangrijk is bij grote datasets. 

Een juiste WHERE-clausule kan dus een krachtig hulpmiddel zijn bij het optimaliseren van de performance van een SQL-query. Enkele manieren waarop we hiervan kunnen profiteren:

• Voeg vroeg de juiste filtervoorwaarden toe. Soms is een WHERE-clausule goed maar niet genoeg. We moeten opletten waar we de clausule plaatsen. Zo veel mogelijk rijen vroeg in de WHERE-clausule wegfilteren kan helpen de query te optimaliseren.
• Vermijd het gebruik van functies op kolommen in de WHERE-clausule. Als we een functie op een kolom toepassen, moet de database die functie op elke rij in de tabel toepassen voordat hij kan filteren. Dit verhindert effectief indexgebruik.

Bijvoorbeeld, in plaats van: 

SELECT * 
FROM employees WHERE 
YEAR(hire_date) = 2020;

Gebruiken we beter: 

SELECT * 
FROM employees 
WHERE hire_date >= '2020-01-01' AND hire_date < '2021-01-01';

• Gebruik geschikte operatoren. We kiezen de meest efficiënte operatoren die aan onze behoeften voldoen. Zo is = doorgaans sneller dan LIKE, en specifieke datumbereiken zijn sneller dan functies zoals MONTH(order_date).

Dus, in plaats van deze query uit te voeren:

SELECT * 
FROM orders 
WHERE MONTH(order_date) = 12 AND YEAR(order_date) = 2023;

Kunnen we het volgende doen: 

SELECT * 
FROM orders 
WHERE order_date >= '2023-12-01' AND order_date < '2024-01-01';

7. Optimaliseer subquery’s

In sommige gevallen schrijven we een query en voelen we dat we dynamisch moeten filteren, aggregeren of data joinen. We willen geen meerdere queries doen; we willen het bij één query houden. 

Voor die gevallen kunnen we subquery’s gebruiken. Subquery’s in SQL zijn queries die genest zijn in een andere query, meestal in de SELECT-, INSERT-, UPDATE- of DELETE-statements. 

Subquery’s kunnen krachtig en snel zijn, maar ook performanceproblemen veroorzaken als ze niet zorgvuldig worden gebruikt. In het algemeen minimaliseren we het gebruik van subquery’s en volgen we een aantal best practices:

• Vervang subquery’s waar mogelijk door joins. Joins zijn over het algemeen sneller en efficiënter dan subquery’s.
• Gebruik in plaats daarvan common table expressions (CTE’s).  CTE’s scheiden onze code op in een paar kleinere in plaats van één grote query, wat veel leesbaarder is.

WITH SalesCTE AS ( 
             SELECT salesperson_id, SUM(sales_amount) AS total_sales 
             FROM sales GROUP BY salesperson_id ) 

SELECT salesperson_id, total_sales 
FROM SalesCTE WHERE total_sales > 5000;

• Gebruik niet-gecorreleerde subquery’s. Niet-gecorreleerde subquery’s zijn onafhankelijk van de buitenste query en kunnen één keer worden uitgevoerd, terwijl gecorreleerde subquery’s voor elke rij in de buitenste query worden uitgevoerd.

8. Gebruik EXISTS in plaats van IN voor subquery’s

Bij het werken met subquery’s moeten we vaak controleren of een waarde in een set resultaten voorkomt. Dat kan met IN of EXISTS, maar EXISTS is over het algemeen efficiënter, vooral bij grotere datasets.

De IN-clausule leest de volledige set subqueryresultaten in het geheugen voordat hij vergelijkt. De EXISTS-clausule stopt daarentegen met verwerken zodra er een match is gevonden. 

Hier is een voorbeeld van hoe je deze clausule gebruikt:

SELECT * 
FROM orders o
WHERE EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id AND c.country = 'USA');

9. Beperk het gebruik van DISTINCT

Stel dat we werken aan een analyse om een promotionele aanbieding te sturen naar klanten uit unieke steden. De database bevat meerdere bestellingen van dezelfde klanten. Het eerste waar we aan denken is de DISTINCT-clausule.

Deze functie is handig in bepaalde gevallen, maar kan veel resources vergen, vooral bij grote datasets. Er zijn een paar alternatieven voor DISTINCT:

• Identificeer en verwijder duplicaten tijdens het opschonen van data. Zo voorkom je dat duplicaten überhaupt in onze database terechtkomen.
• Gebruik GROUP BY in plaats van DISTINCT waar mogelijk. GROUP BY kan efficiënter zijn, vooral in combinatie met aggregatiefuncties. 

Dus in plaats van:

SELECT DISTINCT city FROM customers;

Kunnen we gebruiken:

SELECT city FROM customers GROUP BY city;

• Gebruik vensterfuncties. Vensterfuncties zoals ROW_NUMBER kunnen ons helpen duplicaten te identificeren en te filteren zonder DISTINCT te gebruiken.

10. Maak gebruik van databasespecifieke features

Wanneer we met data werken, doen we dat met SQL via een Database Management System (DBMS). Het DBMS verwerkt de SQL-commando’s, beheert de database en zorgt voor dataintegriteit en -beveiliging. Verschillende databasesystemen bieden unieke features die kunnen helpen bij het optimaliseren van queries. 

Databasehints zijn speciale instructies die we aan onze queries kunnen toevoegen om een query efficiënter uit te voeren. Ze zijn nuttig, maar moeten met voorzichtigheid gebruikt worden. 

In MySQL kan de hint USE INDEX het gebruik van een specifieke index afdwingen:

 SELECT * FROM employees USE INDEX (idx_salary) WHERE salary > 50000;

In SQL Server specificeert de hint OPTION (LOOP JOIN) de joinmethode: 

SELECT * 
FROM orders 
INNER JOIN customers ON orders.customer_id = customers.id OPTION (LOOP JOIN); 

Deze hints overschrijven de standaard query-optimalisatie en kunnen de prestaties in specifieke scenario’s verbeteren.

Aan de andere kant zijn partitioneren en sharding twee technieken om data in de cloud te verdelen. 

• Bij partitioneren verdelen we één grote tabel in meerdere kleinere tabellen, elk met een eigen partition key. Partition keys zijn doorgaans gebaseerd op de tijdstempels waarop rijen zijn aangemaakt of op de gehele waarden die ze bevatten. Wanneer we een query op deze tabel uitvoeren, leidt de server ons automatisch naar de gepartitioneerde tabel die bij onze query past. 
• Sharding lijkt hierop, behalve dat in plaats van één grote tabel in kleinere tabellen te splitsen, één grote database in kleinere databases wordt gesplitst. Elk van deze databases staat op een andere server. In plaats van een partition key stuurt een sharding key queries door naar de juiste database. Sharding verhoogt de verwerkingssnelheid omdat de belasting over verschillende servers wordt verdeeld. 

11. Monitor en optimaliseer databasestatistieken

Het up-to-date houden van databasestatistieken is belangrijk om ervoor te zorgen dat de query-optimizer weloverwogen, nauwkeurige beslissingen kan nemen over de meest efficiënte manier om queries uit te voeren. 

Statistieken beschrijven de dataverdeling in een tabel (bijv. het aantal rijen, de frequentie van waarden en de spreiding van waarden over kolommen), en de optimizer vertrouwt op deze informatie om de uitvoeringskosten van queries te schatten. Als statistieken verouderd zijn, kan de optimizer inefficiënte uitvoeringsplannen kiezen, zoals het gebruiken van de verkeerde indexen of kiezen voor een volledige tabelscan in plaats van een efficiëntere indexscan, wat leidt tot slechte queryprestaties.

Databases ondersteunen vaak automatische updates om statistieken accuraat te houden. Zo werkt in SQL Server de standaardconfiguratie statistieken automatisch bij wanneer een significant deel van de data verandert. Evenzo heeft PostgreSQL een auto-analyze-functie, die statistieken bijwerkt na een drempel aan datamodificatie. 

We kunnen statistieken echter handmatig bijwerken in gevallen waar automatische updates onvoldoende zijn of wanneer handmatige tussenkomst nodig is. In SQL Server kunnen we het commando UPDATE STATISTICS gebruiken om statistieken voor een specifieke tabel of index te verversen, terwijl in PostgreSQL het commando ANALYZE kan worden uitgevoerd om statistieken voor één of meer tabellen bij te werken. 

-- Update statistics for all tables in the current database
ANALYZE;

-- Update statistics for a specific table
ANALYZE my_table;

12. Gebruik stored procedures

Een stored procedure is een set SQL-commando’s die we in onze database opslaan zodat we niet steeds dezelfde SQL hoeven te schrijven. Je kunt het zien als een herbruikbaar script. 

Wanneer we een bepaalde taak moeten uitvoeren, zoals records bijwerken of waarden berekenen, roepen we simpelweg de stored procedure aan. Die kan invoer ontvangen, werk uitvoeren zoals data opvragen of wijzigen, en zelfs een resultaat teruggeven. Stored procedures helpen de boel te versnellen omdat de SQL vooraf gecompileerd is, waardoor je code schoner en makkelijker te beheren is. 

We kunnen in PostgreSQL als volgt een stored procedure maken:

CREATE OR REPLACE PROCEDURE insert_employee(
    emp_id INT,
    emp_first_name VARCHAR,
    emp_last_name VARCHAR
)
LANGUAGE plpgsql
AS $
BEGIN
    -- Insert a new employee into the employees table
    INSERT INTO employees (employee_id, first_name, last_name)
    VALUES (emp_id, emp_first_name, emp_last_name);
END;
$;

-- call the procedure
CALL insert_employee(101, 'John', 'Doe');

13. Vermijd onnodig sorteren en groeperen

Als dataprofessionals houden we ervan onze data te sorteren en te groeperen zodat we gemakkelijker inzichten kunnen krijgen. We gebruiken hiervoor vaak ORDER BY en GROUP BY in onze SQL-queries.

Beide clausules kunnen echter rekenintensief zijn, vooral bij grote datasets. Bij het sorteren of aggregeren van data moet de database-engine vaak een volledige scan uitvoeren en de data vervolgens ordenen, de groepen bepalen en/of aggregatiefuncties toepassen, meestal met algoritmen die veel resources vragen. 

Om de queries te optimaliseren, kunnen we enkele van deze tips volgen:

• Minimaliseer sorteren. Gebruik ORDER BY alleen wanneer het nodig is. Als sorteren niet essentieel is, kan het weglaten van deze clausule de verwerkingstijd sterk verkorten. 
• Gebruik indexen. Zorg er waar mogelijk voor dat de kolommen die betrokken zijn bij ORDER BY en GROUP BY zijn geïndexeerd. 
• Verplaats sorteren naar de applicatielaag. Als het kan, verplaatsen we de sorteerbewerking naar de applicatielaag in plaats van naar de database. 
• Pre-aggereer data. Voor complexe queries met GROUP BY kunnen we de data in een eerder stadium of in een gematerialiseerde view vooraggregeren, zodat de database niet telkens dezelfde aggregaties hoeft te berekenen.

14. Gebruik UNION ALL in plaats van UNION

Wanneer we resultaten uit meerdere queries in één lijst willen combineren, kunnen we de clausules UNION en UNION ALL gebruiken. Beide combineren de resultaten van twee of meer SELECT -statements wanneer ze dezelfde kolomnamen hebben. Ze zijn echter niet hetzelfde, en dat verschil maakt ze geschikt voor verschillende use-cases.

De clausule UNION verwijdert dubbele rijen, wat meer verwerkingstijd kost. 

Figuur: Union in SQL. Afbeeldingsbron: DataCamp SQL-Join cheat sheet.

Daarentegen combineert UNION ALL de resultaten maar houdt alle rijen, inclusief duplicaten, intact. Als we geen duplicaten hoeven te verwijderen, gebruiken we dus UNION ALL voor betere performance.

Figuur: UNION ALL in SQL. Afbeeldingsbron: DataCamp SQL-Join cheat sheet.

-- Potentially slower
SELECT product_id FROM products WHERE category = 'Electronics'
UNION
SELECT product_id FROM products WHERE category = 'Books';

-- Potentially faster
SELECT product_id FROM products WHERE category = 'Electronics'
UNION ALL
SELECT product_id FROM products WHERE category = 'Books';

15. Breek complexe queries op

Werken met grote datasets betekent dat we vaak complexe queries tegenkomen die lastig te begrijpen en te optimaliseren zijn. We kunnen dit aanpakken door ze op te delen in kleinere, eenvoudigere queries. Zo kunnen we prestatieknelpunten makkelijker identificeren en optimalisatietechnieken toepassen.

Een van de meest gebruikte strategieën om queries op te delen, is gematerialiseerde views. Dit zijn voorbewerkte en opgeslagen queryresultaten die snel toegankelijk zijn, in plaats van de query telkens opnieuw te berekenen. Wanneer de onderliggende data verandert, moet de gematerialiseerde view handmatig of automatisch worden ververst.

Hier is een voorbeeld van hoe je een gematerialiseerde view maakt en bevraagt:

-- Create a materialized view
CREATE MATERIALIZED VIEW daily_sales AS
SELECT product_id, SUM(quantity) AS total_quantity
FROM order_items
GROUP BY product_id;

-- Query the materialized view
SELECT * FROM daily_sales;

Conclusie

In dit artikel hebben we diverse strategieën en best practices voor het optimaliseren van SQL-queries verkend, van indexering en joins tot subquery’s en databasespecifieke features. Door deze technieken toe te passen, kun je de prestaties van je queries aanzienlijk verbeteren en onze databases efficiënter laten draaien.

Onthoud: SQL-queries optimaliseren is een continu proces. Naarmate je data groeit en je applicatie evolueert, moet je je queries blijven monitoren en optimaliseren om ze optimaal te laten presteren.

Om je begrip van SQL verder te verdiepen, raden we je aan de volgende bronnen op DataCamp te verkennen:

• SQL Basics Cheat Sheet
• Improving Query Performance in SQL Server
• Improving Query Performance in PostgreSQL