Andmebaaside ajaveeb

Järgnev katsetus on tehtud PostgreSQL (16) põhjal.

Andmebaasis on tabelid järgmise kontseptuaalse struktuuriga:

[Person]-1----------0..*-[Health_care_visit]

Tabelis Person on 70_000 rida.
Tabelis Health_care_visit on 1_000_000 rida.

Tabelis Health_care_visit on liitindeks, mille esimene veerg on person_id, ja millesse kuulub ka veerg from_date.

Ülesandeks on leida iga isiku kohta tema isiku id, eesnimi, perenimi, sünniaeg ja tõeväärtus, mis näitab, kas isikul on alanud viimase viie aasta jooksul vähemalt üks visiit (TRUE) või mitte (FALSE).

Kolm võimalikku lahendust.

Lahendus 1: SELECT klauslis on korreleeruv EXISTS alampäring

SELECT party_id, first_name, last_name, birth_date, EXISTS (SELECT *

FROM Health_care_visit AS hcv

WHERE hcv.patient_id=person.party_id

AND EXTRACT (YEAR FROM from_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE)-5 AND EXTRACT(YEAR FROM CURRENT_DATE)) AS has_recent_visits

FROM Person;

Lahendus 2: SELECT klauslis on korreleeruv alampäring, millega loendatakse isikuga seotud sobivate visiitide arv ja väljastatakse tõeväärtus TRUE, kui see arv on suurem kui 0.

SELECT party_id, first_name, last_name, birth_date, (SELECT Count(*)>0 AS reslt

FROM Health_care_visit AS hcv

WHERE hcv.patient_id=person.party_id

AND EXTRACT (YEAR FROM from_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE)-5 AND EXTRACT(YEAR FROM CURRENT_DATE)) AS has_recent_visits

FROM Person;

Lahendus 3: Kasutatakse kokkuvõttefunktsiooni Bool_or, mis tagastab ridade rühma kohta tõeväärtuse TRUE, kui vähemalt ühe rühma kuuluva rea puhul arvutatud tõeväärtus on TRUE.

SELECT party_id, first_name, last_name, birth_date, Bool_or(EXTRACT (YEAR FROM from_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE)-5 AND EXTRACT(YEAR FROM CURRENT_DATE)) AS has_recent_visits

FROM Person INNER JOIN Health_care_visit AS hcv

ON hcv.patient_id=person.party_id
GROUP BY party_id, first_name, last_name, birth_date;

Lahendus 4: Ühises tabeli avaldises (common table expression) leitakse hiljuti külastusi teinud patsientide identifikaatorid. Põhipäringus ühendatakse need andmed välisühendamist kasutades tabeliga Person - välisühendamist kasutatakse, et tulemuses oleks ka need isikud, kellel hiljutisi külastusi ei ole.

WITH recent_visitors AS (SELECT DISTINCT patient_id

FROM Health_care_visit

WHERE EXTRACT (YEAR FROM from_date) BETWEEN EXTRACT(YEAR FROM CURRENT_DATE)-5 AND EXTRACT(YEAR FROM CURRENT_DATE))

SELECT party_id, first_name, last_name, birth_date, CASE WHEN rv.patient_id IS NOT NULL THEN TRUE ELSE FALSE END AS  has_recent_visits

FROM Person LEFT JOIN recent_visitors AS rv

ON rv.patient_id=person.party_id;

Kasutasin lausete käivitamiseks EXPLAIN ANALYZE lauset. Sellisel juhul lause täidetakse, kasutajale tulemust ei näidata, kuid kasutaja näeb lause täitmisplaani (protseduuri, mille alusel andmebaasisüsteem lause täidab) ja täitmise aega.

Kõigil kolmel lausel oli täitmisplaan erinev.

Kõige kiiremini täideti lahendus 1 - 227 ms. Täitmiseks ei kasutatud indeksit. Andmebaasisüsteem vaatas järjest läbi kõik tabeli Person read ja iga rea korral vaatas ridu tabelist Health_care_visit, kuni leidis vaste.

Täitmiskiiruselt järgmine oli lahendus 4 - 267 ms. Täitmiseks ei kasutatud indeksit. Alampäringu täitmiseks loeti paralleelprotsesside poolt tabeli Health_care visit ridu ja kordused eemaldati sorteerimist kasutades. Saadud tabel ühendati tabeliga Person kasutades räsiväärtuste arvutamisel põhinevat algoritmi (hash join).

Täitmiskiiruselt järgmine oli lahendus 3 - 588 ms. Täitmiseks ei kasutatud indeksit. Andmebaasisüsteem ühendas tabelid Person ja Health_care visit kasutades räsiväärtuste arvutamisel põhinevat algoritmi (hash join).

Kõige aeglasem oli lahendus 2 - 688 ms. Täitmiseks kasutati indeksit. Andmebaasisüsteem vaatas järjest läbi kõik tabeli Person read ja iga rea korral loendas indeksi alusel kokku tabelis Health_care_visit olevate seotud ridade arvu.

Mõned üldised järeldused:

SQL ülesandele on tüüpiliselt mitu erinevat vastust.
Erinevate lahenduste täitmiskiirused võivad olla erinevad.
Andmebaasisüsteem otsustab ise, millal andmebaasis loodud indekseid päringute täitmiseks kasutada ja indeksi kasutamine ei pruugi alati olla kõige kiirem viis andmete ülesleidmiseks.

Hinda postitust:

Keskmine hinne : 5.0