DevProTalk - mySQL view

DevProTalk (http://www.devprotalk.com/index.php)

- SQL baze podataka - Sponzor: Baze-Podataka.net (http://www.devprotalk.com/forumdisplay.php?f=10)

- - mySQL view (http://www.devprotalk.com/showthread.php?t=5117)

Povodom priče o stored procedures, setih se da upitam nešto što odavno želim da proverim: koliko vas koristi VIEW u mySQL-u ?

Ja ne koristim. Jednom sam imao porebu za pogledima ali tada ih nije bilo u MySQL-u.

Inace njihova upotreba je veoma korisna kod nekih racunskih operacija koje se izvrsavaju nad vise tabela, tipican primer neke profakture i slicno.

Mozete i da navedete primere u kojima ste ih koristili, i gde su pogledi veoma korisni.

Ja ne koristim MySQL ali koristim view-ove.

Isti slučaj ovde.

a) ponekad je malo lakše ili preglednije napraviti potrebni view nego pisati upit direktno nad tablema (JOIN, JOIN..). Verovatno se malčice gubi na brzini, ali bože moj.
b) nekad jednostavno nije dozvoljeno drugačije nego da se koristi view, zbog sigurnosti (različiti korisnici [=korisnik u smislu naloga koji se koristi za povezivanje do baze, ne krajnji korisnik] imaju mogućnost da samo vrše upite nad određenim pogledima, itd)

Ja bih ih verovatno koristio da mogu da ograničim app na MySQL5 (što trenutno ne mogu). Uštedelo bi mi dosta logike u PHP kodu, upiti bi se brže izvršavali jer bih mogao da za specifične stranice izvlačim samo specifične stvari (ORM uglavnom izvlači više nego što mu treba) itd.

koristim tu i tamo, uglavnom retko

nisam jos seo i procitao PROs i CONs do kraja (u nacelu mi je jasno sve, al treba videti detaljnije)

koristim ih na jednim rucno pravljenim statistikama, gde bi mi select bio spor, a treba mi cesto i mora biti uvek up-to-date

Naisao sam na situaciju kada mi VIEW extra zavrsava posao ali imam jednu nedoumicu ...

Jel moze neko da mi razjasni sta znaci ova recenica:

Citat:

ORDER BY is allowed in a view definition, but it is ignored if you select from a view using a statement that has its own ORDER BY.

.. jer sam ja probao sve moguce kombinacije sa ORDER i prilikom kreiranja i prilikom selektovanja iz pogleda i sve ok radi. Inace ovo je sa ovog linka.

Hvala,
Ivan

Pa postaviš default ORDER BY u definiciji pogleda, a posle možeš da ga pregaziš nekim drugim ORDER BY-jem iz samog upita.

Jos uvek ne koristim. Do skoro mi je na vecini servera koje koristim bio MySQL 4.1 a tek u poslednjih par meseci uradjen je upgrade na peticu.

@jablan

To znaci da se sasvim normalno ponasa kao i "normalna" baza ? Po tome je onda ona recenica suvisna ili sam nesto propustio u manual-u ...

Misliš normalna tabela? Pa nije isto, jer normalna tabela nema podrazumevani ORDER BY. Barem ne u bazama sa kojima sam ja dosad radio.

Da, mislio sam tabela, a i skontao sam o cemu radi iz tvoje recenice: "jer normalna tabela nema podrazumevani ORDER BY".

Hvala ;)

Ako mislite na običan View nad tabelama, osnovno sredstvo rada u bazama podataka.

Tu mora fino da se pazi, pogotovo ako se kombinuje više tabela.

Prvo, NIKAD Order By u samom pogledu; time terate SQL engine (koji god da je u pitanju) da pravi privremenu tabelu i sortira je (naravno, ako ovo nije pogled nad jednom tabelom i Order By je nad kolonom koja ima indeks).

ORDER By uvek radite iz klijent koda.

(ta rečenica upravo to znači: ako uradite Order By iz klijentskog koda, uzeće se taj Order By a ne definicija iz pogleda).

Drugo, izbegavajte updatable view-s; radite preko stored procedura ili direktno, a izbegavajte instead of triggers (Oracle) ili slične mehanizme na MySQL.

Treće, korišćenjem View-a dajete šansu SQL enginu da uradi optimizaciju plana pristupa podacima (ili eksplicitno, nekom komandom, ili implicitno) i/ili prekompajliranje definicije pogleda u neki interni jezik, tako da se podacima pristupa brže i efikasnije.

Četvrto, kada god možete, koristite bind varijable za definisanje kriterijuma za izdvajanje podataka; znači, umesto da lepite eksplicitne vrednosti u SQL uplit:

"Select * From Products_V where IDProduct = 23"

koristite:

"Select * From Products_V where IDProduct = :1"

a zatim i odgovarajući način za jezik / platformu da date vrednost parametru :1

Peto ... ma ima svašta, ne mogu sada da se setim :)

Citat:

Originalno napisao DejanVesic (Napišite 56209)

Ako mislite na običan View nad tabelama, osnovno sredstvo rada u bazama podataka.

Osnovno sredstvo rada u bazi su tabele. :)

