Kodulehed
[387] - Andmebaasid II (ITI0207) (sügis 2024)
Esiletöstetud Kiirvalik
Lisainfo Kõige olulisemate tegevuste kiirvalik. Failide saatmiseks valige Vastamine alt sobiv ülesanne.
Üldist
Materjalid
LisainfoMaterjalide kataloogid.
Värvilised mummud tähistavad hinnangulist kataloogide lugemise vajadust. Roheline - suurim, kollane - keskmine, punane või mummuta - väikseim
Isiklik
Lisainfo Info ainult Sulle - teised kasutajad seda ei näe
Abi
Lisainfo Võimalus küsida õppejõult abi (nagu foorum, kus saab küsida küsimusi ja kommenteerida vastuseid)
Mitmesugust
Abi / Kasutajatugi / Andmebaasi kavandamine

Avalikud küsimused ja vastused:

Küsimuste teemade nimekiri

Anonüümne (06.11.2023 11:25):
Kui veebipõhises andmebaasirakenduses on vaja esitada päringu tulemusi lehekülgede kaupa, siis kuidas oleks kõige parem vastavat päringut andmebaasis teha?
Vastus (09.11.2023 14:51): Sellest kirjutatakse pikemalt SIIN, SIIN ja SIIN.

Seal tuuakse välja kaks lahendust:
  1. OFFSET'i e nihke kasutamine (nihke meetod)
  2. Päringu tingimuse sellisel viisil seadmine, et OFFSET'i ei ole vaja kasutada (otsingu meetod).
OFFSET'i kasutamisel on mitmeid puuduseid, millest üheks on jõudluse pidev langus, mida kaugemaid lehekülgi tulemuse algusest kasutaja vaatab. Tegemist on ühe töökiiruse antimustri ilminguga.

Järgnev töökiiruse näide põhineb PostgreSQL'il ja katsetus on tehtud PostgreSQL 16-ga. Tabelis Hcv (tervishoiuasutuse külastus) on 1_000_000 rida. Soovin kõigepealt leida kõik külastused sorteerituna id väärtuse järgi kasvavalt ja väljastada andmed 10-rea suuruste portsudena.

Kõigi järgnevate lausete täitmisel loetakse andmebaasisüsteemi poolt primaarvõtme veerule (health_care_visit_id) loodud indeksit (index scan operatsioon), sest selle lehtedes on võtmeväärtused sorteeritud. Iga tabeli rea kohta loetakse indeksipuu leheks olevat plokki ja sealt leitud info alusel tabeli rea andmeid sisaldavat plokki.

SELECT *
FROM Hcv
ORDER BY health_care_visit_id
OFFSET 0
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.1 ms, loetakse 10 rida.

SELECT *
FROM Hcv
ORDER BY health_care_visit_id
OFFSET 10_000
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 2.6 ms, loetakse 10_010 rida.

SELECT *
FROM Hcv
ORDER BY health_care_visit_id
OFFSET 999_990
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 344 ms, loetakse 1_000_000 rida.

Seega nihke abil portsude leidmiseks otsib süsteem ka kõik need read, mis jäävad tulemuseks olevas ridade hulgast nihkest ettepoole. Seega, mida kaugemale tulemuste väljastamisega liikuda, seda aeglasemaks muutub päring.

Alternatiivne ja parema töökiirusega lahendus leiab vajaliku ridade hulga WHERE klausli abil. Ka järgnev lahenduse korral kasutatakse päringu täitmiseks primaarvõtmele loodud indeksit, kui sõltumata sellest, kas soovitav ports on tulemusteks oleva ridade hulga alguses või lõpus on lause täitmiseks loetavate andmete hulk ja seega ka lause täitmise kiirus ühesugune.

SELECT *
FROM Hcv
WHERE health_care_visit_id>0
ORDER BY health_care_visit_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.05 ms, loetakse 10 rida.

SELECT *
FROM Hcv
WHERE health_care_visit_id>999990
ORDER BY health_care_visit_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.03 ms, loetakse 10 rida.

Selle lahenduse korral peab portse sooviv tarkvara meeles pidama, millised olid kõige viimased andmed, mida see sai ning kasutama seda päringu tingimuses. Näiteks teise päringu korral teadis tarkvara, et kõige viimane visiit, mille andmeid see kuvas oli identifikaatoriga 999_990.

Sama põhimõte kehtib ka siis, kui sorteeritakse mõne muu veeru põhjal kui võtmeveerg.

SELECT *
FROM Hcv
ORDER BY from_date, patient_id
OFFSET 0
FETCH FIRST 10 ROWS ONLY;
Päringu täitmiseks toimub tabeli täielik läbiskaneerimine, sest pole indeksit, kus oleks samad veerud ja samas järjekorras kui ORDER BY klauslis. Lause täitmiseks kulub 112 ms.

SELECT *
FROM Hcv
ORDER BY from_date, patient_id
OFFSET 999990
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 545 ms.

Loon indeksi, kus on veerud, mida kasutatakse sorteerimisel ning need on samas järjekorras kui ORDER BY klauslis.
CREATE INDEX idx_hcv_date_patient ON Hcv (from_date, patient_id);

ANALYZE;

Peale seda kasutati eelnimetatud kahe lause täitmiseks indeksit (index scan). Esimese lause täitmiseks kulub 0.79 ms ja teise lause täitmiseks 1101 ms. Nii et indeksist oli kasu, kuid saaks veel paremini. Kui kasutada portsudeks jagamisel otsingu meetodit, siis on töökiirus palju parem. Ka mõlema järgneva lause täitmiseks kasutatakse eelnevalt loodud indeksit.

SELECT *
FROM Hcv
WHERE (from_date, patient_id)>('2000-10-13', 30017)
ORDER BY from_date, patient_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 0.139 ms.

SELECT *
FROM Hcv
WHERE (from_date, facility_id)>('2014-10-12', 95334)
ORDER BY from_date, facility_id
FETCH FIRST 10 ROWS ONLY;
Lause täitmiseks kulub 1.278 ms.

Hinda vastust:

Keskmine hinne : Pole veel hinnanguid!