Stel je voor dat je een tool hebt die automatisch JPA en Hibernate performance problemen kan detecteren. Hypersistence Optimizer is dat gereedschap!
Inleiding
In dit artikel gaan we kijken hoe de SQL EXISTS operator werkt en wanneer je hem zou moeten gebruiken.
Hoewel de EXISTS operator al beschikbaar is sinds SQL:86, de allereerste editie van de SQL Standaard, ontdekte ik dat er nog steeds veel applicatie-ontwikkelaars zijn die zich niet realiseren hoe krachtig SQL subquery expressies eigenlijk zijn als het gaat om het filteren van een bepaalde tabel op basis van een voorwaarde die geëvalueerd is op een andere tabel.
Database tabelmodel
Laten we aannemen dat we de volgende twee tabellen in onze database hebben, die een one-to-many tabel relatie vormen. De student tabel is de ouder, en de student_grade is de kind tabel omdat het een student_id Foreign Key kolom heeft die verwijst naar de id Primary Key kolom in de student tabel.
De tabel student bevat de volgende twee records:
| id | first_name | last_name | admission_score ||----|------------|-----------|-----------------|| 1 | Alice | Smith | 8.95 || 2 | Bob | Johnson | 8.75 |
En, de student_grade tabel slaat de cijfers op die de leerlingen hebben gekregen:
| id | class_name | grade | student_id ||----|------------|-------|------------|| 1 | Math | 10 | 1 || 2 | Math | 9.5 | 1 || 3 | Math | 9.75 | 1 || 4 | Science | 9.5 | 1 || 5 | Science | 9 | 1 || 6 | Science | 9.25 | 1 || 7 | Math | 8.5 | 2 || 8 | Math | 9.5 | 2 || 9 | Math | 9 | 2 || 10 | Science | 10 | 2 || 11 | Science | 9.4 | 2 |
SQL EXISTS
Laten we zeggen dat we alle leerlingen willen krijgen die een 10 hebben gekregen in de wiskundeklas.
Als we alleen geïnteresseerd zijn in de student-identifier, dan kunnen we een query als deze uitvoeren:
SELECT student_grade.student_idFROM student_gradeWHERE student_grade.grade = 10 AND student_grade.class_name = 'Math'ORDER BY student_grade.student_id
Maar de toepassing wil de volledige naam van een student weergeven, niet alleen de identifier, dus hebben we ook informatie uit de student tabel nodig.
Om de student records te filteren die een 10 hebben voor wiskunde, kunnen we de EXISTS SQL operator gebruiken, zoals deze:
SELECT id, first_name, last_nameFROM studentWHERE EXISTS ( SELECT 1 FROM student_grade WHERE student_grade.student_id = student.id AND student_grade.grade = 10 AND student_grade.class_name = 'Math')ORDER BY id
Bij het uitvoeren van de query hierboven, zien we dat alleen de rij Alice wordt geselecteerd:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
De buitenste query selecteert de student rij kolommen waarin we geïnteresseerd zijn om terug te keren naar de client. De WHERE-clausule gebruikt echter de EXISTS-operator met een bijbehorende interne subquery.
De EXISTS-operator retourneert true als de subquery ten minste één record retourneert en false als geen enkele rij is geselecteerd. De database engine hoeft de subquery niet volledig uit te voeren. Als een enkele record wordt gematched, geeft de EXISTS operator true terug, en wordt de bijbehorende andere rij van de query geselecteerd.
De binnenste subquery is gecorreleerd omdat de student_id-kolom van de student_grade-tabel is gematcht met de id-kolom van de buitenste student-tabel.
SQL NOT EXISTS
Stellen we ons voor dat we alle leerlingen willen selecteren die geen cijfer lager dan 9 hebben. Hiervoor kunnen we NOT EXISTS gebruiken, dat de logica van de EXISTS operator ontkent.
Daarom geeft de NOT EXISTS operator true als de onderliggende subquery geen record retourneert. Als echter een enkel record door de binnenste subquery wordt gematcht, retourneert de operator NOT EXISTS false, en kan de uitvoering van de subquery worden gestopt.
Om alle student records te matchen die geen geassocieerde student_grade met een waarde lager dan 9 hebben, kunnen we de volgende SQL query uitvoeren:
SELECT id, first_name, last_nameFROM studentWHERE NOT EXISTS ( SELECT 1 FROM student_grade WHERE student_grade.student_id = student.id AND student_grade.grade < 9)ORDER BY id
Wanneer we bovenstaande query uitvoeren, kunnen we zien dat alleen het Alice record wordt gematcht:
| id | first_name | last_name ||----|------------|-----------|| 1 | Alice | Smith |
Cool, toch?
Online Workshops
Als je van dit artikel hebt genoten, dan ga je vast en zeker genieten van mijn komende 4-daagse Online Workshop!
- Hoog-Performance Java Persistence Online Workshop (4 uur x 4 dagen) tussen 19 en 22 april
Conclusie
Het voordeel van het gebruik van de SQL EXISTS en NOT EXISTS operatoren is dat de uitvoering van de binnenste subquery kan worden gestopt zolang er een overeenkomend record wordt gevonden.
Als voor de subquery een groot aantal records moet worden gescand, kan het stoppen van de uitvoering van de subquery zodra een enkel record is gevonden, de totale responstijd van de query aanzienlijk versnellen.
