[Timukas0]

On 4 tabelit:
1) recipe_recipes (recipe_id, recipe_name, ...)
2) recipe_ingredients (ingredient_id, ingredient_price, ...) ingredient_price ehk hind on ühe ühiku kohta
3) recipe_ingredient_mapping (map_recipe, map_ingredient, map_quanitity)
4) recipe_related_recipes (related_parent, related_child, related_required)

Ühesõnaga on komponentide tabel, igal komponendil oma hind. Siis on retseptide tabel, igal retseptil nimi ja muud andmeid. Iga retsept koosneb kindlast arvust ja kindla kogusega komponentides. Igal retseptil võib olla seotud retsept, mis on kas nõutud (related_required = 1) või valikuline. Mul on vaja saada kõik retsepti andmed ning kogu retsepti hind.
Hind = SUM(iga retsepti komponendi hind * komponendi kogus) + SUM(iga seotud ja nõutud retsepti hind).
Ise tulin sellise lahendusega lagedale, aga äkki keegi oskab paremini:

[mirko28]

See päring on praegu kujul:

[Timukas0]

GROUP BY oli tõesti puudu.

MYSQLil on olemas EXPLAIN käsk, aga see midagi kasulikku (mulle) ei ütlenud, siis kasutasin kiiruse võrdlemiseks lihtsalt kogu tabeli küsimist ja vaatasin, kaua aega läheb.
(ema X MuudHulgad) + (Lapsed X Muudhulgad) oli tõesti kiirem, aga parima tulemuse sain hoopis alampäringuga:

[serk]

Mõned vead:

Kõige hullem asi mida sa SQLi kirjutades teha saad on kasutada funktsiooni andmete toomiseks või siis sinu puhul siis select selecti sees - see on kõige aeglasem ja baasi koormavam viis üldse.

Explain Plan on arendaja parem käsi, õpi selgeks! Ma ei kujuta ette, kuidas ilma selleta üldse normaalseid päringuid kirjutada võimalik on ...

Ei tea küll mis MySQLi versiooni kasutad, kuid minu teada on MySQLis juba kasutusel Oraclega sarnasd analüütilised funktsioonid ja juba üsna pikka aega - väga võimsad vahendid päringute muutmiseks tunduvalt kiiremaks, jällegi, vii kurssi ennast nendega.

[mirko28]

[Timukas0]

MySQL versioon 5.1.36.
Uurisin natuke. Esimese postituse päring, kui kõik read küsida, (WHERE lõpust ära ja Group BY asemele) on 9 korda aeglasem kui minu järgmise postituse päring (phpMyAdmini näidatud aja põhjal). Põhjuseks oli "Copying to tmp table", millele kulus esimese päringu korral 90% ajast. Seega jääb õhku küsimus, et kas ikka on esimene päring parem (kui jah, siis miks)?

Mis puutub analüütilistesse funktsioonidesse, siis ma sain aru, et neid MySQLis ei ole, aga neid saab emuleerida. Samas ei näe, kuidas see peaks mind aitama.

[mirko28]

[Timukas0]

Antud lehel peab jälgima, millal räägitakse MySQL-ist ja millal muust. MySQLil ei ole OVER-klauslit. Pealegi sain ma aru, et analüütilised funktsioonid on mõeldud selleks, et GROUP BY-d kasutades ei läheks infot kaduma. Mul seda probleemi pole.

Väike teemavahetus. Kui komponentide (ingredient) hinda muuta, siis peaksid vanad hinnad ka alles jääma. Tegin uue tabeli hinnad veergudega komponent, hind, kuupäev (tegelikkuses pole pealkirjad päris samad). Komponentide tabelisse jätsin hinna veeru alles. Kui kasutaja komponendi hinda muudab, siis lisan uue hinna hindade tabelisse kuupäevaga ja muudan komponentide tabelis ka. Aga kuidas käituda, kui kasutaja soovib mitut hinda korraga muuta. Kas lihtsalt iga komponendi korral 2 päringut (lisada uus hind hindade tabelisse ja muuta hind komponentide tabelis) või teha midagi, mis nõuaks vähem päringuid.

[mirko28]

[Timukas0]

Ma just mõtlesin seda, kuidas PHPs andmebaasiga suhtlemine teha. Näiteks on kasutajal vaja 100 komponendi hinda muuta korraga. Kas teha php-ga kokku kokku 200 mysql_query-t (iga komponendi kohta 2), või siis näha vaeva ja vähendada vaja minevate päringute arvu.

