Vaak volstaat het om een SQL query te schrijven die functioneel het juiste resultaat oplevert zonder dat de snelheid van de query een rol speelt, bijvoorbeeld als één record moet worden opgehaald van een tabel die in mutatiescherm van een applicatie moet worden gebruikt.

Vaak is de snelheid waarmee de SQL query een resultaat oplevert wel heel erg belangrijk, bijvoorbeeld als:

  • De query heel vaak wordt gebruikt
  • De query heel complex is waardoor een gebruiker, bijvoorbeeld bij het opstellen van een rapport, op het resultaat moet wachten
  • De query wachtrij in de database vaak vol is en er dus queries wachten totdat zij kunnen worden opgepakt door de database
  • Als iedere milliseconde telt. Een snelle website scoort bijvoorbeeld doorgaans hoger bij de zoekmachines zoals Google, Bing en Yahoo. (SEO)

De executietijd tussen een wel of niet geoptimaliseerde SQL query kan al gauw 2 tot 100 keer verschillen.
De kunst zit hem erin de juiste indexen te definieren op de kolommen en de query zo te schrijven dat deze gebruik kan maken van deze indexen.

Ik heb, door queries te optimaliseren, al heel vaak kunnen voorkomen dat er nieuwe dure hardware zou worden aangeschaft (die m.i. het probleem nog steeds niet optimaal zou oplossen) of dat een applicatie links zou worden gelegd door de gebruikers. Ik geef hieronder een aantal tips hoe je queries kunt versnellen. Zou je deze problematiek echter niet zelf willen of kunnen oplossen en bij een professional willen neerleggen dan kun je mijn contactgegevens op deze website vinden :) .

 

Maak queries sargable door een goede WHERE clause te gebruiken

Een query is sargable (Search ARGument Able) als de databaseserver in staat is om indexen te gebruiken ( er vanuit gaande dat er relevante indexen zijn aangemaakt). Als je een bepaalde naam opzoekt in de index van een telefoonboek dan spreekt het voor zich dat je heel gemakkelijk de naam kunt vinden als je de hele of de eerste letters van de achternaam kent. Dit wordt al een stuk moeilijker als je slechts het laatste deel van de achternaam kent. Zo werkt dit ook bij databases:

Sargable: Select * from Tabel1 where Achternaam = 'Janssen'
Niet Sargable: Select * from Tabel1 where Achternaam <> 'Janssen'
Sargable: Select * from Tabel1 where Achternaam like 'Jan%'
Niet Sargable: Select * from Tabel1 where Achternaam like '%ssen'

Dit wetende zijn sommige queries gemakkelijk sargable te maken zonder dat dit afdoet aan het doel van de query, bijvoorbeeld vergelijk de volgende query’s:

1) select * from Table1 where ID <> 0
2) select * from Table1 where ID > 0

De eigenschappen van het veld ID zijn:
- Geheel getal (integer)
- Positief getal (unsigned)
- bevat een index

Query 2 is sargable dus de databaseserver zal bij query 2 in staat zijn om de index te gebruiken en voor query 1 niet. Query 2 zal dus veel sneller performen dan Query 1. Omdat ID nooit negatief kan zijn is Query 2 een beter alternatief dan Query 1.

Gebruik de volgende operatoren in de WHERE-clause om de query sargable te maken (in volgorde van voorkeur):

  • =
  • <
  • >
  • <=
  • >=
  • EXIST
  • IS
  • IN
  • BETWEEN
  • LIKE ‘abc%’

Gebruik ZO MIN MOGELIJK de volgende operatoren in de where-clause:

  • IS NULL
  • <>
  • !=
  • !>
  • !<
  • NOT
  • NOT EXIST
  • NOT IN
  • NOT LIKE
  • LIKE ‘%abc’
  • LIKE ‘%abc%’
  • Functie op kolom
  • kolom1 = kolom1
  • kolom1 = kolom2

 

Maak queries sargable door de OR in de where-clause te vervangen door een UNION of UNION ALL

Een database kan uitsluitend een index gebruiken als alle velden in de where-clause die zijn gescheiden met een OR statement zijn opgenomen in één index. Natuurlijk is dit op te lossen door een nieuwe index aan te maken en hierin alle velden uit de where-clause op te nemen. Soms is dit niet verstandig, bijvoorbeeld als de table vaak wordt gemuteerd. In dat geval kan het vaak beter te zijn om de Query met de OR-statement(s) in de where-clause op te splitsen in meerdere query’s en een UNION te gebruiken, bijvoorbeeld:

1: Select Veld1 from Table1 where Veld2 = 1 or Veld3 = 3
2: Select Veld1 from Table1 where Veld2 = 1
UNION
Select Veld1 from Table1 where Veld3 = 3

Van Table1 weten we de volgende eigenschappen:
- Wel een index op Veld2
- Geen index op Veld3

Query 2 zal sneller performen dan Query 1 omdat Query 1 een OR operator gebruikt in de WHERE clause maar er geen gecombineerde index is op alle velden in de where clause. De databaseserver kan dan helemaal geen indexen gebruiken. In Query 2 kan de database server voor het eerste deel van de query wel de index op Veld2 gebruiken. De query kan nog sneller worden gemaakt door de UNION te vervangen door een UNION ALL. Dit voorkomt een ontdubbel-slag (equivalent van DISTINCT) in het resultaat.

 

Gebruik bij voorkeur UNION ALL in plaats van UNION als dit mogelijk is.