Citat:

Originalno napisao DejanVesic (Napišite 56209)

Prvo, NIKAD Order By u samom pogledu; time terate SQL engine (koji god da je u pitanju) da pravi privremenu tabelu i sortira je (naravno, ako ovo nije pogled nad jednom tabelom i Order By je nad kolonom koja ima indeks).

ORDER By uvek radite iz klijent koda.

(ta rečenica upravo to znači: ako uradite Order By iz klijentskog koda, uzeće se taj Order By a ne definicija iz pogleda).

Da li si siguran u tu tvrdnju?
Evo rezultat jednog malog testa, koristeci tabelu sa oko 300 000 redova:

createview test_view_no_order
asselect * fromtb_benchmark_data_m;

createview test_view_with_order
asselect * fromtb_benchmark_data_morderby bm_date;
select * from test_view_with_order;
select * from test_view_no_order;
declare
vrijeme_na_startu number;

begin
sys.dbms_support.start_trace(true,true);
-- prvo upit na test_view_no_order da podaci budu u cache-u:
for rec in(select * from test_view_no_order)
loop
null;
endloop;
-- zatim upit na test_view_no_order sa ORDER BY:
vrijeme_na_startu := dbms_utility.get_time;
for rec in(select * from test_view_no_order orderby bm_date)
loop
null;
endloop;

dbms_output.put_line('Vrijeme trajanja za ''test_view_no_order'' sa ORDER BY: '|| to_char((dbms_utility.get_time- vrijeme_na_startu)/100) ||' sekundi');
-- zatim upit na test_view_with_order bez ORDER BY:
vrijeme_na_startu := dbms_utility.get_time;
for rec in(select * from test_view_with_order)
loop
null;
endloop;

dbms_output.put_line('Vrijeme trajanja za ''test_view_with_order'' bez ORDER BY: '|| to_char((dbms_utility.get_time- vrijeme_na_startu)/100) ||' sekundi');

sys.dbms_support.stop_trace;
end;

Rezultat izgleda ovako:

Citat:

Vrijeme trajanja za 'test_view_no_order' sa ORDER BY: 3,75 sekundi
Vrijeme trajanja za 'test_view_with_order' bez ORDER BY: 3,44 sekundi

Vrijeme trajanja za 'test_view_no_order' sa ORDER BY: 4,3 sekundi
Vrijeme trajanja za 'test_view_with_order' bez ORDER BY: 3,41 sekundi

Vrijeme trajanja za 'test_view_no_order' sa ORDER BY: 4,14 sekundi
Vrijeme trajanja za 'test_view_with_order' bez ORDER BY: 3,95 sekundi

Vrijeme trajanja za 'test_view_no_order' sa ORDER BY: 3,91 sekundi
Vrijeme trajanja za 'test_view_with_order' bez ORDER BY: 3,67 sekundi

Znaci prilikom svakog izvrsavanja je opcija VIEW sa ORDER BY bila brza od opcije VIEW sa klijentskim ORDER BY.

Evo jos rezultat internog funkcionisanja u Oracle bazi:

Kôd:

SELECT * 

FROM

 TEST_VIEW_NO_ORDER



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      3      0.00       0.00          0          0          0           0

Fetch    10536      7.17       5.79          0      39637          0     1053351

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    10542      7.17       5.79          0      39637          0     1053351

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 57     (recursive depth: 1)

********************************************************************************

SELECT * 

FROM

 TEST_VIEW_NO_ORDER ORDER BY BM_DATE



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      3      0.00       0.00          0          0          0           0

Fetch    10536     11.31       8.98          0      29391          0     1053351

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    10542     11.31       8.98          0      29391          0     1053351

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 57     (recursive depth: 1)

********************************************************************************

SELECT * 

FROM

 TEST_VIEW_WITH_ORDER



call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        3      0.00       0.00          0          0          0           0

Execute      3      0.00       0.00          0          0          0           0

Fetch    10536     10.59       8.43          0      29391          0     1053351

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    10542     10.59       8.43          0      29391          0     1053351

Misses in library cache during parse: 0

Optimizer mode: ALL_ROWS

Parsing user id: 57     (recursive depth: 1)

********************************************************************************

Nadam se da je iz ovog testa vidljivo, da je VIEW sa ugradjenim ORDER BY brzi od VIEW sa ORDER BY NA klijentskoj strani...

Citat:

Originalno napisao DejanVesic (Napišite 56209)

Treće, korišćenjem View-a dajete šansu SQL enginu da uradi optimizaciju plana pristupa podacima (ili eksplicitno, nekom komandom, ili implicitno) i/ili prekompajliranje definicije pogleda u neki interni jezik, tako da se podacima pristupa brže i efikasnije.

Netacno, barem kod Oraclea. Ako se koristi materialized views sa opcijom "query rewrite enabled", onda baza interno moze da izvrsi optimizaciju upita, ali sa obicnim viewom to nije moguce.

Citat:

Originalno napisao DejanVesic (Napišite 56209)

Četvrto, kada god možete, koristite bind varijable za definisanje kriterijuma za izdvajanje podataka; znači, umesto da lepite eksplicitne vrednosti u SQL uplit:

"Select * From Products_V where IDProduct = 23"

koristite:

"Select * From Products_V where IDProduct = :1"

a zatim i odgovarajući način za jezik / platformu da date vrednost parametru :1

Ovo je tacno i to je veoma bitna stvar, koja uveliko utice na performanse.

Moze li neko da mi objasni zasto je bolje koristiti bindovanje parametara a ne eksplicitne vrednosti?

@ Dejan T:

Dragi DBA ;-) kada kažem "klijentski" to i mislim :) (iz neke aplikacije a ne iz same baze).

Odnosno, ako si raspoložen za testove, probaj sledeće:

- napravi View sa ORDER BY u definiciji
- napravi isti takav bez ORDER BY
- Uradi upit nad oba view-a iz neke aplikacije koristeći ORDER BY po DRUGOJ koloni
- Daj rezultate :-)

Ako nisam bio tačan u iskazu, da probam opet: "Nikada Order By u definiciju pogleda" - to su meni rekla moja 4 DBA :)

@ skaarj:

Ako definišeš view preko bind varijabli, parsiranje upita od strane SQL engina se radi samo jednom; ako zakucaš vrednosti, parsiranje upita se radi stalno a za ponovljene upite (u petlji, na web stranicama itd) ovo (parsiranje) itekako zna da doprinese vremenu izvršavanja.

Citat:

Originalno napisao DejanVesic (Napišite 56240)

Imenjace, pa valjda sam bio jasan. :)

- Napravio sam VIEW sa ORDER BY u definiciji.
- napravio sam isti takav view bez ORDER BY
- upit sam uradio iz aplikacije TOAD
- rezultati su tu
- ORDER BY po drugoj koloni zahtjeva novi view (znaci 5 sekundi posla da se kreira novi view i onda usteda za svaki upit)

Sta je nejasno iz mog testa? :)

De ti reci ti svojim DBA nek pogledaju ovu temu i nek naprave neki slican test, pa da usporedimo rezultate, da ne bude "rekla-kazala"...

Meni je uvijek drago kada naucim nesto novo ili ako uvidim da se nesto moze bolje uraditi, nego sto ja mislim da treba...

let the db war begin :)

Citat:

Originalno napisao DejanVesic (Napišite 56209)

Delimicno tacno.
Masa ljudi kada procita ovakav savet gura bindove gde treba i gde netreba.

Bindovi se koriste kako bi se smanjilo vreme parsiranja istih upita, pomocu kesiranja.

Primer: rekurzivna funkcija koja u nekoj while petlji vadi decu iz tabele izvrsavajuci n puta isti upit sa promenjenim parametrom. U ovom slucaju koriscenje binda je vise nego validno, zato sto se lako moze desiti da vreme parsiranja svih ti silnih (istih) upita bude vece od vremena izvrsavanja upita (posebno ako imamo paralelne zahteve).

Bindove nije pozeljno koristiti u nekoliko slucajeva. Pokusacu na primeru da objasnim jedan od njih:

Primer:
Imate tabelu i kolonu sa gomilom YES i NO vrednosti, koja je pritom jos i indeksirana.
Recimo u tabeli postoji 80+% NO vrednosti i relativno mali broj YES vrednosti (reda radi ispod 20%), koristiti index za NO je sporije nego da se pretrci kroz celu tabelu, a koristiti index za YES je brze. Ako db ima bindovan query on ne zna da li pretrazujete po YES ili NO i ne moze da se pravi pametan i da izabere najbolji nacin za izvrsavanje.

Unosom tacnih vrednosti omogucavate db-u da sam pronadje najbolji nacin za pretragu po tabeli.

----------

Dalje. Vreme izvrsavanja viewa-a u odnosu na obican upit se ne razlikuje.

Fora sa order by je veoma prosta. Ako napravite view koji spaja nekoliko tabela, i vraca recimo 100k redova, veoma je glupo sortirati 100k redova ako cete vecinu upita raditi sa nekim where uslovom koji ce broj redova skratiti za pola ili jos vise. Daklem common sense, bolje je sortirati upit sa manje rezultata nego onaj sa vise :)

Ako imate upit u view sa order by i napisete isti taj upit dobicete priblizno jednaka vremena izvrsavanja.

----------

Zakljucak svega je, ne mozete za nesto apsolutno tvrditi 'vako je najbolse zato sto sve zavisi od nacina upotrebe i realnih potreba.

Ma kakav DB war, ne pada mi na pamet :) Ja sam (sada) C# programer, nekada hard-core Oracle programer, pa mi ostala ljubav prema bazama :)

Ovo je sve iskustveno, i naravno da nijedna tvrdnja nije apsolutna. Skup saveta, kome treba neka proba :)

Naravno da neću da pravim bind upit na tabeli koja neće imati preko 1000 redova (kodne tabele) ili koja po strukturi to ne zadovoljava (većina binarnih vrednosti).

Opet, neću nikada staviti ORDER BY u view jer nikada ne znam kada će i kako taj view biti korišćen od strane drugih. A materialized views i slične Oracle lepote (advanced queues, autonomous transactions) su tek posebna priča ...