[Fukiku]

Mul tekkis see küsimus, et kui juba sellist lahendust kasutatakse, kas siis ei oleks mõistlik hindade tabelisse tekitada veerud id, hind, kehtivuse_lopp ja komponentide tabelisse mitte hinda numbriliselt salvestada, vaid viidata kehtivale hinnale id kaudu? Andmete dubleerimine kahte tabelisse ei tundu hea mõte olevat andmebaasi disaini mõttes.

[mirko28]

MySql-is näib olema võimalik andmebaasi protseduure teha:
http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html

siis sul võiks ju olla selline üks andmebaasi protseduur, mida välja kutsud:

[Timukas0]

Võtsin kasutaja Fukiku ideest kinni. Tabelil hinnad nüüd neli veergu: id (auto increment), komponendi_id, hind, kuupäev (current timestamp) ja komponentide tabelis on veerus `hind` vastava hinna id. Paljude komponentide muutmisele pakuks siis sellise lahenduse: php-ga teen umbes sellise päringu (kuupäev t:

[Fukiku]

Ma päeval vastasin umbropsu ja lühidalt. Seega, mis halvasti, see uuesti

Ühesõnaga, mina jätaks su hindade tabeli nii, nagu ma enne soovitasin (id, komponent_id, hind, kuupäev) ja komponentide tabelisse viidet hinnale üldse ei panekski.

Siis esiteks oleks hinna lisamine lihtsam, piisaks ainult insert lausest hindade tabelisse (komponent_id väärtust sa ju lisamisel tead, muud aga pole vaja).

Hinna pärimiseks sobiks midagi taolist:

[mirko28]

Tere,
Hiljem saab olema sellise lahendusega andmebaas aeglane.
Ajaloo tabel saab olema Süsteemi kõige aeglasem tabel, ja seda peaksite tee siis kogu aeg join-idesse lisama. Lisaks jääb veel arusaamatuks, kuidas andmebaasilt vastust saada, millise rea salvestamine andmebaasi vea andis.
MySql-is polegi kollektsioone vist, tuleb komaliste koostada stringide näol, kui on kollektsioonide vajadus. Ma soovitaks teha andmebaasi protseduuri, mis kollektsiooni sisendiks võtaks, ja väljastaks, missugune rida vea andis. Arvan, et see protseduur oleks kiire ja hiljem toote jooksva hinna pärimine oleks ka normaalne.


---

Võib-olla siiski ei tulegi aeglane lahendus, mida pakute siin! Andke siis kunagi utlevikus teada, kas perfoomants on okei! MySql-is saab ka partitsioneerida tabeleid, seda võiks ajaloo tabeli jaoks uurida.

[Timukas0]

Fukiku, sinu kood kahjuks ei tööta. Toimiks kas selline:

[mirko28]

Hinnaveeru selline dubleerimine on lühidalt öeldes "kiire aga hallatamatu" lahendus. Hallatamatu on ta selles osas et tuleb hallata, et dubleeritud andmed oleksid süngis, aga seda on raske tagada. Kiire on see lahendus, kuna tegu on eelarvutusega. Andmebaasi indeksid on eelarvutuslik ja dubleeriv nähtus, mis saamuti tekitab ohu, et see süngist välja läheb, kuid me ei pea indekseid kuigi palju ise haldama, andmebaasimootor teeb seda ise, indeks on korras kogu aeg. Kuid dubleeritud Hinnaveerge süngis hoida on raske, varem või hiljem miski juhtub ja ongi üks kahest väärtusest erinev. Niiet "kiire aga raskelt hallatav" asi on üldiselt see dubleerimine. Sedalaadi dubleerimise lahenduse juurde tuleks ilmselt alles siis tulla, kui kõik muud variandid on ära proovitud, ja tõepoolest perfoomansit muul traditsioonilisemal viisil parandada ei saa. Samas minuarust selline dubleerimise häkk tundub teoorias huvitavana, võiks proovida praktiliselt.

Kui mul oleks veebileht, kus oleks 100 tekstikasi, siis võiks ju öelda, millises tekstikastis on vale väärtus, kus ma ise neist 100 kastist selle vale üles oskaks leida.

[Timukas0]

Põhimõtteliselt ma ei dubleeri hinna veergu. Komponentide tabelis on hetke hind ja hindade ajaloo tabelis on hindade ajalugu. See, et mingi komponendi ajaloo tabeli viimane kirje kattub kattub hetke hinnaga on kokkusattumus :d. Jätab võimaluse kasutada ka selliseid hindu, mida pole vaja või ei tohiks ajaloo tabelisse salvestada (kuigi hetkel sellist funktsionaalsust vaja ei lähe).

[mirko28]

Toote käesoleva hinna jaoks võiks sobida selline päring:

[Timukas0]

Hindade ajaloo tabeli struktuur on selline

[mirko28]

Foorumid ongi hea koht, kus saab programmeerimist õppida. Teine hea koht on töökoht, kus on hea mentor-töökaaslane, kellega lahenduste üle arutada, kuid sellist kaas-töötajat paljudes töökohtades ei leia.

--

Lisaks mõned aspektid andmete dubleerimise kohta, mida aastakümnetega välja kujunenud hea tava heaks ei kiida, aga mida siiski tehakse järgmiste põhjustega:

1. Kui juhtub olukord, et tabeli T üle ei taheta anda isikule S õigusi andmeid küsida, siis on üheks lahenduseks dubleerida andmeid andmebaasis, tuua/dubleerida veerg sinna tabelisse kuhu on ligipääs olemas.
2. Andmete küsimise operatsiooni perfoomansi tõstmine. Jääb ära kulukas join.
3. Andmebaasi tasemel andmete turvalisuse tagamine. Häkker ei saa muuta parooli-veergu niilihtsalt, kuna andmebaas takistab seda öeldes et see veerg on sõltuv ühest teisest dubleeritud veerust, referential integriti vms kaitseks andmemuudatust.

[serk]

[mirko28]

Ja veel ka neljas põhjus:

4. Andmebaasi peale ehitatud tarkvara on piirangutega, mis teeb dubleerimise ainuvõimalikuks lahenduseks.

Seega 4 juba, üritan veel siia tuua neid.

[Fukiku]

[Le Inc]

Pole küll MySQL aga Oracle (sarnased ).

Miks peale oracle service restarti suurest tabelist lugemise kiirus (select * from) langeb ~11 sekilt ~2 sekile? See on muidugi tore, aga küss selles miks ta päeva jooksul jälle 11 sekile maandub? Kas asi on mingites indeksites vms? Kuidas saaks asja "jäädavaks" teha. Ehk mõni spets teab.

[nemu]

Buffer cache saab muud jura täis?
Võid cache hinti proovida:

[serk]

[2korda2]

serk,
kui suuri süsteeme oled teinud? Niikaua, kui ülesandeks on Kalle Kusta pagaritöökoja 10 kuklit kolmele hulgimüüjale müüa, on kõik ilus ja lihtne. Ükspäev aga satud ülesande otsa, kus tabelites on miljoneid kirjeid ja klient ootab süsteemilt regeerimist sekundi jooksul. Siis võid ennast halliks optimeerida aga denormaliseerimisest ei pääse.

"ligipääs baasile" - on päris erineval tasemel ja meetodil ligipääsu võimaldamist. Üldjuhul välistele pooltele kuvatakse andmeid läbi view - selle definitsiooni saab muuta jooksvalt vastavalt vajadusele ilma, et teine pool peaks andmemudeli muudatuste tõttu andmevahetust muutma hakkama (lisaks on õiguste probleem sellega lihtsalt lahendatav). Kui teisel poolel on vaja andmeid lisada, siis selleks tehakse eraldi tabel või protseduur. Jällegi selleks, et väljast tulevad andmed oleksid võimalikult eraldatud muust andmemudelist ja liides ei sõltuks andmemudeli muudatustest. Ma olen näinud ka lahendust, kus sisuliselt kogu salvestus baasi käis läbi protseduuride (ma ei ütle, et see mõistlik oleks aga ka nii saab ja seal oli isegi vaieldav põhjus).

Le Inc,
Oracle (ja ka muud "suured" serverid) optimeerivad oma tööd jooksvalt. Üks päring võib seetõttu tõesti võtta erinevalt aega sõltuvalt sellest, millise plaani alusel server selle lahendab. Nii umbes 8 aastat tagasi olin ise tunnistajaks, kus Oracle baas vajas iga paari nädala tagant sunniviisilist statistika ümberarvutamist, sest suutis ennast jooksvalt "lolliks optimeerida". Mis siis lahendus oli, ei mäleta kahjuks.

[serk]

[2korda2]

Kui aru ei saanud, ju pole piisavalt keerulisi süsteeme teinud. Väide, et andmete dubleerimine on IGAL JUHUL paha ei päde lihtsalt.

[serk]

[2korda2]

EI! Ma ei pidanud silmas mat. viewsid vaid justnimelt andmemudeli denormaliseerimist! Küsimus ei ole aruandes - küsimus on töökuvade kiires toimimises. Kui suur aruanne sekundiga ei avane, siis see üldjuhul kedagi ei morjenda, kui aga tavaline töökuva 2 sekundit avaneb, siis on kasutaja õigustatult kuri. Kui korraga on kuval andmeid 5-7 tabelist ja igaühes on miljoneid kirjeid, siis kasulikum on paar veergu dubleerida ja saada tabelite hulk <=3 peale.

[serk]

Olgu, teeme rahu, kuna me ei tea üksteise tööstussektoreid, siis vaielda raske, ühele sobib üks, teisele teine.
Aga arvestades sellega, et käesolevas foorumis, ka käesolev topic, tehakse üldjuhul tavalisi veebilahendusi, siis nendel juhtudel ei küündi nende andmemaht eales tasemeni, mil tuleks hakata denormaliseerima

Aga nagu ennist öeldud, denormaliseerimist mina isiklikult ei poolda ja väldiks igal võimalikul viisil, kuna see tekitab siiski probleeme:
1) Andmete up-to-date hoidmine
2) Lisa kettaruum
3) Andmemudeli lisa keerukus ja insert, update, delete aeglustumine(FK;PM;Constraindid)
4) Arenduskulu tunduvalt kallim, nii baasi kui UI seisukohast.

