Projektowanie aplikacji w celu zwiększenia wydajności bazy danych

Po wykonaniu kodu nie należy zmieniać właściwości text obiektu SQLStatement. Zamiast tego należy użyć jednej instancji SQLStatement dla każdej instrukcji SQL i użyć parametrów instrukcji w celu udostępnienia innych wartości.

Przed wykonaniem instrukcji SQL środowisko wykonawcze przygotowuje (kompiluje) instrukcję w celu określenia kroków, jakie powinny zostać wykonane wewnętrznie w celu wykonania instrukcji. Po wywołaniu metody SQLStatement.execute() dla instancji klasy SQLStatement, która nie została wykonana poprzednio, instrukcja jest automatycznie przygotowywana zanim zostanie wykonana. Przy kolejnych wywołaniach metody execute() instrukcja jest nadal przygotowywana, pod warunkiem że właściwość SQLStatement.text nie została zmieniona. Dzięki temu jest wykonywana szybciej.

W celu maksymalnego wykorzystania instrukcji wykonywanych wielokrotnie, gdy wartości muszą zostać zmienione pomiędzy wykonywanymi instrukcjami, należy zastosować parametry instrukcji w celu dostosowania instrukcji. (Parametry instrukcji są określane za pomocą właściwości SQLStatement.parameters tablicy asocjacyjnej). W przypadku zmiany wartości parametrów instrukcji (w odróżnieniu od zmiany właściwości text instancji klasy SQLStatement) środowisko wykonawcze nie musi ponownie przygotowywać instrukcji.

Jeśli instancja klasy SQLStatement jest używana ponownie, aplikacja powinna zachować odwołanie do instancji SQLStatement, gdy zostanie ona przygotowana. W celu zachowania odwołania do instancji należy zadeklarować zmienną jako zmienną o zasięgu klasy, a nie jako zmienną o zasięgu funkcji. Dobrym sposobem na ustawienie instancji SQLStatement jako zmiennej o zasięgu klasy jest takie skonstruowanie aplikacji, aby instrukcja SQL była opakowana w pojedynczą klasę. Grupę instrukcji wykonywanych łącznie również można opakować w pojedynczą klasę. (Ta technika jest znana jako stosowanie wzorca projektowego Command). Zdefiniowanie instancji jako zmiennych należących do klasy powoduje, że będą one istniały tak długo, jak instancja klasy opakowującej będzie istniała w aplikacji. Wystarczy również zdefiniować zmienną zawierającą instancję SQLStatement na zewnątrz funkcji, dzięki czemu instancja zostanie zachowana w pamięci. Przykład: należy zadeklarować instancję SQLStatement jako zmienną klasy ActionScript lub jako zmienną (która nie jest funkcją) w pliku JavaScript. Następnie można ustawić wartości parametrów instrukcji i wywołać metodę execute() w celu rzeczywistego uruchomienia zapytania.

W celu zwiększenia szybkości porównywania i sortowania danych należy zastosować indeksy bazy danych.

Podczas tworzenia indeksu dla kolumny w bazie danych jest zapisywana kopia danych tej kolumny. Kopia jest posortowana w porządku liczbowym lub alfabetycznym. Dzięki sortowaniu baza danych może szybko dopasować wartości (np. podczas korzystania z operatora równości) i sortować dane wyników za pomocą klauzuli ORDER BY .

Aktualność indeksów bazy danych jest stale zachowana, przez co operacje zmiany danych (INSERT lub UPDATE) w tabeli działają nieznacznie wolniej. Jednak wzrost szybkości odczytu danych może być znaczący. Z powodu tego kompromisu nie należy po prostu indeksować każdej kolumny każdej tabeli. W zamian należy użyć strategii do definiowania indeksów. Poniższe wytyczne ułatwiają zaplanowanie strategii indeksowania:

• Indeksowaniem należy obejmować kolumny, które są używane w łączeniu tabel, w klauzulach WHERE oraz w klauzulach ORDER BY

• Jeśli kolumny są często wykorzystywane razem, należy utworzyć dla nich jeden wspólny indeks

