[Firebird] Problem z długimi zapytaniami do bazy i długimi oczekiwaniami na odpowiedź z bazy Opcje

[Sokrates]

Witam,

Staram sie jakoś zoptymalizować zapytanie bo obecnie trwa bardzo długo. Nie wiem czy to wina bazy
(a słyszałem bardzo wiele złego o tej bazie jeśli idzie o używaną pamięci),
czy może moje zapytanie SQL jest nie optymalne.

O to moje zapytanie SQL w jednym wywołaniu:

[SQL] pobierz, plaintext 
SELECT A.ktm, A1.wartosc AS liniawww, A4.wartosc AS kategoria, T.nazwa, T1.opispod, T1.opisroz,
C.cenanet AS cenadetal, C1.cenanet AS cenahurt, TP.ref, B.ext
FROM atrybuty A JOIN atrybuty A1 ON (A1.ktm = A.ktm AND A1.cecha = 'l' AND A1.wersjaref = A.wersjaref)
JOIN atrybuty A2 ON (A2.ktm = A.ktm AND A2.cecha = 'r' AND A2.wartosc = '1' AND A2.wersjaref = A.wersjaref)
JOIN atrybuty A3 ON ((A3.cecha = A1.cecha) AND (A3.wartosc = A1.wartosc) AND (A3.ktm containing('www_')))
JOIN atrybuty A4 ON (A4.ktm = A3.ktm AND A4.cecha = 'gr')
JOIN towary T ON (T.ktm = A.ktm AND T.witryna = '1' AND T.akt = '1')
JOIN towary T1 ON (T1.ktm = A3.ktm AND T1.witryna = '1' AND T1.akt = '1')
JOIN cennik C ON (C.ktm = A.ktm AND C.cennik = '62' AND C.wersja = '0')
JOIN cennik C1 ON (C1.ktm = A.ktm AND C1.cennik = '63' AND C1.wersja = '0')
JOIN towpliki TP ON (TP.ktm = A3.ktm AND TP.numer = '1')
JOIN s_binary B ON (B.fileref = TP.ref)
WHERE A.cecha = 'g' AND ((A.wartosc = 'AR') OR (A.wartosc = 'GPS') OR (A.wartosc = 'ARUM'))
[SQL] pobierz, plaintext 

Czas oczekiwania na wynik tego zapytania trwa od 5 do 12 min.!!

oraz zapytanie SQL w 17 zapytaniach: (Najpierw jedno zapytanie:)

[SQL] pobierz, plaintext 
SELECT A.ktm, A.wartosc, A1.wartosc AS grupa, A2.wartosc AS nazwa,
T.opispod, T.opisroz, TP.ref, B.ext
FROM atrybuty A JOIN atrybuty A1 ON (A1.ktm = A.ktm AND A1.cecha = 'gr')
JOIN atrybuty A2 ON (A2.ktm = A.ktm AND A2.cecha = 'l')
JOIN atrybuty A3 ON (A3.ktm = A.ktm AND A3.cecha = 'podstawowy' AND A3.wartosc = '1')
JOIN towary T ON (T.ktm = A.ktm AND T.witryna = '1')
JOIN towpliki TP ON (TP.ktm = A.ktm AND TP.numer = '1')
JOIN s_binary B ON (B.fileref = TP.ref)
WHERE A.cecha = 'g' AND (A.wartosc = 'AR' OR A.wartosc = 'GPS' OR A.wartosc = 'ARUM')
[SQL] pobierz, plaintext 

a następnie wyniki poprzedniego zapytania puszczam w pętli foreach
i dla każdego wiersza wywołuje kolejne zapytanie SQL (jest takich wywołań 16):