Andmete kuvamise kiirus on tegelt lõputu probleem ning lähenemisviise on erinevaid, kes soovitab osta kõvemat rauda, kes süsteemi ümber kirjutada jne ... Ainuõiget teed polegi.

Aga oli tore vaielda Edu.

[Le Inc]

[2korda2]

Plaan vaja üle vaadata. Kas päring kasutab indekseid või laseb full scan? Kui indekseid ei kasuta, siis muuda kas päringut või lisa indeks, kuhu vaja. Kui group by vms sees pole, siis 4 sekundit sellise hulga juures on hiiglama palju (tingimusel, et sa ei lae üles mingit suurt "pildiinfot" vms).
Veel 1 asi mida kindlasti tähele panna - tarkvaraliselt (ehk päringuid optimeerides, mudelit kohendades) võid teinekord võita kiiruses tuhandeid kordi, riistvara annab ka väga heal juhul tubli suurusjärgu võrra vähem tagasi. Samas riistvara on tihtipeale odavam ja seetõttu ka lihtsama vastupanu tee Mul on endal jälle kogemus, kus üks üsna suur protseduur töötas esimeses "ah peaasi et töötab" versioonis ca 30 minutit. Pärast kahte päeva optimeerimist sai selle alla 20 sekundi peale. Seejuures andmemudelit ei muudetud (kui mitte arvestada protseduuri töö käigus juurde tekkinud ajutisi tabeleid). Riistvaraliselt poleks sellise tulemuseni lihtsalt jõudnud.

