A jednak speed x 5

Dało się przyspieszyć bez uciekania się do mod_perla. Zamiast odpytywać bazę o każdą komórkę, zrobiłem jedno zapytanie wyciągające dane na cały tydzień do hasha, w pętli operując już tylko na hashu. Całość dostała kopa niesamowitego.
Cały czas mowa o tym Systemie Rezerwacji. Beware: ten link prowadzi nie bezpośrednio do systemu, ale do jego uproszczonej wersji. Wersja właściwa znajduje się na vhoście niedostępnym spoza naszej sieci akademickiej. Dlatego to, co można obejrzeć z internetu, pozbawione jest kilku arkuszy styli i możliwości wejścia głębiej - można zobaczyć tylko spis. Wystarcza to jednak do ocenienia liczbie zapytań do bazy, które teraz zastąpione są jednym.

Przykład optymalizacji zapytań SQL:

Problem jest taki: mamy w bazie kolejne pola. Problemem jest to, ze numeracja mimo, że sekwencyjna, jest nieciagla. Za zadanie mamy odnalezienie poprzedniego pola w stosunku do $DANE, przy czym nie ma byc on bardziej odległe niż 168 numerków do danego.

$akt_nr = $DANE-1;
do {
                $query = "SELECT pole FROM tabelka WHERE numer='$akt_nr'";
                $result = $dbh->prepare($query);
                $result->execute;
                $akt_gnr--;
} while (!(@row = $result->fetchrow) && $akt_nr > $DANE-168);

Zostanie wykonane maksymalnie 168 zapytań do bazy. Jest duża szansa, że przepytywanie skończy się szybciej, zawsze jednak będzie to conajmniej jedno zapytanie.<

Zamiast samemu przetwarzać otrzymane dane, właściwym podejściem jest zrzucenie jak największej ilości pracy na bazę danych. Musimy skonkretyzować nasze żądania:

$query = "SELECT pole FROM tabelka WHERE numer<$DANE AND numer>$DANE-168 ORDER BY numer DESC LIMIT 1;";
$result = $dbh->prepare($query);
$result->execute;
@row = $result->fetchrow;

I już! Czym różni się to zapytanie od poprzedniego? Po pierwsze, sprawdzanie warunku $akt_nr > $DANE-168 zastąpione zastało zawężeniem zakresu, z którego spodziewamy się wyników od bazy. Po drugie, życzymy sobie posortowania wyników wg. numeru, w kolejności malejącej (DESC). Dlaczego? Ponieważ wtedy pierwszym zwróconym wynikiem będzie ten o najwyższym numerze, za nim będzie o numerze mniejszym, potem jeszcze mniejszym itd. Ponieważ interesuje nas najpóźniejszy wpis -- który zostanie zwrócony jako pierwszy -- ograniczmy się tylko do pierwszego wyniku poprzez LIMIT 1. Pozostałe nie będą przepychane przez kanał komunikacyjny program<->baza. Po co nam one, skoro i tak mają iść od razu do śmieci?

Tutaj zadanie jest takie: mamy do dyspozycji pewien zakres (od $BEG do $END) numerów, na których będziemy operować. Numery, choć dyskretne, są nieciągłe -- niektórych brakuje. Do przeprowadzenia mamy parę operacji na poszczególnych numerkach. Co robimy?
O każdy numer można pytać się w momencie, kiedy jest potrzebny:

pętla {
       [...]
       $query = "SELECT pole FROM tabelka WHERE numer=$potrzebny;";
       $result = $dbh->prepare($query);
       $result->execute;
       @row = $result->fetchrow;
       if (defined($row[0])) { zrob_cos_z($row[0])};
}

Takich zapytań będzie $END - $BEG, co czasem daje sporo odwołań do bazy. Kosztownych czasowo, zwłaszcza, gdy pobierane pole sklada się np. z numerka i jakieś małej literki.
Rozwiązaniem jest operowanie na większych porcjach danych pobieranych z bazy:

$query = "SELECT pole FROM tabelka WHERE numer>$BEG-1 AND numer<$END+1;
$result = $dbh->prepare($query);
$result->execute;
$hashref = $result->fetchall_hashref("numer");

pętla {
      [...]
      $nr = %$hashref->{$potrzebny}->{"costam"};
      if (defined($nr)) { zrob_cos_z($nr); };
};

W ten sposób do bazy odwołujemy się raz, a nie za każdym przebiegiem pętli. Wyniki otrzymujemy w hashu, można się do nich łatwo odwoływać przez poszukiwany numer. Minusem jest zwiększone wykorzystanie pamięci przez naszą aplikację, jednak przyrost szybkości prawie zawsze rekompensuje tą niedogodność.

Mając do wykonania większość ilość iteracji podobnych do tych w Przykładzie pierwszym powyżej, należy się zastanowić -- czy dla każdego wyszukiwania generować SELECT z odpowiednio zawężonym zakresem i LIMIT 1? A może lepiej pobrać cały interesujący nas przedział za jednym zapytaniem i używać pętli takiej jak w pierwszym kawałku kodu, zastępując jednak odpytywanie bazy -- odwołaniami do poszczególnych pozycji hasha.

Archived comments:

jpc 2004-04-08 16:23:33

Nie tak źle, tylko czemu Perl a nie Python? :)
Use Webware (bedzie duzo szybszy).