Optimizacija, može li bez podupita?

Optimizacija, može li bez podupita? 14.08.2008. u 19:58 · pre 221 mesec #1

Pozdrav svima!

Potrebna mi je mala pomoć u optimizaciji....

U bazi imam tabelu cenovnik, u kojoj se cuvaju samo kalkulacije.
Bitna polja su: ID, sifra_robe, datum - datum od kada važi kalkulacija.
U toj tabeli postoje samo zapisi na dane kada je došlo do promene kalkulacije.

za dobijanje trenutne cene izvršava se podupit tipa:

Code:

select a.sifra_robe, a.datum, a.cena
from cenovnik a
where a.id = ( select max(pom.cen_id) from cenovnik pom where pom.sifra_robe=a.sifra_robe)
order by a.sifra_robe

U slučaju potrebe cenovnika za odredjeni dan dodaje se

Code:

where datum<='trazeni_datum'

i ovo rešenje radi OK.

E sad probam da se igram DWH i u posebnoj bazi napravim tabelu prodaje, u kojoj su izmedju ostalog i polja datum i sifra_robe. Tabela ima samo za ovu godinu do sada (august) oko 20.miliona stavki!

Pitanje:

Postoji li optimalniji način spajanja dve tabele osim navedenog, jer "nažalost" nemam toliko "konjskih snaga" da se izvrši podupit 20 Mil puta!!

Unapred Hvala

Re: Optimizacija, može li bez podupita? 14.08.2008. u 22:30 · pre 221 mesec #2

A što ne radiš upit na tabelu proizvoda sa podupitom na taj cenovnik? Tako ćeš imati onoliko izvršenih podupita koliko i proizvoda. Valjda.

Nešto na foru:

Code:
select * from proizvodi p
inner join cene c on p.id = c.pid
where datum = (select max(datum) from cene where pid = p.id);

BTW, koristio sam datum jer oslanjati se na redosled ID-jeva u tabeli je no-no.

Re: Optimizacija, može li bez podupita? 14.08.2008. u 23:03 · pre 221 mesec #3

"DWH i u posebnoj bazi napravim tabelu prodaje, u kojoj su izmedju ostalog i polja datum i sifra_robe"

Za ovaj problem imas generalno dve opcije:

A) normalizovane tabele, cena se pamti samo u tabeli Cenovnik

Code:

Cenovnik (Sifra_robe varchar(5) , Cena decimal, DatumVazenjaCene datetime)
Prodaja (Prodaja_ID int , Sifra_Robe varchar(5), Kolicina decimal, DatumProdaje datetime)

Onda ti za prikaz cene robe na dan prodaje moze posluziti ovakav kveri:

Code:

SELECT  P.Prodaja_ID
      , P.Sifra_Robe
      , P.Kolicina
      , P.DatumProdaje
      , CenaNaDanProdaje =
        (SELECT C.Cena
        FROM    Cenovnik AS C
        WHERE   C.Sifra_robe       = P.Sifra_Robe
            AND C.DatumVazenjaCene =
                (SELECT MAX( DatumVazenjaCene )
                FROM    Cenovnik AS C2
                WHERE   C2.Sifra_Robe        = C.Sifra_Robe
                    AND C2.DatumVazenjaCene <= P.Datumprodaje
                )
        )
FROM    Prodaja AS P
ORDER BY P.Sifra_Robe
      , P.DatumProdaje

To je nesto slicno onome sto si ti napisao i provereno - radi. Iz strukture ovog kverija mozes da zakljucis koji bi ti indeksi bili od koristi.

Medjutim, u praksi malo ko radi na ovaj nacin. Uglavnom se korsiti druga varijanta, gde se cena na dan prodaje kopira iz tabele Cenovnik u tabelu prodaja. Sve je isto, samo tabela Prodaja ima jednu kolonu vise:

B) Denormalizovano resenja:

Code:

Cenovnik (Sifra_robe varchar(5) , Cena decimal, DatumVazenjaCene datetime)
Prodaja (Prodaja_ID int , Sifra_Robe varchar(5), Kolicina decimal, DatumProdaje datetime, cenaNaDanprodaje decimal)

Onda pre nego sto uradis INSERT u tabelu Prodaja moras da pronadjes trenutno vazecu cenu. Za to mozes da upotrebis svoj kveri ili iz prethodnog slucaja nesto ovako:

Code:

SELECT CenaNaDanProdaje =
        (SELECT C.Cena
        FROM    Cenovnik AS C
        WHERE   C.Sifra_robe       =  'JAB'
           AND C.DatumVazenjaCene =
                (SELECT MAX( DatumVazenjaCene )
                FROM    Cenovnik AS C2
                WHERE   C2.Sifra_Robe        = 'JAB'
                    AND C2.DatumVazenjaCene <= 'DatumProdaje'
                )

Server ima da radi vise pri insertu, ali zato vise nema potrebe za komplikovanim kverijima.
Posto pominjes DWH (valjd je to data Warhouse), onda se seti da u DWH ne mora bas sve da bude normalizovano i da je redundansa u podacima pozeljna, e da bi se pretrazivanje i prikaz podataka ubrzali. Za potrebe DWH se cak i agregatni podaci (SUM(Kolicina*CENA) GROUP BY Sifra_Robe, Godina) cesto unapred izracunaju i tako izracunati cuvaju u DWH.

:-)

Re: Optimizacija, može li bez podupita? 15.08.2008. u 10:11 · pre 221 mesec #4

Citat:

Zidar: "DWH i u posebnoj bazi napravim tabelu prodaje, u kojoj su izmedju ostalog i polja datum i sifra_robe"

Za ovaj problem imas generalno dve opcije:

A) normalizovane tabele, cena se pamti samo u tabeli Cenovnik

Da to imam sad i to zahteva podupite.

Citat:

B) Denormalizovano resenja:

Code:

Cenovnik (Sifra_robe varchar(5) , Cena decimal, DatumVazenjaCene datetime)
Prodaja (Prodaja_ID int , Sifra_Robe varchar(5), Kolicina decimal, DatumProdaje datetime, cenaNaDanprodaje decimal)

Onda pre nego sto uradis INSERT u tabelu Prodaja moras da pronadjes trenutno vazecu cenu. Za to mozes da upotrebis svoj kveri ili iz prethodnog slucaja nesto ovako:

Server ima da radi vise pri insertu, ali zato vise nema potrebe za komplikovanim kverijima.

To rešenje je Ok ako mi samo treba cena, ali ako mi trebaju i ostali kalkulativni elementi (Nabavna Cena, rabat, RUC), onda ne znam koliko je pametno ići ka tolikoj denormalizaciji.

Možda ovakvo rešenje:

Tabela cenovnik:

Code:

CEN_ID    SIFRA_ROBE    DATUM_CENE        CENA ....
101        AAA            2008-02-25        110
100        AAA            2008-01-10        100
102        BBB            2008-01-13        500
103        AAA            2008-06-01        150
104        BBB            2008-07-23        550

Rezultat upita (čiji rezultat može bit i posebna tabela):

Code:

CEN_ID    SIFRA_ROBE    DATUM_OD    DATUM_DO
100        AAA            2008-01-10    2008-02-24
101        AAA            2008-02-25    2008-05-31
102        BBB            2008-01-13    2008-07-22
103        AAA            2008-06-01    TODAY()
104        BBB            2008-07-23    TODAY()

Time bi postigli da imamo rezultat joinom dva upita. I sada mi je potrebna pomoć za ovaj konretan upit!

Re: Optimizacija, može li bez podupita? 15.08.2008. u 10:53 · pre 221 mesec #5

I sam si uočio glavni problem - a to je da ti je struktura tabele cenovnik loša! Cena važi od jednog momenta u vremenu do nekog drugog momenta u vremenu (pa makar taj momenat težio beskonačnosti).

Ako imaš mogućnost da utičeš na strukturu tabele, savetovao bih ti da je promeniš upravo onako kako si i zaključio svoj post, a to je da u samoj tabeli cenovnici držiš dva datuma: datum_od i datum_do.

Sa takvom strukturom se pronalaženje aktuelne cene (ili cene u bilo kom vremenu) svodi na trivijalan upit pretrage cenovnika (bez podupita ili join-ova).

Potpis

"The best code is no code at all."
- Zidar (ES član)
"Biggest obstacle to learning
SQL is unlearning procedural
programming." - Joe
Celko
"Minimize code, maximize data."
- A. Neil Pappalardo

Re: Optimizacija, može li bez podupita? 15.08.2008. u 15:46 · pre 221 mesec #6

Chachka je u pravu sa predlogom za novu strukturu cenovnika. Upotreba DatumOd i DatumDo zaista eliminise probleme koje proizilaze id dve opciej kje sam opisao.

Medjutim, treba voditi racuna da ne dodje do preklapanja intervala DatumOd i DatumDo. Na primer, za jedan artikl stavis da cena vazi od '1 Jan 2008' do '30 May 2008' a onda za isti artikl stavis da cena vazi od '15 mar 2008' do '30 Jun 2008'. Vecina modernih DBMS ima problem da spreci ovo preklapanje.

Chachka koristi mislim POSTGRESS ili tako nesto, koji je valjda jedini sistem na kome se u CHECK CONSTRAINT moze spreciti preklapanje intervala. U MS SQL se moze slicno postici u verziji 2005, pisanjem korinickih funkcija i njihovim pozivanjem kroz CHECK, sto nije bas lako.