serk,
sry, ma olen hellaks tehtud igasugu "hiilgavate" lahendustega ja seetõttu kipun järsult reageerima. Mõte on ikkagi selles, et andmete dubleerimine suurte süsteemide korral on paratamatus. Jah, on kallim arendada ja hallata aga kui alternatiiviks on tatina veniv rakendus, siis lihtsalt pole muud võimalust. Samas tõsi ta on - siit foorumist abi otsiv tegelane tõenäoliselt sellist süsteemi ei tee. Peace!

[serk]

Hei

Esimene full scan käib sul 5.3 sekki, järgmine päring mis sul käib 1.7 sekki käib mälust. Paari tunni pärast on mälust päring minema lükatud ja tehakse järjekordselt full scan.
Tuunimise aitamiseks oleks hea kui saaksid postitada create tabel scriptid koos veergude kommentidega - data võin ise insertida. Ning peamine, päring mis seal peal käib - on vaja äriliseslt aru saada mida tahetakse näidata. Peale seda saab mõelda kuidas seda kõike tuunida. Explain plan ei teeks samuti paha.

Aga nagu eelmine postitaja ütles, siis tõenäoliselt korrektsed indeksid aitaks.

Näiteks:
1) Teha ta mat. view'ks kuhu esmalt täitagi ainult need 15 rida mida tahad näidata ning edaspidi kasutada fast refreshi
2) Indekseerida tabel
3) Tõmmatagi tabel jõuga mällu
4) jne ... väga palju erinevaid võimalusi