[SQL] pobierz, plaintext 
SELECT A.ktm, C.cenanet AS cenadetal, C1.cenanet AS cenahurt, T.nazwa
FROM atrybuty A
JOIN atrybuty A1 ON (A1.ktm = A.ktm AND A1.cecha = 'g' AND A1.wartosc = '".$a_grprod['wartosc']."')
JOIN atrybuty A2 ON (A2.ktm = A.ktm AND A2.cecha = 'r' AND A2.wartosc = '1')
JOIN towary T ON (T.ktm = A.ktm AND T.witryna = '1')
JOIN cennik C ON (C.ktm = A.ktm AND C.cennik = '62' AND C.wersja = '0')
JOIN cennik C1 ON (C1.ktm = A.ktm AND C1.cennik = '63' AND C1.wersja = '0')
WHERE A.wartosc = '".$a_grprod['nazwa']."' AND A.cecha = 'l'
[SQL] pobierz, plaintext 

Czas oczekiwania na wynik tych dwóch powyższych zapytań (z czego drugie zapytanie jest wywołane w pętli 16 razy) trwa od 1 do 2 min.

Wyniki tych zapytań są identyczne.

I teraz pytanie, dlaczego tak sie dzieje...

Może zapytania SQL są jakoś przeze mnie mało efektywnie stworzone..


Wielkie dzięki za jakąkolwiek pomoc, sugestie albo uwagii...
Pozdrawiam...

[teutates]

Mysql podczas JOIN zrzera caaala mase ramu, jakbys chcial zjoinowac 2 tabele po kilkaset tysiecy rekordow.. to zdarzylbys zupe ugotowac:) i pare gb ramu by zjadlo przy okazji Proponuje rozbic wszystko na zapytania bez joinow i stosowac je jak subselecty.

[ActivePlayer]

[SQL] pobierz, plaintext 
SEKLECT * FROM (SELECT A.ktm, A1.wartosc AS liniawww, A4.wartosc AS kategoria, T.nazwa, T1.opispod, T1.opisroz,
C.cenanet AS cenadetal, C1.cenanet AS cenahurt, TP.ref, B.ext
FROM atrybuty A WHERE A.cecha = 'g' AND ((A.wartosc = 'AR') OR (A.wartosc = 'GPS') OR (A.wartosc = 'ARUM')) ) TEMPA
JOIN atrybuty A1 ON (A1.ktm = TEMPA.ktm AND A1.cecha = 'l' AND A1.wersjaref = TEMPA.wersjaref)
JOIN atrybuty A2 ON (A2.ktm = TEMPA.ktm AND A2.cecha = 'r' AND A2.wartosc = '1' AND A2.wersjaref = TEMPA.wersjaref)
JOIN atrybuty A3 ON ((A3.cecha = A1.cecha) AND (A3.wartosc = A1.wartosc) AND (A3.ktm containing('www_')))
JOIN atrybuty A4 ON (A4.ktm = A3.ktm AND A4.cecha = 'gr')
JOIN towary T ON (T.ktm = TEMPA.ktm AND T.witryna = '1' AND T.akt = '1')
JOIN towary T1 ON (T1.ktm = A3.ktm AND T1.witryna = '1' AND T1.akt = '1')
JOIN cennik C ON (C.ktm = TEMPA.ktm AND C.cennik = '62' AND C.wersja = '0')
JOIN cennik C1 ON (C1.ktm = TEMPA.ktm AND C1.cennik = '63' AND C1.wersja = '0')
JOIN towpliki TP ON (TP.ktm = A3.ktm AND TP.numer = '1')
JOIN s_binary B ON (B.fileref = TP.ref)
[SQL] pobierz, plaintext 

czy masz pozakladane indexy, ile jest rekordow w kazdej z tabel?

[Sokrates]

Proponuje rozbic wszystko na zapytania bez joinow i stosowac je jak subselecty.

Możesz powiedzieć coś więcej o tych subselectach (albo skierować gdzieś na jakąś dobra literaturę o ty m w necie)
Być może to nie ma znaczenia ale to nie jest MySQL ale FireBird (słyszałem że jest jeszcze gorsza)

czy masz pozakladane indexy, ile jest rekordow w kazdej z tabel?

Wydaje mi sie że w FireBirde automatycznie zakładają się index na tworzoną tabele.
W każdym bądź razie gdy wywołuje zapytanie w programie do obsługi tej bazy mam taki zrzut, więc raczej tabele są indexowane

Kod

Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A8 INDEX (RDB$FOREIGN361),T INDEX (RDB$PRIMARY325),W INDEX (RDB$FOREIGN823)),A1 INDEX (CENNIK_IDX1)),A2 INDEX (CENNIK_IDX1)),A3 INDEX (ATRYBUTY_IDX1)),A4 INDEX (ATRYBUTY_IDX1)),A5 INDEX (ATRYBUTY_IDX1)),A6 INDEX (ATRYBUTY_IDX1)),A7 INDEX (ATRYBUTY_IDX1)),A9 INDEX (ATRYBUTY_IDX1)))

Adapted Plan
PLAN SORT (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (JOIN (A8 INDEX (FK_ATRYBUTY_DEFCECHY),T INDEX (PK_TOWARY),W INDEX (FK_WERSJE_TOWARY)),A1 INDEX (CENNIK_IDX1)),A2 INDEX (CENNIK_IDX1)),A3 INDEX (ATRYBUTY_IDX1)),A4 INDEX (ATRYBUTY_IDX1)),A5 INDEX (ATRYBUTY_IDX1)),A6 INDEX (ATRYBUTY_IDX1)),A7 INDEX (ATRYBUTY_IDX1)),A9 INDEX (ATRYBUTY_IDX1)))

co do ilości rekordów w tabelach, to jest ich sporo:

tabela 'atrybuty' to 34728 rekordów,
tabela 'towary' to 20772 rekordów,
tabela 'cennik' to 106888 rekordów,
tabela 'towpliki' to 980 rekordów,
tabela 's_binary' to 1169 rekordów.

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

Nie wiem czy subselecty działaja na bazie FireBird - Chyba nie...
Ramu na maszynie jest 8GB a i tak nieraz sie zapycha i trzeba restartować bo zapytania do bazy trwają po
paręnaście sekund.

Ten post edytował Sokrates 26.05.2008, 11:23:31

[woj_tas]

A mógłbyś pokazać cała strukturę bazy danych? razem z relacjami.

[Sokrates]

A mógłbyś pokazać cała strukturę bazy danych? razem z relacjami.

a po co Ci to?

Nie bardzo...

Baza ma 500 MB danych,
około 400 tabel.

Nie tworzyłem tej bazy, wiec też nie znam niektórych szczegółów.

Ale relacje jakie występują w tabelach na których ja operuje
można wyczytać z zapytania SQL jakie podałem u góry.

[teutates]

No to odpowiedz jak na dloni: baza nie zaprojektowana pod dane zapytanie:/ Proponuje stowrzyc nowa strukture tabel oraz procedure ktora przepisze stare wpisy do nowej struktury, natomiast jesli chodzi o zapytania i dlugosc ich wykonywania to jesli przeszukujesz 1 tabele z 1k rekordow to jest ok ale jesli tych tabelek jest 10 i kazda ma po kilka k rekordow to nie dosc ze wszystko na czas operacji trafia do jakiegos shared poola firebirdowego to jeszcze jaka ilosc operacji do wykonania...
Proponuje przebudowac baze (dodac tabelek) pod dane wymagania oraz wlasnie tej procedurki:)
Jelsi chodzi o subselecty:

[SQL] pobierz, plaintext 
SELECT col1, col2, col3 FROM tab1 ,(SELECT col1,col2 FROM...WHERE...) WHERE col1 IN (SELECT * FROM.. WHERE...) WHERE
[SQL] pobierz, plaintext 

na duzej ilosci danych dziala duzo szybciej niz JOINY, oczywiscie zakladajac ze sa rozsadnie pozakladane indexy:) Jesli baza nei jest caly czas mocno wykozystywana dobrym zwyczajem jest przebudowywanie indexow co jakis czas co moze tez przyspieszyc operacje.

Pozdrawiam