Artem Yevtushenko | Builder Education |7 min read |April 24, 2019
SQL is een van de krachtigste data-handling tools die er zijn. In SQL Superstar geven we u bruikbare adviezen om u te helpen deze veelzijdige taal optimaal te benutten en prachtige, effectieve query’s te maken.
Als u geen data warehouse of aparte analytische database voor rapportage heeft, is de live productie database waarschijnlijk uw enige bron voor de laatste, up-to-date gegevens. Bij het bevragen van een productiedatabase is optimalisatie van groot belang. Een inefficiënte query zal de resources van de productiedatabase uitputten en een trage performance of verlies van service voor andere gebruikers veroorzaken als de query fouten bevat. Het is van vitaal belang dat u uw query’s optimaliseert voor een minimale impact op de performance van de database.
Stel eerst de business requirements vast
We hebben elders de best practices voor het definiëren van business requirements voor BI behandeld. Zorg er zeker voor dat u deze praktijken toepast bij het optimaliseren van SQL queries, waaronder:
- Identificeer relevante belanghebbenden. Zorg ervoor dat alle noodzakelijke partijen in de discussie zitten om uw query te ontwikkelen. Bij query’s op productie databases, zorg ervoor dat het DBA team is inbegrepen.
- Focus op bedrijfsresultaten. Geef de query een duidelijk en uniek doel. Het belasten van de productiedatabase voor verkennende of dubbele rapporten is een onnodig risico.
- Stel de discussie op voor optimale eisen. Definieer de functie en het bereik van het rapport door het beoogde publiek te identificeren. Dit zal de query richten op de tabellen met de juiste mate van detail.
- Stel goede vragen. Volg de 5 W’s: Wie? Wat? Waar? Wanneer? Waarom?
- Schrijf zeer specifieke eisen en bevestig ze met de belanghebbenden. De performance van de productiedatabase is te kritisch om onduidelijke of dubbelzinnige requirements te hebben. Zorg ervoor dat de eisen zo specifiek mogelijk zijn en bevestig de eisen met alle belanghebbenden voordat de query wordt uitgevoerd.
SELECTEER velden in plaats van SELECT *
Bij het uitvoeren van verkennende queries, gebruiken veel SQL ontwikkelaars SELECT * (lees als “selecteer alles”) als een steno om alle beschikbare gegevens uit een tabel te bevragen. Echter, als een tabel veel velden en veel rijen heeft, belast dit de database bronnen door het bevragen van veel onnodige gegevens.
Door het SELECT statement te gebruiken, zal de database alleen die gegevens bevragen die nodig zijn om aan de business eisen te voldoen.
Inefficiënt:
SELECT *
FROM Customers
Deze query kan ook andere gegevens ophalen die in de klantentabel zijn opgeslagen, zoals telefoonnummers, activiteitendata en notities van verkoop en klantenservice.
Efficiënt:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
Deze query is veel schoner en haalt alleen de benodigde informatie voor postadressen op.
Om een index van alle tabellen en veldnamen bij te houden, voert u een query uit vanuit een systeemtabel zoals INFORMATION_SCHEMA of ALL_TAB_COLUMNS (voor MS SQL Server, lees dit).
Ga vandaag nog aan de slag met SQL:
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 groeperen om zo verschillende resultaten te krijgen. Om dit doel te bereiken is echter een grote hoeveelheid verwerkingskracht vereist. Bovendien kunnen gegevens zodanig worden gegroepeerd dat ze onnauwkeurig worden. Om te voorkomen dat u SELECT DISTINCT gebruikt, selecteert u meer velden om unieke resultaten te verkrijgen.
Inefficiënt en onnauwkeurig:
SELECT DISTINCT FirstName, LastName, State
FROM Customers
Deze query houdt geen rekening met meerdere mensen in dezelfde staat die dezelfde voor- en achternaam hebben. Populaire namen zoals David Smith of Diane Johnson worden gegroepeerd, waardoor het aantal records onnauwkeurig is. In grotere databases zal een groot aantal David Smiths en Diane Johnsons ervoor zorgen dat deze query traag verloopt.
Efficiënt en nauwkeurig:
SELECT FirstName, LastName, Address, City, State, Zip
FROM Customers
Door meer velden toe te voegen, worden records zonder doublures geretourneerd zonder SELECT DISTINCT te hoeven gebruiken. De database hoeft geen velden te groeperen en het aantal records is nauwkeurig.
Zie Sisense in actie:
Verken Dashboard
Maak joins met INNER JOIN (niet WHERE)
Sommige SQL-ontwikkelaars geven er de voorkeur aan joins te maken met WHERE-clausules, zoals op de volgende manier:
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers, Sales
WHERE Customers.CustomerID = Sales.CustomerID
Dit type join creëert een Cartesian Join, ook wel een Cartesian Product of CROSS JOIN genoemd.
In een Cartesian Join worden alle mogelijke combinaties van de variabelen gemaakt. In dit voorbeeld, als we 1.000 klanten hadden met 1.000 totale verkopen, zou de query eerst 1.000.000 resultaten genereren, en dan filteren op de 1.000 records waar CustomerID correct is samengevoegd. Dit is een inefficiënt gebruik van databasebronnen, omdat de database 100x meer werk heeft gedaan dan nodig is. Cartesian Joins zijn vooral problematisch in grootschalige databases, omdat een Cartesian Join van twee grote tabellen miljarden of triljoenen resultaten kan opleveren.
Om te voorkomen dat je een Cartesian Join maakt, gebruik je INNER JOIN:
SELECT Customers.CustomerID, Customers.Name, Sales.LastSaleDate
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
De database genereert alleen de 1000 gewenste records waarbij de CustomerID gelijk is.
Sommige DBMS-systemen zijn in staat WHERE joins te herkennen en voeren ze automatisch uit als INNER JOINs. In die DBMS-systemen zal er geen verschil in prestatie zijn tussen een WHERE join en een INNER JOIN. INNER JOIN wordt echter door alle DBMS-systemen herkend. Uw DBA kan u adviseren over wat in uw omgeving het beste is.
Gebruik WHERE in plaats van HAVING om filters te definiëren
Het doel van een efficiënte query is om alleen de benodigde records uit de database te halen. Volgens de SQL Order of Operations worden HAVING statements berekend na WHERE statements. Als het de bedoeling is om een query te filteren op basis van voorwaarden, is een WHERE-statement efficiënter.
Bij wijze van voorbeeld: stel dat er in het jaar 2016 200 verkopen zijn gedaan, en we willen een query uitvoeren voor het aantal verkopen per klant in 2016.
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers.CustomerID, Customers.Name
HAVING Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
Deze query zou 1.000 verkooprecords uit de tabel Verkoop halen, vervolgens filteren op de 200 records die in het jaar 2016 zijn gegenereerd, en ten slotte de records in de dataset tellen.
In vergelijking daarmee beperken WHERE-clausules het aantal getrokken records:
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
Deze query zou de 200 records uit het jaar 2016 ophalen en vervolgens de records in de dataset tellen. De eerste stap in de HAVING-clausule is volledig geëlimineerd.
HAVING mag alleen worden gebruikt als je filtert op een geaggregeerd veld. In de bovenstaande query zouden we aanvullend kunnen filteren op klanten met meer dan 5 verkopen met behulp van een HAVING statement.
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
HAVING Count(Sales.SalesID) > 5
>>Gratis Starter Kit: Begin vandaag nog met onze gratis SQL-startkit
Gebruik jokertekens alleen aan het eind van een woordgroep
Bij het zoeken naar gegevens in platte tekst, zoals steden of namen, zorgen jokertekens voor de breedst mogelijke zoekactie.
Wanneer een leidend jokerteken wordt gebruikt, vooral in combinatie met een jokerteken aan het eind, moet de database alle records doorzoeken op een overeenkomst waar dan ook binnen het geselecteerde veld.
Zie deze query voor steden die beginnen met ‘Char’:
SELECT City FROM Customers
WHERE City LIKE ‘%Char%’
Deze query levert de verwachte resultaten op van Charleston, Charlotte en Charlton. Er worden echter ook onverwachte resultaten verkregen, zoals Cape Charles, Crab Orchard en Richardson.
Een efficiëntere query zou zijn:
SELECT City FROM Customers
WHERE City LIKE ‘Char%’
Deze query verkrijgt alleen de verwachte resultaten van Charleston, Charlotte en Charlton.
Gebruik LIMIT om queryresultaten te testen
Voordat u een query voor de eerste keer uitvoert, moet u ervoor zorgen dat de resultaten wenselijk en zinvol zijn door een LIMIT-instructie te gebruiken. (In sommige DBMS-systemen wordt het woord TOP door elkaar gebruikt met LIMIT.) De LIMIT-instructie retourneert alleen het aantal opgegeven records. Het gebruik van een LIMIT statement voorkomt dat de productie database wordt belast met een grote query, om er vervolgens achter te komen dat de query moet worden aangepast of verfijnd.
In de 2016 sales query van hierboven, zullen we een limiet van 10 records onderzoeken:
SELECT Customers.CustomerID, Customers.Name, Count(Sales.SalesID)
FROM Customers
INNER JOIN Sales
ON Customers.CustomerID = Sales.CustomerID
WHERE Sales.LastSaleDate BETWEEN #1/1/2016# AND #12/31/2016#
GROUP BY Customers.CustomerID, Customers.Name
LIMIT 10
We kunnen aan de hand van het voorbeeld zien of we een bruikbare data set hebben of niet.
Loop uw query tijdens daluren
Om de impact van uw analytische query’s op de productiedatabase zo klein mogelijk te houden, kunt u het beste met een DBA overleggen om de query op een daluur in te plannen. De query moet worden uitgevoerd wanneer het aantal gelijktijdige gebruikers het laagst is, wat meestal midden in de nacht is (3 – 5 uur ’s ochtends).
Hoe meer van de volgende criteria uw query heeft, des te waarschijnlijker is het dat hij ’s nachts wordt uitgevoerd:
- Selecteren uit grote tabellen (>1.000,000 records)
- Cartesian Joins of CROSS JOINs
- Looping statements
- SELECT DISTINCT statements
- Nested subqueries
- Wildcard zoekopdrachten in lange tekst of memo velden
- Meervoudige schema queries
Verzoek vol vertrouwen
Met deze tips in het achterhoofd (plus wat andere SQL tips en trucs op zak) zou je in staat moeten zijn om efficiënte, mooie query’s te maken die soepel lopen en de inzichten opleveren die uw team nodig heeft.
Krijg hier de gratis SQL-starterskit: