SQL Performance tips & trucs (5-2-2010)

 

Vragen beantwoord ik tegen betaling van een uurtarief van € 60,- met een minimum van 1 uur

 

I N H O U D S O P G A V E

 

SQL Performance tips & trucs (5-2-2010)

1. Zo weinig mogelijk OR gebruiken

1.1 De grootste tabel als laatste

1.2 Liever geen SELECT *

1.3 Zo weinig mogelijk DISTINCT gebruiken

1.4 Pas op met UNION

1.5 Snelheid van operatoren

1.6 Gebruik indien mogelijk BETWEEN

1.7 BETWEEN boven IN

1.8 Vermijd indien mogelijk de SUBSTRING functie

1.9 Vermijd de combinatie NOT IN

1.10 IN of EXISTS

 

•  Zo weinig mogelijk OR gebruiken

Waarom? In dat geval gebruikt SQL meestal geen index en dat scheelt natuurlijk snelheid. Er zijn twee alternatieven: IN en UNION.

 

Voorbeeld OR vervangen door IN:

 

Met OR:

 

SELECT *

FROM klanten

WHERE klantcode = 1

OR klantcode = 2

OR klantcode = 3

 

Met IN:

 

SELECT *

FROM klanten

WHERE klantcode IN (1,2,3)

 

Voorbeeld OR vervangen door UNION:

 

Met OR:

 

SELECT *

FROM klanten

WHERE klantcode = 1

OR klplaats = ‘GRONINGEN'

 

Met UNION:

 

SELECT *

FROM klanten

WHERE klantcode = 1

UNION

SELECT *

FROM klanten

WHERE klplaats = ‘GRONINGEN'

 

•  De grootste tabel als laatste

De volgorde van de tabellen bij FROM kan effect hebben op de snelheid van verwerking. Plaats voor de zekerheid de grootste tabel achteraan.

 

•  Liever geen SELECT *

Vraag geen kolommen op die je niet nodig hebt. Bovendien brengt SELECT * met zich mee dat er geen indexen gebruikt worden wat ook voor een slechtere performance zorgt.

 

•  Zo weinig mogelijk DISTINCT gebruiken

Als er dubbele rijen verwijderd moeten worden, kan dat zeer tijdrovend zijn; de software moet dan elke rij met alle andere vergelijken. DISTINCT is zeker niet noodzakelijk in subqueries.

 

•  Pas op met UNION

Het UNION commando voert automatisch het equivalent van een SELECT DISTINCT uit, ook als er geen dubbele rijen zijn. Als je er zeker van bent dat er geen dubbele rijen zijn, kun je beter UNION ALL gebruiken.

 

•  Snelheid van operatoren

Bij WHERE bepalen de verschillende operatoren hoe snel een query wordt uitgevoerd. Vaak heb je geen keus, maar als je die wel hebt, geeft onderstaande volgorde van hoog naar laag de snelheid van uitvoering aan:

  • =
  • >, >=, <, <=
  • LIKE
  • <>  

Conclusie: gebruik zo veel mogelijk = en zo weinig mogelijk <>.

 

•  Gebruik indien mogelijk BETWEEN

Aansluitend op het vorige geniet het de voorkeur BETWEEN te gebruiken boven een constructie met >= en <=. Bij gebruik van BETWEEN doet SQL namelijk wel een beroep op een eventuele index op de betreffende kolom.

 

•  BETWEEN boven IN

Als je keus hebt, gebruik dan BETWEEN en geen. Bijvoorbeeld:

 

SELECT customer_number, customer_name
FROM customer
WHERE customer_number in (1000, 1001, 1002, 1003, 1004)

 

Is veel minder efficiënt dan:

 

SELECT customer_number, customer_name
FROM customer
WHERE customer_number BETWEEN 1000 and 1004

Gesteld dat er een index zit op customer_number dan zal een reeks nummer veel sneller gelokaliseerd worden met BETWEEN dan met IN.

 

•  Vermijd indien mogelijk de SUBSTRING functie

De SUBSTRING functie kan een scan van de hele tabel tot gevolg hebben in plaats van de eventueel aanwezige index te gebruiken.

Dus in plaats van:

 

WHERE SUBSTRING(column_name,1,1) = 'b'

 

Probeer :

 

WHERE column_name LIKE 'b%'

 

•  Vermijd de combinatie NOT IN

Gebruik één van de volgende alternatieven:

  • EXISTS of NOT EXISTS
  • IN
  • LEFT OUTER JOIN in combinatie met een NULL voorwaarde

• IN of EXISTS

Als je de keus hebt tussen het gebruik van IN of EXISTS, EXISTS zal gewoonlijk sneller zijn.