• W przypadku kolumny zawierającej dane posortowane w porządku alfabetycznym należy określić porównanie COLLATE NOCASE dla indeksu

Należy rozważyć wstępne kompilowanie instrukcji SQL w trakcie bezczynności aplikacji.

Przy pierwszym uruchomieniu instrukcja SQL działa wolniej, ponieważ tekst SQL jest przygotowany (skompilowany) przez mechanizm bazy danych. Przygotowanie i wykonanie instrukcji może być wymagające, dlatego dobrym sposobem postępowania jest wstępne załadowanie danych, a następnie wykonanie innych instrukcji w tle.

1. Najpierw należy załadować dane, których aplikacja potrzebuje na samym początku.

2. Inne instrukcje należy wykonać po zakończeniu wstępnych operacji początkowych w aplikacji lub po wystąpieniu kolejnego momentu „bezczynności” w aplikacji.

Na przykład: załóżmy, że w celu wyświetlenia ekranu początkowego aplikacja nie musi uzyskiwać dostępu do bazy danych. W takim przypadku przed otwarciem połączenia z bazą danych należy poczekać na wyświetlenie ekranu. Na koniec należy utworzyć instancje SQLStatement i wykonać dowolne z nich.

Inna możliwość: załóżmy, że uruchomiona aplikacja natychmiast wyświetla jakieś dane, np. wynik określonego zapytania. W takim przypadku należy uruchomić instancję SQLStatement dla tego zapytania. Po początkowym załadowaniu i wyświetleniu danych należy utworzyć instancje klasy SQLStatement dla innych operacji bazy danych, a jeśli to możliwe — wykonać inne instrukcje, które będą wymagane później.

W rzeczywistości w przypadku ponownego wykorzystania instancji SQLStatement czas wymagany do przygotowania instrukcji to wyłącznie koszt jednorazowy. Prawdopodobnie nie wywiera dużego wpływu na ogólną wydajność.

W transakcji należy zgrupować wiele operacji zmiany danych SQL.

Załóżmy, że wykonywanych jest wiele instrukcji SQL, które obejmują dodawanie lub zmianę danych (instrukcje INSERT lub UPDATE ). Wykonanie wszystkich instrukcji w jawnej transakcji powoduje znaczny wzrost wydajności. Jeśli transakcja nie zostanie rozpoczęta jawnie, każda instrukcja będzie działała we własnej automatycznie tworzonej transakcji. Po zakończeniu wykonywania każdej transakcji (każdej instrukcji) środowisko wykonawcze zapisuje dane wynikowe do pliku bazy danych na dysku.

Rozważmy jednak, co się stanie po jawnym utworzeniu transakcji i wykonaniu instrukcji w kontekście transakcji. Środowisko wykonawcze wykona wszystkie zmiany w pamięci, a następnie zapisze jednorazowo wszystkie zmiany w pliku bazy danych po wykonaniu transakcji. Zapisywanie danych na dysku zwykle zajmuje najwięcej czasu w operacji. W konsekwencji jednorazowe zapisanie na dysk zamiast zapisywania przy każdej instrukcji SQL może spowodować znaczny wzrost wydajności.

Wiele wyników zapytania SELECT należy przetwarzać w częściach, korzystając z metody execute() (z parametrem prefetch ) oraz metody next() klasy SQLStatement.

Załóżmy, że wykonywana jest instrukcja SQL, która odczytuje duży zestaw wyników. Następnie aplikacja przetwarza każdy wiersz danych w pętli. Na przykład: formatuje dane lub tworzy z nich obiekty. Przetwarzanie tych danych może trwać długo, co może powodować problemy z renderowaniem, np. wstrzymanie ekranu lub brak reakcji na działania użytkownika. Zgodnie z opisem w sekcji Operacje asynchroniczne odpowiednim rozwiązaniem jest podział pracy na porcje. Interfejs API bazy danych SQL sprawia, że podział przetwarzania danych jest łatwy do przeprowadzenia.

