データベースのパフォーマンスためのアプリケーションデザイン

実行後は、SQLStatement オブジェクトの text プロパティを変更しないでください。代わりに、各 SQL ステートメントに 1 つの SQLStatement インスタンスを使用し、ステートメントパラメーターを使用して様々な値を指定します。

SQL ステートメントは、実行前に準備（コンパイル）されて、そのステートメントを実行するために内部で実行されるステップが特定されます。以前に実行されていない SQLStatement インスタンスに対して SQLStatement.execute() を呼び出すと、ステートメントが実行される前に自動的に準備されます。その後の execute() メソッドの呼び出しでは、 SQLStatement.text プロパティが変更されていない限り、ステートメントは準備された状態のままです。したがって、より高速に実行されます。

ステートメントの再利用のメリットを最大限に活用するために、実行のたびに値を変更する場合は、ステートメントパラメーターを使用してステートメントをカスタマイズします（ステートメントパラメーターを指定するには SQLStatement.parameters 連想配列プロパティを使用します）。ステートメントパラメーターの値を変更しても、SQLStatement インスタンスの text プロパティを変更した場合とは違って、ステートメントを準備し直す必要はありません。

SQLStatement インスタンスを再利用する場合は、準備した SQLStatement インスタンスへの参照をアプリケーションで保存する必要があります。インスタンスへの参照を保持するには、その変数を関数スコープの変数ではなくクラススコープの変数として宣言します。SQLStatement をクラススコープの変数にするためには、SQL ステートメントが 1 つのクラスにラップされるようにアプリケーションを構成することをお勧めします。一緒に実行される一連のステートメントを 1 つのクラスにラップすることもできます（この方法は、コマンドデザインパターンの使用と呼ばれます）。インスタンスをクラスのメンバー変数として定義すると、そのインスタンスは、そのラッパークラスのインスタンスがアプリケーション内に存在する限り保持されます。最低でも、SQLStatement インスタンスを含む変数を関数の外部で定義すれば、そのインスタンスがメモリ内に保持されるようになります。例えば、SQLStatement インスタンスを ActionScript クラスのメンバ変数または JavaScript ファイルの関数以外の変数として宣言し、実際にクエリを実行する必要が生じたら、ステートメントのパラメーター値を設定して execute() メソッドを呼び出します。

データの比較およびソートの実行速度を改善するには、データベースのインデックスを使用します。

列にインデックスを作成すると、その列データのコピーがデータベースに格納されます。このコピーは、数字順またはアルファベット順で常にソートされます。これを使用して、値の照合処理（等号演算子を使用する場合など）および ORDER BY 句による結果データのソート処理が高速に実行されます。

データベースインデックスが最新の状態に維持されるので、テーブルでの変更操作（INSERT または UPDATE）が少し遅くなります。ただし、データ取得速度については大幅な向上効果が見込まれます。このパフォーマンスのトレードオフがあるので、すべてのテーブルのすべての列に無条件にインデックスを設定することは避け、インデックスの定義に関する基準を定めてください。次のガイドラインを使用して、インデックスの定義方法を計画してください。

  • 結合テーブル、WHERE 句、または ORDER BY 句で使用されるインデックス列。

  • 複数の列が同時に、また頻繁に使用される場合、単一のインデックスでそれらの列にインデックスを定義します。

  • アルファベット順でソートされているテキストデータを含む列を取得する場合、インデックスには COLLATE NOCASE 照合を指定します。

アプリケーションのアイドル時に SQL ステートメントをプリコンパイルすることを検討します。

SQL ステートメントを初めて実行するときは時間がかかります。これは、データベースエンジンによって SQL テキストが準備（コンパイル）されるからです。ステートメントを準備して実行する操作は負荷が高くなる可能性があるので、初期データを事前に読み込んでおき、他のステートメントはバックグラウンドで実行するという方法が考えられます。

  1. まず、アプリケーションで最初に必要になるデータをロードします。

  2. アプリケーションの最初の起動操作が完了したら（またはアプリケーションのその他の「アイドル」時に）、他のステートメントを実行します。

例えば、初期画面を表示するためにアプリケーションからデータベースへ一切アクセスしないとします。この場合、画面の表示を待ってから、データベース接続を開きます。最後に、SQLStatement インスタンスを作成し、その他の必要な処理を実行します。

または、アプリケーションで起動後すぐに何らかのデータ（特定のクエリの結果など）を表示する場合があるとします。そのクエリの SQLStatement インスタンスをすぐに実行します。初期データが読み込まれて表示されたら、他のデータベース操作のための SQLStatement インスタンスを作成し、可能であれば、後に必要になる他のステートメントを実行します。

実際には、SQLStatement インスタンスを再使用している場合、ステートメントの準備に必要な追加の時間にかかるコストは、1 回分のコストだけです。そのため、全体的なパフォーマンスに与える影響は大きくないと考えられます。

トランザクション内の複数の SQL データ変更操作をグループ化します。

