Optimalizovanie SQL dotazov

ahojte, mam za ulohu optimalizovat jeden dotaz..mate nejake dobre rady, ktore by mi mohli pomoct? alebo comu sa mam vyhnut?

dakujem

Mozno by bolo dobre sem ten dotaz napisat, ak sa teda nemylim....

neviem, ci sa to moze sem davat ulohy ..sak dam to sem teda, ale vymazte to, ak by to bolo proti pravidlam...ja cem len cisto poradit, nakopnut, ze ako zacat

select st.st_id, test.te_id, 2006, now()
from stud_termni st, termin_hod_test test, termin_hod th, termin te
where st.th_id = test.th_id
and th.th_id = st.th_id
and st.pr_id = te.ter_pr_id
and th.th_ter_id = te.ter_id
and te.ter_id = 17;

treba vyskumat, ako sa pomeni plan vykonania dotazu a naklady na jeho vykonanie, bez zmeny vysledku selectu..fsetky varianty spojenia sa vyzaduju, plan sa ma graficky znazornovat..atd

Práve dnes som z toho niečo mal na prednáške z databáz, tak sa pozri na strany 22-25 (nejaké základné pravidlá sú na strane 25): http://www.dcs.fmph.uniba.sk/~plachetk/ ... 2008_4.pdf (prípadne si pozri aj predchádzajúce prednášky tu: http://www.dcs.fmph.uniba.sk/~plachetk/TEACHING/DB2008/ ).

jeej, vdaka, su tam nejake pravidla..este taka asi hlupa otazka :-X ..tam je taky strom s medzivysledkami..ako budem sledovat ci optimalizujem dobre? ze bude selekt efektivnejsi?

No, niektoré databázové rozhrania udávajú napr. čas, ktorý trvalo spracovanie dotazu (napr. phpMyAdmin pre MySQL). Pre veľmi veľké databázy to určite budeš cítiť aj bez toho. A ak si dobre skontroluješ, či dodržiavaš spomenuté pravidlá, tak by to malo byť lepšie.

a v oracli..?

Netuším, pozri nejakú dokumentáciu.

pouzil by som vnorene dotazy... takto to je hnusny kartezsky sucin s kopou porovnani...

dajme tomu ze tie tabulky maju nasledovny pocet zaznamov:

stud_termni - 100
termin_hod_test - 10
termin_hod - 50
termin - 100

tak tento tvoj dotaz vykona 100*10*50*100 porovnani co je 5000000

s vnorenymi dotazmi to je neporovnatelne menej

nechce sa mi bavit s tym tvojim tak tu mas lahsi priklad

-efektivne
select tab1.a, tab1.b from tab1
where tab1_id IN (select tab2_tab1_id from tab2 where tab2_nieco = hocico)

-pri vacsom mnozstve tabuliek a zaznamov je na prd
select tab1.a, tab1.b from tab1, tab2
where tab1_id = tab2_tab1_id
and tab2_nieco = hocico

myslím, že sa to nevykoná 5000000, pretože ak v prvom porovnaní je false a podmienka pokračuje iba AND, tak nemá prečo pokračovať.

a takto to vyzera ked sa k problemom vyjadruju ludia co tomu nerozumeju (no offence m@-nX )

To ktory sposob je efektivnejsi zistis az realnym vyskusanim konkretneho prikladu, podla toho aky QEP vytvori databaza. Vseobecne je ale CHYBA pouzivat IN. Pri previazani tabuliek davas databaze moznost pouzivat indexy. IN funguje ako OR medzi vsetkymi hodnotami. To je naozaj neefektivne.