Metoda execute() klasy SQLStatement udostępnia opcjonalny parametr prefetch (pierwszy parametr). W przypadku wprowadzenia wartości ten parametr określa maksymalną liczbę wierszy wyników, jakie zwraca baza danych po zakończeniu wykonywania:

dbStatement.addEventListener(SQLEvent.RESULT, resultHandler); 
dbStatement.execute(100); // 100 rows maximum returned in the first set

Gdy zostanie zwrócony pierwszy zestaw danych wynikowych, można wywołać metodę next() , aby kontynuować wykonywanie instrukcji i pobrać inny zestaw wierszy wyników. Metoda next() — podobnie jak metoda execute() — akceptuje parametr prefetch w celu określenia maksymalnej liczby zwracanych wierszy:

// This method is called when the execute() or next() method completes 
function resultHandler(event:SQLEvent):void 
{ 
    var result:SQLResult = dbStatement.getResult(); 
    if (result != null) 
    { 
        var numRows:int = result.data.length; 
        for (var i:int = 0; i < numRows; i++) 
        { 
            // Process the result data 
        } 
         
        if (!result.complete) 
        { 
            dbStatement.next(100); 
        } 
    } 
}

Metodę next() można wywoływać tak długo, aż zostaną załadowane wszystkie dane. Poprzedni listing przedstawia, że możliwe jest określenie, czy wszystkie dane zostały załadowane. W tym celu należy sprawdzić stan właściwości complete obiektu SQLResult, który jest tworzony za każdym razem, gdy zakończone zostanie działanie metody execute() lub next() .

Należy rozważyć zastosowanie wielu asynchronicznych obiektów SQLConnection z pojedynczą bazą danych w celu jednoczesnego wykonania wielu instrukcji.

Gdy obiekt SQLConnection zostanie połączony z bazą danych za pomocą metody openAsync() , wówczas działa w tle, a nie w głównym wątku wykonania w środowisku wykonawczym. Ponadto każdy obiekt SQLConnection działa we własnym wątku w tle. Korzystając z wielu obiektów SQLConnection można skutecznie uruchamiać wiele instrukcji SQL jednocześnie.

Istnieją także potencjalne wady takiego podejścia. Najważniejszą z nich jest to, że każdy dodatkowy obiekt SQLStatement wymaga dodatkowej pamięci. Ponadto jednoczesne wykonania również zwiększają ilość pracy, jaką wykonuje procesor, szczególnie na komputerach wyposażonych w jeden procesor lub procesor jednordzeniowy. Z powodu tych problemów takie podejście nie jest zalecane w przypadku urządzeń mobilnych.

Dodatkowym problemem jest to, że potencjalna korzyść z ponownego użycia obiektów SQLStatement może zostać utracona, ponieważ obiekt SQLStatement jest połączony z jednym obiektem SQLConnection. W konsekwencji obiekt SQLStatement nie może zostać wykorzystany ponownie, jeśli powiązany z nim obiekt SQLConnection jest już używany.

Jeśli wybrano korzystanie z wielu obiektów SQLConnection połączonych z jedną bazą danych, należy pamiętać o tym, że każdy z tych obiektów wykonuje jego instrukcje we własnej transakcji. Należy pamiętać, aby wziąć pod uwagę te oddzielne transakcje w każdym kodzie, który zmienia dane, np. przy dodawaniu, modyfikowaniu lub usuwaniu danych.

Paul Robertson przygotował bibliotekę kodu Open Source, który ułatwia wykorzystanie zalet stosowania wielu obiektów SQLConnection przy jednoczesnym ograniczeniu potencjalnych wad takiego podejścia. Biblioteka korzysta z puli obiektów SQLConnection i zarządza powiązanymi obiektami SQLStatement. W ten sposób zapewnia, że obiekty SQLStatement zostaną ponownie wykorzystane, oraz zapewnia dostęp do wielu obiektów SQLConnection, dzięki czemu możliwe jest jednoczesne wykonywanie wielu instrukcji. Więcej informacji na ten temat oraz opcje pobrania biblioteki można znaleźć na stronie http://probertson.com/projects/air-sqlite/ .