例えば、データの追加や変更を伴う SQL ステートメント（ INSERT ステートメントや UPDATE ステートメント）を多数実行するとします。すべてのステートメントを明示的なトランザクションの中で実行するとパフォーマンスが大幅に向上します。トランザクションを明示的に開始しない場合は、各ステートメントが、自動的に作成される固有のトランザクションで実行されます。この場合、トランザクション（ステートメント）の実行が完了するたびに結果のデータがディスク上のデータベースファイルに書き込まれます。

一方、トランザクションを明示的に作成してそのトランザクションのコンテキストでステートメントを実行する場合は、すべての変更がメモリ内で行われ、トランザクションがコミットされるときに一度にデータベースファイルに書き込まれます。一般に、ディスクへのデータの書き込みは操作の中で最も時間のかかる部分です。したがって、ディスクへの書き込みを SQL ステートメントごとに行う代わりに一度に行うようにすると、パフォーマンスが大幅に向上します。

複数のパートで大量の SELECT クエリ結果を処理するには、SQLStatement クラスの execute() メソッド（ prefetch パラメーターを指定）と next() メソッドを使用します。

例えば、大量の結果セットを取得する SQL ステートメントを実行するとします。この場合、各行のデータはループで処理されます。例えば、データをフォーマットするか、そこからオブジェクトを作成します。そのデータの処理には長時間かかる可能性があります。また、それによって画面がフリーズしたり応答しないなどのレンダリングの問題が発生する可能性があります。 非同期操作 で説明したように、解決方法の 1 つは作業を複数のチャンクに分割することです。SQL データベース API で、データ処理の分割は簡単に実行できます。

SQLStatement クラスの execute() メソッドには、オプションの prefetch パラメーター（第 1 パラメーター）があります。値を指定する場合、実行が完了したときにデータベースが返す結果行の最大数を指定します。 

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

結果データの最初のセットが返されると、 next() メソッドを呼び出してステートメントの実行を続行し、別の結果行を取得できます。 execute() メソッドと同様に、 next() メソッドでは prefetch パラメーターを使用して、返す行の最大数を指定できます。 

// 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); 
        } 
    } 
}

すべてのデータがロードされるまで、 next() メソッドを呼び出し続けることができます。前の一覧で示したように、データがすべてロードされたときを判断できます。 execute() メソッドまたは next() メソッドが完了するたびに作成される SQLResult オブジェクトの complete プロパティを確認します。

注意： prefetch パラメーターおよび next() メソッドを使用して、結果データの処理を分割します。このパラメーターとメソッドは、クエリの結果を結果セットの一部のみに限定する目的では使用しないでください。ステートメントの結果セットから行のサブセットを取得する場合は、 SELECT ステートメントの LIMIT 句を使用します。その結果セットが大きい場合にも、 prefetch パラメーターおよび next() メソッドを使用して、結果の処理を分割できます。
単一のデータベースに複数の非同期 SQLConnection オブジェクトを使用して、複数のステートメントを同時に実行する方法があります。

SQLConnection オブジェクトが openAsync() メソッドを使用してデータベースに接続されている場合、メインのランタイム実行スレッドではなく、バックグラウンドで実行されます。さらに、各 SQLConnection は独自のバックグラウンドスレッドで実行されます。複数の SQLConnection オブジェクトを使用すると、複数の SQL ステートメントを実質的に並列して実行できます。

この方法には潜在的な弱点もあります。最も重要な点は、追加の各 SQLStatement オブジェクトに追加のメモリが必要なことです。さらに、同時実行によってプロセッサーの処理が増えます。CPU または CPU コアが 1 つしかないコンピューターの場合は特に顕著です。このような問題点があるため、この方法をモバイルデバイスで使用することは推奨されません。

もう 1 つの問題点は、SQLStatement オブジェクトが単一の SQLConnection オブジェクトにリンクしていることで、SQLStatement オブジェクトの再利用による利点が失われる可能性があることです。そのため、関連する SQLConnection が使用中の場合、SQLStatement オブジェクトは再利用できません。

単一のデータベースに接続されている複数の SQLConnection オブジェクトを使用する場合、各オブジェクトが個々のトランザクションでステートメントを実行するように注意してください。データの追加、変更、削除を実行するコードによって別々のトランザクションが同時に実行される可能性を考慮してください。

Paul Robertson が作成したオープンソースコードライブラリを使用すると、複数の SQLConnection オブジェクトを使用する利点を取り入れながら、潜在的な弱点を最小限に抑えることができます。このライブラリでは、SQLConnection オブジェクトのプールを使用し、関連する SQLStatement オブジェクトを管理します。この方法で、SQLStatement を再利用し、複数の SQLConnection オブジェクトを使用して複数のステートメントを同時に実行できます。ライブラリについて詳しくは、またライブラリをダウンロードするには、 http://probertson.com/projects/air-sqlite/ を参照してください。