takze spravny postup optimalizacie je analyzovat selecty, porobit indexy, potom pozriet ako databaza tie indexy pouziva a iba v pripadoch ked databaza indexy vyuzit nevie skusat hokusy pokusy (ako napriklad medzivybery dat do temporarnych tabuliek, pripadne "SELECT FROM SELECT"... ale IN-u sa vyhni vzdy ak je to aspon trochu mozne.

peace

Akurat si citam nasu prednasku o Optimalizacii (ale budeme to preberat az o mesiac asi)

Priebeh optimalizácie:
Parsovanie
Dekompozícia dotazu na poddotazy
Prepis dotazu pomocou operácií relačnej algebry
Konštrukcia logických plánov vykonania
Konštrukcia fyzických plánov vykonania
Nájdenie (sub-) optimálneho riešenia

je tam aj to Zistenie plánu vykonania - Oracle:
pomocou príkazu EXPLAIN PLAN
pomocou príkazu autotrace v SQL*Plus
použitím utilít SQL Trace a TKPROF

pri tych metodach spojenia je spomenute indexovanie (Index-scan), ako pisal Goodwill
a este Full-scan, Rowid-scan a Hash-scan

No..a uz len zistit, ako sa to fsetko robi (zo slideov toho clovek moc nepochopi bez vykladu)..ale aspon uz mam predstavu, ako postupovat. Zatial to nevyzera desivo.
Vedel by mi niekto blizsie vysvetlit to indexovanie? Ze ako sa robi presne? Vdaka

Co potrebujes vediet o indexoch? Cim sa riadit ked ich vytvaras, alebo ako ich databaza pouziva?

myslim, ze asi co najviac :-X ..stretla som sa s takymto niecim prvykrat v sql, tak nemam ponatia, ako na to, ako sa to robi, pouziva..ak by mal niekto trpezlivost to tu troska rozpisat, alebo hodit nejaky link, kde je to dobre vysvetlene

No, ak o tom vies az taketo nic, tak len par jednoduchych rad
Index databaze sluzi na to, aby vedela bez sekvencneho prehladavania tabulky zistit, kde ma hladat konkretne hodnoty (ked das WHERE podmienku, alebo v previazani).

Jednoducha pomocka pre vytvaranie indexov: vytvaraj indexy na stlpce, ktore pouzivas pri previazani tabuliek, alebo vo where podmienke.
Priklad: Ak mas tabulku userov a vyhladavas v nej podla user_name, daj index na tento stlpec. Ak mas tabulku napr forum a vyhladavas v nej podla kategorie a user_name, vytvor index na dvojicu kategoria + user_name.

Indexov na jednu tabulku mozes vytvorit aj viac. Ma to vyznam ak z nej vyberas data roznymi sposobmi a do podmienky davas ine stlpce. Ale pozor, pri vybere vie databaza pouzit iba JEDEN index pre tabulku. Vytvorenie indexu na kazdy stlpec teda nie je dobre riesenie (aj tak sa pouzije len jeden a zbytocne vela indexov spomaluje zapis).

aaha, indexy = kluce? to sa robi este pri vytvarani tabuliek, nje? pri optimalizacii sa teda uz len pracuje s indexami, tak?

indexy != kluce
pokial je index unikatny, tak je zaroven aj klucom, ale v tabulke mozes mat indexov viac, pricom kluc bude len jeden z nich.

a optimalizacia casto znamena prave vytvorenie indexu. praca s indexami je ale cisto vec databazy. ta sa na zaklade vlastnej strategie rozhodne kedy ktory index pouzije

ale môžeš to ovplyvniť
http://dev.mysql.com/doc/refman/5.0/en/index-hints.html

indexy sa teda robia klasicky pomocou creatu?
http://dev.mysql.com/doc/refman/5.0/en/ ... index.html

neviem si zatial dost dobre predstavit, ake indexy je potrebne spravit v tom mojom prikade, ako sa to robi prakticky v oracli

Princip vytvarania indexov:
Index nad tabulkou vytvor iba v pripade, ze sa nejedna o malu tabulku (nech ma minimalne 1000 zaznamov). Ak mas malo zaznamov, rychlejsie je pouzitie full-scanu nad tabulkou, ako pouzit index.

Vraj sa neoplati vytvorit index nad takymi stlpcami, ktore maju menej, ako 10% unikatnych hodnot, mne sa osvedcilo indexovat aj take.

Zindexuj vsetky stlpce, podla ktorych vyhladavas / joinujes. Teda ked mas v SELECT bla FROM blabla WHERE uaa = ueee; tak najrychlejsie bude, ked budes mat index nad uaa aj ueee.

Vzdy joinuj tak, ze zacnes s tabulkou, ktora ti vrati najmenej vysledkov. To znamena, ze ak chces najst vsetky studentky z 3. rocnika VSMU, tak najskor vyselectuj setkych studentov tretieho rocnika a az potom select podla pohlavia

Ak chces optimizovat select, zisti si, nad akou verziou Oracle databazy to budes spustat. Ak to je pred ver. 10, tak je optimizer lepsie nastavit na rules a optimizovat pomocou explain planu a modifikovania SELECTu (+ pripadne hinty) ale ak mas verziu aspon 10, tak staci vygenerovat nove statistiky a optimizer sa postara o efektivne vykonanie selectu.

este taka otazocka..neviem to nikde najst. Ked spravim zistenie planu vykonania (pomocou autotraceu alebo explain planu), tak mi vypise vsetky tie statistiky, plan hash value, operacie, query plan a podobne..lenze nevypise tzv. Total runtime... ako ho zistim? (ORACLE) dakujem

moze to byt ze SET TIMING ON?

Total runtime je reálny čas, ako dlho SELECT bežal. Zistíš ho tak, že ten SELECT spustíš.

no ano ale ako ho zobrazit? ked spustim select, tak tam ziaden total runtime neukaze

Niektoré aplikácie priamo vypíšu, aký bol execution time. Na úrovni DB môžeš použiť:

jj, uz som si overila, ze hentym sa to robi, dakujem...este taka otazocka..v jednej ulohe (k tomu selectu) mam menit tabulku, z ktorej pochadza ta podmienka so 17tkou..to znamena rozne indexy podavat tam? btw. indexy sa mozu vytvarat aj pre naplnene tabulky?