Maak uw SQL-query's performant

Maak uw SQL-query’s performant – Deel 1

SQL is een van de krachtigste tools voor gegevensverwerking die er zijn. Hieronder vind u praktisch advies om u te helpen het meeste uit deze veelzijdige taal te halen en prachtige, effectieve query’s te maken.

Als u geen datawarehouse of analytische database voor rapportage heeft, is de productiedatabase waarschijnlijk uw enige bron voor de up-to-date gegevens. Bij het opvragen van een productiedatabase is optimalisatie de sleutel. Een inefficiënte query zal de bronnen van de productiedatabase leegmaken en leiden tot trage prestaties of verlies van service voor andere gebruikers als de query fouten bevat.

Het is van essentieel belang dat u uw query’s optimaliseert voor een minimale impact op de databaseprestaties.

1. SELECT veldnamen in plaats van SELECT *

Bij het uitvoeren van verkennende query’s gebruiken veel ontwikkelaars SELECT * (lees als “alles selecteren”) als een afkorting om alle gegevens uit een tabel te doorzoeken. Als een tabel echter veel velden en veel rijen heeft, belast dit de database door veel onnodige data op te vragen.

Het gebruik van de SELECT-instructie zal de database verwijzen naar het opvragen van alleen de data die u nodig hebt om aan de zakelijke vereisten te voldoen. Hier is een voorbeeld.

Inefficiënt:

SELECT * FROM Clients

Deze zoekopdracht kan andere gegevens opleveren die ook in de klantentabel zijn opgeslagen, zoals telefoonnummers, activiteitsdatums en notities van verkoop en klantenservice.

Efficiënt:

SELECT FirstName, LastName, Address, City, State, Zip FROM Clients

Deze zoekopdracht is veel overzichtelijker en haalt alleen de vereiste informatie op.

2. Vermijd SELECT DISTINCT

SELECT DISTINCT is een handige manier om duplicaten uit een query te verwijderen. SELECT DISTINCT werkt door alle velden in de query te GROUPEREN om duidelijke resultaten te creëren. Om dit doel te bereiken is echter een grote hoeveelheid verwerkingskracht vereist. Bovendien kunnen gegevens zodanig worden gegroepeerd dat ze onnauwkeurig zijn. Om het gebruik van SELECT DISTINCT te vermijden, selecteert u meer velden om unieke resultaten te creëren.

Inefficiënt en onnauwkeurig:

SELECT DISTINCT FirstName, LastName, State FROM Clients

Deze zoekopdracht houdt geen rekening met meerdere mensen in dezelfde staat met dezelfde voor- en achternaam.

Populaire namen zoals Jos Verlinden of An Peeters worden gegroepeerd, waardoor een onnauwkeurig aantal records ontstaat. In grotere databases zorgt een groot aantal Jos Verlindens en An Peeters ervoor dat deze query langzaam wordt uitgevoerd.

Efficiënt en nauwkeurig:

SELECT FirstName, LastName, Address, City, State, Zip FROM Clients

Door meer velden toe te voegen, werden dubbele records geretourneerd zonder SELECT DISTINCT te gebruiken. De database hoeft geen velden te groeperen en het aantal records is nauwkeurig.

3. Maak een JOIN met INNER JOIN en niet WHERE

Sommige ontwikkelaars geven er de voorkeur aan om JOINs te maken met WHERE-clausules, zoals de volgende:

SELECT Client.ClientID, Client.Name, Sales.LastSaleDate
FROM Client, Sales
WHERE Client.ClientID = Sales.ClientID

In een JOIN worden alle mogelijke combinaties van de variabelen gemaakt. Als we in dit voorbeeld 1.000 klanten hadden met 1.000 totale verkopen. Dan zou de zoekopdracht eerst 1.000.000 resultaten genereren en vervolgens filteren op de 1.000 records waarbij ClientID correct is samengevoegd. Dit is een inefficiënt gebruik van de database. Aangezien de database 100x meer werk heeft gedaan dan nodig was. JOINS zijn vooral problematisch in grootschalige databases, omdat een JOIN van twee grote tabellen miljarden of triljoenen resultaten kan opleveren.

Gebruik in plaats daarvan INNER JOIN om het maken van JOIN te voorkomen:

SELECT Client.ClientID, Client.Name, Sales.LastSaleDate
FROM Client
INNER JOIN Sales ON Client.ClientID = Sales.ClientID

De database zou alleen de 1.000 gewenste records genereren waarbij ClientID gelijk is.

Sommige DBMS-systemen kunnen WHERE-joins herkennen en deze automatisch uitvoeren als INNER JOINs. In die DBMS-systemen is er geen verschil in prestatie tussen een WHERE-join en INNER JOIN. Het INNER JOIN sleutelwoord wordt echter door alle DBMS-systemen herkend. Uw DBA zal u adviseren welke het beste is in uw omgeving.

4. Gebruik WHERE in plaats van HAVING om filters te definiëren

Het doel van een efficiënte query is om alleen de vereiste records uit de database te halen. Volgens de SQL Order of Operations worden HAVING-instructies berekend na WHERE-instructies. Als het de bedoeling is om een query te filteren op basis van voorwaarden, is een WHERE-instructie efficiënter.

Laten we bijvoorbeeld aannemen dat er in 2021 200 verkopen zijn gedaan en we willen vragen naar het aantal verkopen per klant in 2021.

SELECT Client.ClientID, Client.Name, Count(Sales.SalesID)
FROM Client
INNER JOIN Sales ON Client.ClientID = Sales.ClientID
GROUP BY Client.ClientID, Client.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2021# AND #12/31/2021#

Deze query haalt 1.000 verkooprecords uit de tabel Sales, filtert vervolgens op de 200 records die in het jaar 2021 zijn gegenereerd en telt uiteindelijk de records in de dataset.

Ter vergelijking: WHERE-clausules beperken het aantal records dat wordt opgehaald:

SELECT Client.ClientID, Client.Name, Count(Sales.SalesID)
FROM Client
INNER JOIN Sales ON Client.ClientID = Sales.ClientID
WHERE Sales.LastSaleDate BETWEEN #1/1/2021# AND #12/31/2021#
GROUP BY Client.ClientID, Client.Name

Deze query haalt de 200 records uit het jaar 2021 en telt vervolgens de records in de dataset. De eerste stap in de HAVING-clausule is volledig geëlimineerd.

Het HAVING sleutelwoord mag alleen worden gebruikt bij het filteren op een geaggregeerd veld. In de bovenstaande zoekopdracht konden we bovendien filteren op klanten met meer dan 5 verkopen met behulp van een HAVING-instructie. Maak uw SQL-query’s performant – Deel 1.

Met deze tips in gedachten en enkele andere SQL-tips en -trucs die ik nog ga maken, zou je in staat moeten zijn om efficiënte, mooie query’s te bouwen die soepel zullen verlopen. Maak uw SQL-query’s performant – Deel 1.

Comments are closed.

Maak uw SQL-query’s performant – Deel 1