A onda ostaje jos jedno pitanje - kako znamo dokle ce cena da vazi? Znamo da 'od danas vazi nova cena'. A dokle? Mozemo da ostavimo NULL u DatumDo ili da stavimo neki daleki datum u buducnosti. I u jednom i u drugom slucaju, kad dodje do nove promene cene, mora prvom da se UPDATE rekord sa tekucom cenom, da bi upisali tacan datumDo, pa da se onde radi ISNERT za novi rok vazenja cene. I mora da bude (novi DatumOd) = (stari datumDo + 1 dan). Ne deluje mi bas mnogo jednostavnije od prepisivanja cenei i cega god jos zelis prilikom INSERTa.

Zbog svega ovoga bih razmislio dobro pre promene strukture. Nesto dobijes, nesto izgubis. Ako pitas ljude koji se bave knjigovodstvenim bazama, glatko ce ti kazati da mnoge izracunate elemente kalkulacije jednostavno imas da sacuvas u tabelama, bez obzira sto se kverijima mogu dobiti. Tako da ispada da se nije bas bez veze odomacila u praksi metoda sa delimicnom denormalizacijom. Ja licno ne radim na knjigovodstvu, govorim samo ono sto sam video kod drugih. Getsbi je mozda prava osoba da nam priblizi tajne i zackoljice knjigovodstvenih metoda.

Re: Optimizacija, može li bez podupita? 15.08.2008. u 16:08 · pre 221 mesec #7

Ako sam dobro shvato ovde se radi o dva odvojena modela. Onom koji kolega Jutikus već poseduje i DW sa kojim bi da se poigra. I ja se trudim kod transakcionih baza da sve bude dobro normalizovano i da nema izračunatih podataka, mada nisam to radio u vreme Clipper-a i Dbase-a, a posebno u vreme sporih mašina. Ono što je mene zagolicalo da se oglasim na ovoj temi je potreba za optimizacijom upita ukoliko je DW u pitanju.

Kod DW (Data Warehouse-inga) je po defaultu jasno da će se upiti izvršavati sporo.
1. Radi se sa istorijskim podacima čiji je nivo više sumarni nego detaljni.
2. Količine podataka su u gigabit-ima odnosno terabit-ima.
3. Podaci služe samo za čitanje, a ne i za ažuriranje.
4. Model je denormalizovan. Ili bolje rečeno denormalizovana je tabela činjenca, dok su tabele dimenzija normalizivane.

Stoga je za očekivati da će vreme odziva sistema biti dugo. To i ne treba da te brine. Analitičari koji rade na OLAP aplikacijama - OnLine Analytical Processing (direktna analitička obrada), zasnovanim na skladištima podataka, manje više sami kreiraju upite (imaju taj stepen znanja) i spremni su da odu na kaficu, ili raspredaju o nekom dobrom traču, dok im se rezultat njihovog upita ne pojavi na ekranu. Na to, kako oni kreiraju upite, ti kao projektant modela nećeš imati mnogo uticaja. Ovo je sasvim drugačiji pristup nego kod OLTP - OnLine Transaction Processing (direktna transakciona obrada) gde je normalizovana baza podataka i često ažuriranje.

Više treba da te brine kako da obezbediš sve podatke u tabelu činjenica i kako neke od njih da agregiraš (nešto je već spomenu Zidar), kao i koje su to tabele od kojih se mogu načiniti tabele dimenzija atraktivne za proces analize u jednom trgovinskom lancu.

Chachk-in predlog je više za transakcioni model. Mada kod modela sa više tabela činjenica nije isključeno da bi se jedna takva tabela mogla baviti isključivo pormenama cena.

I da ne budem krivo shvaćen. Nisam ni za, ni protiv optimizacije kod DW-a, već mislim da to ne treba da bude glavna ideja pri kreiranju jednog takvog modela.

Potpis

https://mzahorjanski.wixsite.com/mzahorjanski
https://www.fiverr.com/miroljubzahorja?up_rollout=true
https://www.youtube.com/@miroljubzahorjanski

Re: Optimizacija, može li bez podupita? 18.08.2008. u 08:06 · pre 221 mesec #8

Probaj sa ovim upitom za cenu jedne robe:

Code:
select a.sifra_robe, a.datum, a.cena
from cenovnik a
where a.sifra_robe=:sifrob
order by a.datum desc
rows 1

Po istom principu ako zelis ceo cenovnik napravis proceduru i u njoj:

Code:
for select r.sifra_robe from roba r
into :sifrob
do
begin
  select a.datum, a.cena
  from cenovnik a
  where a.sifra_robe=:sifrob
  order by a.datum desc
  rows 1
  into :datum, :cena;

  suspend;
end

Podrazumeva se da je cenovnik.datum indexiran, trebalo bi da radi brzo.
Meni radi fino, doduse nemam 20mil stavki, pa ne znam kako ce tebi raditi.

Forum kao aplikacija na telefonu

Srodne teme