Zodra een UNION wordt gebruikt dan voert de database een ontdubbelslag over het resultaat, vergelijkbaar met een DISTINCT. Dit is uiteraard minder snel dan een UNION ALL.
Is het niet erg als het resultaat dubbele records bevat OF weet je zeker dat de individuele queries, die zijn verbonden met een UNION, unieke records opleveren dan kun je dus beter een UNION ALL gebruiken

 

Gebruik geen * in een SELECT als er slechts enkele velden in het resultaat benodigd zijn.

De query zal iets sneller zijn, maar ook het netwerkverkeer of het verkeer tussen database en applicatie zal hierdoor niet onnodig hoog zijn. Met name voor de grotere resultaatsets zal dit snelheidswinst opleveren.

 

Gebruik geen ORDER BY als dit niet echt nodig is.

Spreekt voor zich, maar wordt gek genoeg nog heel vaak onnodig gesorteerd. Een sorteeractie is zeer processorintensief, zeker als op het te sorteren veld geen (clustered) index geplaatst is.

 

Zodra een HAVING wordt gebruikt, beperk dan zoveel mogelijk rijen door een WHERE te gebruiken.

De database zal dan uitsluitend over de relevante records een GROUP BY uitvoeren en daarna pas de HAVING uitvoeren. Groepperen is zeer processorintensief

 

Zodra er GROUP BY is gebruikt zonder gebruik te maken van Aggregatie-functies (SUM, MAX, MIN, COUNT ,enz), gebruik dan liever een DISTINCT.

Bijvoorbeeld:

SELECT Veld1, Veld2 FROM Table1 GROUP BY Veld1, Veld2

Kan beter worden herschreven naar:

SELECT DISTINCT Veld1, Veld2 from Table1

Beiden leveren de zelfde resultaatset op echter een DISTINCT is minder processorintensief dan een GROUP BY.

 

Plaats indexen op de meest gevarieerde kolommen en noem deze kolommen zoveel mogelijk aan het begin van een WHERE-clause (AND operator).

Stel, er is een tabel PERSONEN die is gevuld met 10000 persoonsgegevens. Twee van de velden van deze tabel zijn gevuld met de naam en het gelacht van de persoon (NAAM en GESLACHT). Op beide kolommen is een index geplaatst.

Dan weten we dat:

  • De index op de kolom NAAM het meeste zal bijdragen aan een performance winst, immers de kolom NAAM zal veel diverser zijn dan de kolom GESLACHT
  • De where clause van een query beter “WHERE NAAM=’Janssen’ and GESLACHT=’m'” kan zijn dan “WHERE GESLACHT=’m’ and NAAM=’Janssen’”

 

Gebruik zo min mogelijk indexen op dynamische tabellen

Enerzijds verhoogt een index de snelheid van een WHERE, GROUP BY, DISTINCT, HAVING, JOIN, enz van een SELECT, UPDATE of DELETE query.
Anderzijds zal iedere wijziging op een tabel (INSERT, UPDATE of DELETE) iets langzamer gaan omdat met deze wijziging ook de index moet worden bijgewerkt. Zodra een tabel vaak gewijzigd wordt (in verhouding tot het aantal SELECTS) probeer dan het gebruik van indexen op deze tabel te beperken.

 

Voorkom onnodig netwerkverkeer.

  • Gebruik TOP in een query als slechts de eerste X resultaten belangrijk zijn, bijvoorbeeld als de applicatie slechts de eerste X resultaten in het scherm toont
  • Vraag niet meer velden in de SELECT query dan dat er door de applicatie gebruikt worden.
  • Als de applicatie slechts de eerste 200 karakters van een tekstveld in het scherm toont, vraag dan ook slechts de eerste 200 karakters op in de SELECT query

Een bijkomend voordeel is dat deze maatregelen:

  • het geheugengebruik van de databaseserver verlagen
  • het geheugengebruik van de applicatie(server) verlagen, immers de applicatie moet iets met de resultaatset doen.
  • het netwerk ontlasten voor andere gebruiker en/of applicaties

 

Voorkom onnodig geheugen gebruik van de databaseserver

  • Gebruik DROP om tijdelijke tabellen op te ruimen
  • Gebruik zo min mogelijk sub-query’s (SELECT statement in een SELECT, JOIN of WHERE) die grote een resultaatset opleveren. Gebruik dan liever een JOIN
  • Beperk het gebruik van JOINS alleen op de tabellen die nodig zijn in de query
  • Liever INNER JOIN dan LEFT JOIN of RIGHT JOIN en liever LEFT- of RIGHT JOIN dan een OUTER JOIN.

 

Werkt de applicatie met grote resultaatsets? Zorg voor een grote IO bandbreedte

Als de database en de applicatie zijn geinstalleerd op aparte servers en deze zijn aangesloten op een netwerk met een lage bandbreedte dan zullen grote resultaatsets relatief veel tijd nodig hebben om over het netwerk te transporteren. Er kan dan mogelijk veel snelheidswinst worden behaald door:

  • de applicatieserver en databaseserver op de zelfde hardware te draaien
  • de applicatieserver en databaseserver virtueel op de zelfde hardware te draaien
  • de bandbreedte tussen databaseserver en applicatieserver te vergroten

Ook de bandbreedte van het netwerk van een SAN (Storage Area Network) kan een zwakke schakel zijn.

 

Tot slot

Op mijn website Dotnet4all.com kan een cheat sheet worden gedownload met meer performance tips voor SQL Server ontwikkelaars of DBA’s. Deze Cheat Sheet kan worden uitgeprint op een dubbelzijdig A4-tje. Geplastificeerd is dit een heel handig hulpje dat een permanente plaats in de tas verdient.