Aga ennem kui postitad tableite struktuure või datat, ole kindel et sa võid seda teha!

[kiiver]

[Le Inc]

Nõus. Indekseid pole, nüüd tegin. Võtsin ID (unikaalne nr igas reas) indeksi aluseks. Ilmselt on mul jah sql päring optimeerimata. Proovin ümber kõpitseda .. asi sai tehtud kunagi ammu, kui veel sql suurt midagi ei jaganud.

Kahjuks vist veebilehe põhised ~3 sekised vist jäävad. Seal on väga palju mitme tabeli vahelist suhtlemist, aga ehk annab midagi teha.

[Fukiku]

[mirko28]

[Le Inc]

Indexitega ID luges 650k rida kokku 0,57 sekki, ilma index'iteta 2,1 sekki. Töötab.

[2korda2]

Fukiku,
tabelis veerg ID peaks oma olemuselt olema PK ja seega automaatselt indekseeritud. Ilma PK-ta tabel on saadanast, nagu siin ühes teises teemas sai nenditud.
Aasta oli siis umbes 2002, kui huvi pärast sai testitud indekseerimata Sybase IQ baasi: 4M kirjega tabelist "group by" jms koledate tingimusega 100 rida tuli ~12 sekundiga (serveriks tavaline tolle aja lauaarvuti). Korralikult indekseeritult tavalised mootorid (Oracle, Sybase ASE) suutsid sama päringu läbi teha ca 5 sekundiga. Koos indeksitega oli IQ kordi kiirem aga need hinnad.... $15000 ainult ühe protsessoriga serveri litsentsi eest (kasutajad tulid veel juurde) oli vähestele tollal jõukohane. Kui tuli veel selgitada, et see ei ole mitte töökeskkonna vaid ainult andmelao jaoks, siis... Praeguseks peaks Eesti pinnal olema päris mitu IQ kasutajat (Elisa ja SEB tulevad nagu meelde).

[Fukiku]

[2korda2]

Jah, said õigesti aru. Päringus peab üldiselt kasutama indekseeritud veergu(sid). Samas kui mul on näiteks isikute tabel ja ma tahan kõiki meessoost isikuid, siis veerg SUGU, mis omab väärtusi M,N ei ole hea indekseerida - ei anna suurt midagi juurde. Üldiselt on mõtet indekseerida veergusid, kus on piisavalt palju erinevaid väärtusi ja ühtlasi selle veeru väärtusi kasutatakse päringute tegemisel. Kuupäev on tüüpiline näide.

[Le Inc]

Tundus et minu puhul oli kala tabelis endas. Nimelt 650k veeruga tabelist tegin lahjema 170k veeruga tabeli (enne kopeerimine, siis delete), aga lugemise kiirus langes paarkümmend %. Tegin uue tabeli vana põhjal ja kopeerisin info 1:1 üle. Nüüd loeb ~5..6x kiiremini. Ka veebileht on 3..4x kiirem.

Ilmselt oli oracle tabeli suurema hulga peale ära optimeerinud, seega ei tulnud ka 4x andmete vähendamine erilist kiiruse võitu. Muide hetkel ei oli ühtegi indeksit, töötab kenasti.

[serk]

Indeks on efektiivne siis kui sa pärid vastavalt indeksile suhteliselt väikest osa tabeli andmetest - ei mäleta peast enam seda rule of thumb % määra.
Üldiselt oleks tark peale indeksi loomist vaadata päring uuesti explain planiga üle.

Sinu tabeli probleemi kohta:
Oracle soovitab uuesti statistika arvutada kui 10% andmetest muutub ja kui selle peale query pange paneb. Defauldis peaks Oracle iga 24h tagant seda anyway ise tegema - võin siin muidugi hetkel eksida, pean järgi vaatama manualist.
Mälu järgi oli käsk selleks: DBMS_STATS.gather_table_stats -viitsimist on siis, proovi.

See 10% on muidugi "pseudo" väärtus ja pole kuldne reegel, kõik sõltub paljudest muudest teguritest. Mõnikord võib isegi väiksem data change käivitusplaani jumala metsa keerata ja vastupidi... Muudad, testid, muudad, testid, muudad, testid .... ja toodangus võib ikka asi pekkis olla

[nemu]

DBMS_STATS.gather_table_stats "deprecated", kuid lihtsamini meeles püsiv alternatiiv on:

[serk]

[nemu]

Kogu eelmine postitus viitab ju kiirele testile kui segaseks jäi.

[morgoth]