How To Optimize Symbian Sql Server 1.1 (japanese)

  • Uploaded by: Symbian
  • 0
  • 0
  • May 2020
  • PDF

This document was uploaded by user and they confirmed that they have the permission to share it. If you are author or own the copyright of this book, please report to us by using this DMCA report form. Report DMCA


Overview

Download & View How To Optimize Symbian Sql Server 1.1 (japanese) as PDF for free.

More details

  • Words: 4,775
  • Pages: 33
Symbian SQL Server 最適化の手順 セキュリティ区分: 参考文書:

External - Symbian

状態: バージョン: 最終改定日:

発行 1.1

SGL.GT0334.067

2008/02/22

チーム/部門 : 作成者: 所有者:

SWE - System Libraries

承認者:

George Sewell

Symbian Persistent Data Services Team Symbian Persistent Data Services Team

配布:

外部

USE FILE > PROPERTIES TO EDIT DOCUMENT INFORMATION

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED. THIS DOCUMENT MAY NOT BE REPRODUCED IN ANY FORM, IN WHOLE OR IN PART, BY ANY MEANS WHATSOEVER, WITHOUT THE WRITTEN PERMISSION OF THE COPYRIGHT HOLDER.

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

CONTENTS 1

はじめに ............................................................................................................................................ 4 1.1 1.2 1.3

2

目的、適用範囲、対象読者.......................................................................................................... 4 確認事項 ..................................................................................................................................... 4 重要な注意事項 ........................................................................................................................... 4

SQL SERVER の概要......................................................................................................................... 5 2.1 SQL SERVER アーキテクチャの概要 ............................................................................................ 5 2.2 SQLITE アーキテクチャの概要..................................................................................................... 6 データベース ファイルのフォーマット ............................................................................... 6 2.2.1 2.2.2 SQLite サブシステム ........................................................................................................... 6 2.2.3 ロールバック ジャーナル ファイル ..................................................................................... 7 2.3 SQL SERVER の制限..................................................................................................................... 8

3

データベース設計の最適化................................................................................................................. 9 3.1 正規化......................................................................................................................................... 9 3.2 大容量のデータ型........................................................................................................................ 9 3.3 列の順序 ..................................................................................................................................... 9 3.4 索引 .......................................................................................................................................... 10 索引のサイズ..................................................................................................................... 10 3.4.1 3.4.2 一般的な索引の問題点を回避する ..................................................................................... 10 3.4.3 正しい索引タイプを選択する ............................................................................................ 11 3.4.4 ROWID を特殊な索引として使用する ............................................................................... 11 3.4.5 複数の索引を文で使用する................................................................................................ 11 3.4.6 索引の選択を最適化する ................................................................................................... 12 3.5 トリガ....................................................................................................................................... 12 3.6 レコード数の影響...................................................................................................................... 12

4

データベース処理の最適化............................................................................................................... 13 4.1 データベース処理を実行するオプション................................................................................... 13 4.2 処理の最適化 – 一般的なヒント................................................................................................. 13 4.2.1 単一 SQL クエリの複数の処理を最適化する ..................................................................... 13 4.2.2 トランザクションを使用して複数の処理を最適化する ...................................................... 13 4.2.3 Prepare 文をキャッシュおよび再利用する ........................................................................ 13 4.2.4 集計関数と単関数を使用する ............................................................................................ 14 4.2.5 EXPLAIN キーワードを使用する ....................................................................................... 14 4.3 SELECT 処理の最適化方法 ....................................................................................................... 14 4.3.1 索引を使用して WHERE 句の評価を高速化する................................................................ 14 4.3.2 索引を使用して ORDER BY の評価を高速化する .............................................................. 16 4.3.3 LONG テキストまたは BLOB データを取得する ............................................................... 16 4.3.4 単一値の取得に最適のインターフェース ........................................................................... 17 4.3.5 RSqlStatement API の最適化 ......................................................................................... 17 4.3.6 サブクエリの中に定数部分式を配置する ........................................................................... 19 4.3.7 LIMIT および OFFSET を使用する .................................................................................... 19 4.4 INSERT 処理の最適化方法 ........................................................................................................ 19 単一レコードの挿入を最適化する ..................................................................................... 20 4.4.1 4.4.2 LONG テキストまたは BLOB データの挿入を最適化する ................................................. 20 複数のレコードの挿入を最適化する .................................................................................. 20 4.4.3 4.5 UPDATE 処理の最適化方法....................................................................................................... 21 4.5.1 単一レコードの更新を最適化する ..................................................................................... 21 4.5.2 LONG テキストまたは BLOB データの更新を最適化する ................................................. 21 4.5.3 複数のレコードの更新を最適化する .................................................................................. 22

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 2 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

4.6 5

ISSUED V1.1

DELETE 処理の最適化方法 ....................................................................................................... 22

RAM 使用率の最適化 ....................................................................................................................... 23 5.1 5.2 5.3 5.4 5.5

6

EXTERNAL - SYMBIAN

データベース接続...................................................................................................................... 23 事前作成された文...................................................................................................................... 23 複数のレコードの削除............................................................................................................... 23 大容量データの操作 .................................................................................................................. 23 一時テーブル............................................................................................................................. 23

データベース構成の最適化............................................................................................................... 25 6.1 6.2 6.3 6.4 6.5

自動バキューム ......................................................................................................................... 25 ページ サイズ............................................................................................................................ 25 キャッシュ サイズ..................................................................................................................... 26 データベースのエンコーディング ............................................................................................. 26 クエリのエンコーディング........................................................................................................ 26

7

マルチユーザー データベースの考慮事項......................................................................................... 27

8

マルチテーブル データベースの考慮事項......................................................................................... 28 8.1 トリガを使用した参照整合性 .................................................................................................... 28 トランザクションでのプログラム チェック ...................................................................... 29 8.1.1 8.1.2 トリガの使用..................................................................................................................... 29 8.1.3 パフォーマンスの考慮事項................................................................................................ 30 8.1.4 結論 .................................................................................................................................. 31 8.2 多対多関係の非正規化............................................................................................................... 31

9

その他の情報 ................................................................................................................................... 33 9.1 9.2

参考資料 ................................................................................................................................... 33 用語集....................................................................................................................................... 33

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 3 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

1 はじめに Symbian のデータベース製品は、DBMS と SQL Server の 2 つのコンポーネントで構成されます。この 2 つのコンポーネントは、内部アーキテクチャおよび外部 API が大きく異なり、機能セットとシステム特性 も違います。 特定のアプリケーションに SQL Server を使用するのが最適であると判断された場合、アプリケーション 開発者は SQL Server を最大限活用するために、そのアプリケーションの高度な開発が可能であることを 望みます。本書は、そうした開発者の活動を支援するために提供されています。 本書では、読者が十分な情報に基づき、DBMS ではなく SQL Server の使用が各自のアプリケーションに 最も適していると判断していることを前提としています。[R2] の指針や情報に基づいて、このような判断 に至る場合もあります。本書の情報を利用して、読者は対象とするアプリケーションの各種要件と使用パ ターンに基づき、SQL Server を最大限活用できる方法でアプリケーションを開発することができます。

1.1

目的、適用範囲、対象読者

本書の主な対象読者は、ライセンシーのアプリケーション開発者です。本書は、それらの開発者が各アプ リケーションにおいてのSQL Server の最適な使用方法を理解できるよう支援することを目的としていま す。本書では、特にデータベースの処理速度に重点を置いて、最大のパフォーマンス ゲインを達成する方 法と、RAM の使用率を最小限に抑えるためのオプションについて解説します。また、読者が各自のアプ リケーションをできる限り効率よく最適化できるよう、一般的なヒントと防止策も提示します。

1.2

確認事項

本書の注意事項には、独自または公共の各種情報源から引用または応用された内容が含まれています。 [R1] ~ [R7] を参照してください。

1.3

重要な注意事項

本書は、Symbian OS v9.3 に同梱される Symbian SQL Server のパフォーマンスを最適化する方法につい て助言するものです。このバージョンの SQL は、SQLite version 3.3.13 を基盤にしています。 読者は、本書の指針や情報が他のSymbian OS リリース関しても絶対的に正しい、あるいは関連性があ ると見なすことはできません。他の Symbian OS リリースの詳細については、各リリースのマニュアルを 参照してください。 読者は、SQLite の公式マニュアルを参照することもできます。 Symbian OS の今後のリリースは、バージョンの異なる SQLite を基盤にします。 ·

Symbian は現在、Symbian OS v9.4 で SQLite version 3.3.17 にアップグレードすることを計画してい ます。このリリースでは、特定のユースケース向けに I/O の最適化機能が提供される予定です。

·

Symbian は現在、将来のリリースに SQLite version 3.4.x を導入することを計画しています。これに は、増分 BLOB I/O のサポートも含まれる予定です。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 4 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

2 SQL Server の概要 この章では、Symbian の SQL Server の概要を示します。ここでは、最適化に関連する背景情報と、後述 する推奨事項について一定の見通しを示す背景情報を提供することを目的としています。

2.1

SQL Server アーキテクチャの概要

SQL Server は、SQLite [R1] を使用します。SQLite は、オープン ソースのリレーショナル データベース ライブラリで、ACID に対応しています。SQL Server では、標準的な Symbian OS クライアント / サーバ アーキテクチャを介してデータベースにアクセスできます。

クライアント アプリケーション

sql.dl sql.dll l

SQ

Event

SQL クライアント API SQ L

クライアント プロセス サーバ プロセス

sqlserver.e sqlserver.exe

Server

IPC

SQL Server

xe

SQL Server

SQ

Confi

SQLite SQLit

Exten

e

Symbian OS Adaptor

OS サービス

E32

F32

上の図に示すように、クライアント アプリケーションは SQL DLL とリンクします。SQL DLL は、IPC の 境界を越えて SQL Server にデータベース要求を送信するための API を提供します。要求は、データベー ス、テーブル、データ、索引等の作成、削除、変更を目的に行うことができます。 一般に使用されるクライアント API のクラスは次のとおりです。 RSqlDatabase – これは、サーバを介して SQL データベースへのハンドルを提供するクラスで、データ ベースの作成、オープン、クローズ、アタッチ、またはデータベースでの SQL 文の実行に使用できます。 RSqlStatement – このクラスは SQL 文を表し、あらゆるタイプの SQL 文の作成および実行に使用でき ます。 SQL Server は、SQLite API の周辺に薄い API ラッパーを保持し、クライアント要求の処理に使用します。 SQL Server は、複数のクライアント接続を処理でき、Symbian OS プラットフォーム セキュリティ モデ ルを使用してデータベースへのアクセスを制御します。 データベース システムとの下位のファイル インターフェースは、Symbian OS File Server(F32)コンポ ーネントによって提供されます。上の図の Symbian OS Adaptor 層は、SQLite の下位移植層 API と独自 の File Server API 間の適合層です。これにより、SQLite を Symbian OS で実行できます。 SQL Server アーキテクチャの詳細については、[R7] を参照してください。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 5 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

2.2

EXTERNAL - SYMBIAN

ISSUED V1.1

SQLite アーキテクチャの概要

以下の項では、SQLite アーキテクチャの主な機能を取り上げます。

2.2.1

データベース ファイルのフォーマット

SQLite データベース ファイルは、サイズが固定された 1 つ以上の ページで構成されます。ページとは、 データベースがディスクへの読み書きに使用する単位です。先頭ページには 100 バイトのヘッダが含まれ ます。このヘッダは、ファイルを SQLite データベースとして識別し、ファイル フォーマットのバージョ ン番号、ページ サイズ、自動バキュームの有効 / 無効などの処理パラメータを格納します。 各 SQLite デ ー タ ベ ー ス に は 、 SQLITE_MASTER と い う 特 殊 な テ ー ブ ル も 含 ま れ て い ま す 。 SQLITE_MASTER テーブルには、データベース スキーマと、データベース内に存在するすべてのテーブ ルおよび索引に関する情報(具体的には、テーブルおよび索引を作成する CREATE TABLE 文と CREATE INDEX 文)が格納されます。

2.2.2

SQLite サブシステム

SQLite の主要サブシステムを簡単なブロック図で示します。各サブシステムについて、以下で簡単に説 明します。 2.2.2.1

SQL コンパイラ

SQL コンパイラ

処理対象の SQL 文は、SQL コンパイラ モジュールに送信され ます。このモジュールは、SQL 文の構文解析と意味解析を行い、 各文を実行するバイトコードを生成します。 2.2.2.2

仮想マシン 生成されたバイトコードは、仮想マシンに渡されて評価されま す。仮想マシンは、データベースを B-Tree [R4] のセットと見な します。各テーブルおよび索引に対し、1 つの B-Tree がありま す。B-Tree の作成、読み取り、書き込み、更新、バランス、お よび破棄の論理は、B-Tree モジュールに格納されます。

2.2.2.3

仮想マシン

B-Tree

ページャー

B-Tree モジュール B-Tree モジュールは、データベースで使用されるページのリス トを管理し、ページャー モジュールを使用してデータベース フ ァイルの各ページにアクセスします。

OS インターフェース

各テーブルは個別の B+Tree [R5] であり、テーブルの各レコードは、B+Tree の該当するノード内 の個別のエントリです。B+Tree キーは、レコードの ROWID または INTEGER PRIMARY KEY であり、B+Tree エントリの先頭に格納されます。レコード内のそれ以外のすべての列は、エント リのデータ部に格納されます。レコードにデータ セグメントの容量を超えるバイト数が含まれる 場合は、「オーバフロー」ページが作成されます。オーバフロー ページの作成後、データの大部 分はオーバフロー ページのノードに格納されますが、必ず最小限のデータが元のノード エントリ に格納されます。そのレコードに対応するオーバフロー ページのリンク付きリストを示すポイン タは、元のノード エントリに格納されます。 索引は、索引付けするテーブルの各レコードに対応する B-Tree の該当するノードに、個別の BTree として 1 つのエントリと共に格納されます。B-Tree キーは、索引が付けられた列の値で構 成され、B-Tree エントリの先頭に格納されます。その後ろに、テーブル内の関連するレコードの ROWID が格納されます。索引 B-Tree では、B-Tree エントリのデータ部は使用されません。 2.2.2.4

ページャー モジュール ページャー モジュール(単に「ページャー」とも呼ばれる)は、データベース ファイルからの生 データの読み取りおよび書き込みを管理します。たとえば、読み取りは常に一貫性があって独立 しており、書き込みはアトミック性を確保するよう管理されます。ページャーは、データベース

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 6 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

ファイルを連続した 0 個以上の固定サイズのページと見なします。各ページのデフォルト サイズ は 1,024 バイトです。 ページャーは、最近アクセスされたデータベース ページのキャッシュも保存し、ページ要求への 応答でディスク I/O が必要になる頻度を最小限に抑えます。 2.2.2.5

OS インターフェース 基本ファイル システムとオペレーティング システムとのすべての対話は、OS インターフェース モジュールを介して行われます。したがって、OS インターフェース モジュールのカスタム バー ジョンを実装するだけで、SQLite を新しいプラットフォームに簡単に移植できます。

2.2.3

ロールバック ジャーナル ファイル ロールバック ジャーナルは、データベース ファイルとは別のファイルで、データベースと同じ名 前に接尾辞の「–journal」が付きます。1 つのデータベースにつき、ジャーナル ファイルは 1 つだ け存在します。ロールバック ジャーナルによって、データベースを安全な方法で更新することがで きます。なぜなら、更新が正常に完了しなかった場合、ロールバック ジャーナルはデータベースを 元の状態に「ロールバック」できるからです。データベースを更新する場合、ページャーはまず、 更新するページの現在のコンテンツをジャーナル ファイルに書き込む必要があります。その後、デ ータベース ファイルのページを更新できます。データベースが正常に更新され、ディスクにコミッ トされると、ジャーナル ファイルは削除されます。たとえば、データベースの書き込み処理で停電 が発生した場合は、起動時にジャーナル ファイルの存在が検出されます。これは、更新が正常に完 了しなかったため、ジャーナル ファイルに保存されたデータを使用して、データベースが前の状態 に自動的にロールバックされたことを意味します。 複数のデータベースがアタッチされている(ATTACH コマンドを使用)場合は、各データベースに それぞれ独自のジャーナル ファイルがあり、マスタ ジャーナル ファイルによって個々のジャーナ ル ファイルへの参照が維持されます。

2.2.3.1

ロールバック トランザクション

ロールバックには、次に示す 2 つのシナリオがあります。 単一の暗黙的トランザクション。以下に例を示します。 UPDATE table1 SET i1=2 WHERE i1==1 先述したように、トランザクションが失敗すると、ジャーナル ファイルの存在が検出されるので、 SQLite は自動的にトランザクションのロールバックを処理します。 一括トランザクション、すなわち「BEGIN」から「COMMIT」までのブロックで連続して実行され る単一処理。ロールバックのメカニズムは開発者が提供する必要があります。以下に例を示します。 static void DoRollback(void* aDbHandle) { } RSqlDatabase db; CleanupClosePushL(db); TInt err = db.Open(); // err should be KErrNone CleanupStack::PushL(TCleanupItem(&DoRollback, &db)); db.Exec(_L(“BEGIN”));

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

// start of bulk transaction

EXTERNAL - SYMBIAN

PAGE 7 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

<some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(1, 18)”)); <some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(2, 56)”)); <some code that could leave> db.Exec(_L(“INSERT INTO table1 (i1, i2) values(3, 32)”)); db.Exec(_L(“COMMIT”)); // end of bulk transaction CleanupStack::Pop(); // TCleanupItem CleanupStack::PopAndDestroy(); // db 上記のコードでは、クリーンアップ アイテムがクリーンアップ スタックにプッシュされます。このクリ ーンアップ アイテムは、クリーンアップ関数 DoRollback() とその関数が実行されるオブジェクト、すな わちデータベース接続をカプセル化します。「BEGIN」と「COMMIT」の間で途中終了が発生すると、こ の関数が呼び出され、関数内部でデータベース接続の「ROLLBACK」処理が実行されます。この処理に より、途中終了が発生する前に正常に実行された各 INSERT 処理がロールバックされるため、データベー スは「BEGIN」~「COMMIT」ブロックが実行される前と同じ状態で残されます。

2.3

SQL Server の制限

SQL Server には、次の制限が適用されます。制限の大半はネイティブの SQLite の制限によるもので、そ れ以外は Symbian OS の制限に起因します。たとえば、特定のファイル システムとメディアに対し、 Symbian OS のファイル サイズは 2GB、ファイル バッファ サイズは 256MB に制限されています。 ·

データベース ファイルの最大サイズは 2GB(Symbian OS の制限)

·

最大レコード数は 2^64 -1(SQLite の制限)

·

アタッチデータベースの最大数は 10(SQLite の制限)

·

最大ページ サイズは 32,768 バイト(SQL Server のデフォルト ページ サイズは変更可能、6.1 を参 照)

·

1 つのデータベースにおける最大ページ数は 1,073,741,823(SQLite の制限)

·

SQL クエリで使用するワイルドカード演算子「?」の最大数は 999(SQLite の制限)

·

テーブル、列、索引、トリガ、またはビューの名前にサイズの制限はありません。

·

複合索引の列数に制限はありませんが、列数が 30 を超える索引は、クエリの最適化(たとえば、 SELECT 文の WHERE 句に索引を使用するなど)に使用できません(SQLite の制限)。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 8 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

3 データベース設計の最適化 この章では、データベースのパフォーマンスを強化するためにデータベース スキーマ レベルで実行でき る各種の最適化について説明します。

3.1

正規化

データベースの正規化は、データの重複を低減するためにリレーショナル データベースで利用されている 手法で、これによってデータの整合性の問題を引き起こす挿入、削除、更新の異常が発生する可能性を除 去します。冗長データを維持する方が適切な場合もあるため、ケースバイケースの原則で判断する必要が あります。 テーブル データに適用できる正規化にはいくつかの段階があり、この段階のことを「正規形」という用語 で表します。「第 1 正規形」(「1NF」)は、最も初期の段階の形式です。「テーブルの各値はアトミッ ク性を確保し、レコードは一意に識別できなければならない」と規定することで、テーブル「セル」内の グループを繰り返す可能性を除去します。「第 2 正規形」(「2NF」)に適合する基準は、1NF の基準の 上位集合です。一般に、必要なテーブル数が多いほど、データを正規化する必要性が高くなります。 正規化と非正規化は、マルチテーブル データベース環境で一般的に利用されるコンセプトです(8 章を参 照)。

3.2

大容量のデータ型

LONG テキストや BLOB(Binary Large Object)データなど大容量のデータ型をテーブルに含める必要が あるかどうかを考慮することは重要です。たとえば、検索中に多数の BLOB を確認する必要がある場合、 大容量の BLOB があると検索時間に大きく影響します。 BLOB が含まれたレコードを格納するためにオーバフロー ページが必要になると、状況はさらに悪化しま す。オーバフロー ページは、レコード サイズがページ サイズが超えると必要になり、これは大容量の BLOB がレコードに含まれている場合にはよくあることです。その場合、オーバフロー ページのデータも 取得して確認する必要があるため、検索速度はさらに遅くなります。 さらに、レコードのオーバフロー ページを作成または取得する必要がある場合、そのレコードを挿入また は更新する時間が長くなります。また、そのようなレコードを取得することは望ましくありません。なぜ なら、レコードを再構築するために、そのレコードに対応するページのリンク付きリストをすべて確認す る必要があり、これは効率的な処理ではないからです。 上記の問題を踏まえて、BLOB データが必要な場合は、次のいずれかのアプローチを検討する必要があり ます。

1. BLOB データを、共通キーによって元のテーブルにリンクされた個別のテーブルに分離する。 2. BLOB 列をテーブル スキーマの最終列として配置する。 上記の推奨事項は、レコードの取得中、それほど頻繁に BLOB 列を選択する必要がない場合に有益です。 大容量 BLOB をデータベースの外部(別個のファイル)に格納し、ファイルの場所の参照をテーブルの値 として保持しておくと、最適化をさらに進めることができます。SQLite では、BLOB の増分書き込みまた は増分読み取りができず、BLOB 全体を一度に読み取りまたは書き込みする必要があるため、BOLB を外 部に分離するこの方法は便利です。たとえば、10MB の BLOB の最後に 100 バイトを付加するには、 SQL Server は BLOB 全体を読み取り、100 バイトを付加し、もう一度ディスクに 新しい BLOB を書き戻 す必要があります。別のファイルに BLOB を配置しておくと、10 MB の BLOB にアクセスしなくても 100 バイトをすばやく効率的に付加できます。

3.3

列の順序

スペースの効率上の理由から、各レコードは圧縮形式で格納されます。これにより、各ページにより多く のレコードを割り当てられるので、ディスク I/O も最小限に抑えられます。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 9 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

ただし、データベースがテーブルの n 番目の列の値を取得するには、まずその前にあるすべての列を解凍 しなければならないという欠点があります。したがって、テーブル内のアクセス頻度が最も高い列が、テ ーブル スキーマの先頭にあるのが最適の状態です。 これは、整数列などのように容量のごく小さいデータを格納しているテーブルの列にも当てはまります。 このような列は、テーブルの先頭近くに配置するのが適しています。先述したように、BLOB 列などの大 容量のデータ列は、できる限りテーブルの末尾に配置します。

3.4

索引

索引は、データベースからのレコードのルックアップ速度を大幅に高める上で極めて重要です。なぜなら、 索引を使用したルックアップでは、線形のフル テーブル スキャンではなく、バイナリ検索技術が使用さ れるからです。 アプリケーションを開発する場合、データ ルックアップの速度を高める必要性が明確になるまでは、索引 を作成しないことをお勧めします。なぜなら、適切にあるいは実用本位で使用されないと、索引はデータ ベースのパフォーマンスを全体的に低下させるからです。索引を追加すると、ルックアップ速度は大幅に 向上しますが、レコードの挿入または削除にかかる時間が増大する可能性があります。また、更新する列 に索引が付いていると、レコードの更新にかかる時間も増大する場合があります。

3.4.1

索引のサイズ

SQLite では、索引は SQLite 自身の B-Tree に格納され、テーブル データから切り離されています。BTree の葉 1 枚につき、最低 4 つのエントリがあります。B-Tree のエントリの最大サイズは約 230 バイト です。サイズの大きいデータが格納されている列に索引が付いていると、索引 B-Tree がオーバフロー ペ ージを使用する原因となります。この状態は、先述した理由により、できる限り避ける必要があります。

3.4.2

一般的な索引の問題点を回避する

3.4.2.1

明示的および暗黙的索引

通常、索引は「CREATE INDEX」SQL コマンドを使用して明示的に作成されます。以下に例を示します。 ‘CREATE INDEX idx1 on Table1(i3)’ ここで i3 は、テーブル Table1 の 3 番目の列です。 代わりに、テーブル スキーマ内の特定タイプの列を指定すると、索引は暗黙的に作成されます。この方法 は、列が UNIQUE または PRIMARY KEY として指定されている場合に使用されます。列が INTEGER PRIMARY KEY として宣言されている場合、新しい索引は作成されませんが、指定された列は組み込み ROWID 索引の別名として扱われます(3.4.3 を参照)。 明示的索引が暗黙的索引と重複しないよう注意してください。 3.4.2.2

索引によるリソースへの影響

新しい索引の作成にはコストが伴うことを認識しておくのは重要です。 ·

新しい索引は、データベース ファイルにそれぞれの B-Tree を格納できる追加スペースを必要 とします。作成される索引が多いほど、テーブル データは付加されずにデータベース ファイ ルが大きくなります。重複や冗長な索引を回避するよう、十分注意する必要があります。以 下に例を示します。 o 冗長な索引 CREATE TABLE Table1(i1 INTEGER, i2 INTEGER) CREATE INDEX idx1 on Table1(i1) CREATE INDEX idx2 on Table1(i1,i2) o 重複した索引 CREATE TABLE Table1(ID INTEGER UNIQUE); CREATE INDEX idx1 on Table1(ID)

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 10 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

3.4.3

EXTERNAL - SYMBIAN

ISSUED V1.1

·

BLOB 列などの大容量データが含まれた列には索引を作成しないようにします。これにより、 索引 B-Tree でオーバフロー ページの使用が必要になるのを防ぎます。

·

すべての挿入および削除処理は、データ テーブルの B-Tree だけでなく、そのテーブルの各索 引の B-Tree も変更します。したがって、索引の数が増えるほど、挿入および削除処理のパフ ォーマンスは低下します。これは、索引付きの列を更新する場合にも当てはまります。

·

索引が重複していると、オプティマイザが使用する索引の選択を誤る可能性が高くなります。 たとえば、上記の冗長な索引で、クエリで使用する列として i1 が指定されている場合、オプ ティマイザは idx2 を選択する可能性があります。

正しい索引タイプを選択する

索引にはさまざまなタイプがあり、索引を選択する前に、各タイプの長所と短所を考慮することが重要で す。使用できる各種の索引と、それらに最も適した環境を次に示します。 ·

標準索引 – 標準索引は、順序付けられた値のリストで、重複する値が許可されています。文 の WHERE 句の検索フィールドで使用される索引として、デフォルトで選択されます。

·

一意索引 - 一意索引は、順序付けられた値のリストで、重複する値は許可されません。一意 索引は、データの整合性を維持するために、テーブルの列または列のセットに制約を適用す る場合に使用できます。テーブル内には、プライマリ キー索引は 1 つしか含めることができ ませんが、一意索引は複数含めることができます。一意性に関する制約を設定すると、一意 索引に影響する処理は、標準索引の場合よりも若干遅くなります。これは、一意性の制約が 守られていることを確認する必要があるためです。

·

プライマリ キー索引 – データの正規化では、テーブルにプライマリ キーが含まれていること が重要です。プライマリ キーの値は、各レコードに対して一意の値です。対象とするテーブ ルによっては、複数の列で一意のプライマリ キーを構成する方が適している場合もあります が、通常は単一の整数プライマリ キーを使用するのが最良の方法です。単一の整数プライマ リ キーは、組み込み ROWID 索引の別名として指定されるので、索引のフットプリントが最 も小さいからです。さらに、索引キーの値は個別の B-Tree ではなくテーブルの B+Tree のエ ントリに格納されるため、最高のパフォーマンスを得ることができます。プライマリ キーの 短所は、値が一意であり null でないことを確実にするために必要な確認があるため、プライ マリ キーに関する処理の速度が低下することです。

·

マルチキー索引 – この索引は複合索引とも呼ばれ、複数の列で構成されます。マルチキー索 引では、列の順序を指定する際に注意が必要です。たとえば、列のいずれかが一意の列であ る場合、その索引では、一意の列の後ろに一意でない列を配置することはできません。

3.4.4 ROWID を特殊な索引として使用する 各テーブルの各レコードは、一意の 64 ビット整数型の値 ROWID を持ちます。ROWID は、テーブル デ ータを格納する B+Tree のキーであり、同じテーブルのその他のすべてのレコードにおいて一意です。一 般に、ROWID を使用するデータ検索は、他の検索方法の約 2 倍高速です。これは、ROWID 索引キーと 共にレコード データが格納されているからです。 ROWID には、特殊な列名である ROWID、_ROWID_ または OID のいずれかを使用してアクセスできま す。あるいは、テーブルの作成時に整数列のキーワード INTEGER PRIMARY KEY を使用して、そのテー ブルの整数列を ROWID の別名として宣言することもできます。

3.4.5

複数の索引を文で使用する

オプティマイザは、文を評価する際、1 つのテーブルに対して 1 つの索引だけを使用します。したがって、 複数の列を含む WHERE 句では、マルチキー索引を使用すると便利な場合があります。それ以外の場合、 索引は 1 つしか使用されません。 たとえば、次のような文があります。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 11 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

SELECT * FROM Table1 WHERE a = 5 and b < 10; 列 a および b に、以下の索引がそれぞれ作成されます。 CREATE INDEX idx1 ON Table1(a); CREATE INDEX idx2 ON Table1(b); オプティマイザによって索引が 1 つだけ使用され、他の索引は不要になります。 以下のように、a と b 両方の列を含むマルチキー索引を使用する方法もあります。 CREATE INDEX idx3 ON Table1(a,b); この場合、idx3(a と b の複合列に作成されたマルチキー索引)が使用され、列 a および b を最適の方法 で評価に使用することができます。マルチキー索引を使用する場合、WHERE 句で指定されたすべての索 引列は = または IN 演算子を付けて使用する必要があります。ただし、不等演算子を付けて使用できると 指定されている右端の列は除きます。

3.4.6

索引の選択を最適化する

先述のように、文の実行に複数の索引を使用できる場合、使用する索引はオプティマイザによって選択さ れます。この選択は、必ずしも明確、または、予想通りであるわけではありません。 オプティマイザを支援するために、ANALYZE コマンドを使用して、存在する索引に関する統計情報を格 納するテーブル「sqlite_stat1」を作成できます。この情報は、より適切な索引を選択するために、クエリ オプティマイザによって使用されます。

3.5

トリガ

トリガとは、事前に指定されたイベントが発生すると自動的に起動されるデータベース処理です。たとえ ば、テーブル X で削除イベントが発生するとテーブル Y からレコードを削除するトリガを作成できます。 トリガは、更新、削除、または挿入イベントで作成されます。トリガを使用して、外部キーの制約を適用 することができます。8.1.2 を参照してください。

3.6

レコード数の影響

索引を使わずにデータベースでレコードの場所を特定するには、フル テーブル スキャンを実行する必要 があります。当然、この処理の完了に要する時間は、テーブルのレコード数に比例して増大します。 検索列に索引が付いていると、ルックアップ時間は大幅に向上します。大量のレコードを含むテーブルで は、索引の使用はアプリケーションのパフォーマンスにとってきわめて重要です。 一般に、テーブルのレコード数が少ないほど、処理の実行時間は短くなります。したがって、不要になっ たレコードをただちにテーブルから削除することで、データ処理における最適のパフォーマンスを実現で きます。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 12 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

4 データベース処理の最適化 4.1

データベース処理を実行するオプション

データベース処理の最も簡単な実行方法は、RSqlDatabase::Exec() を使用して SQL クエリを実行す ることです。これは、ほとんどの単一 SQL クエリ処理に当てはまりますが、複数の SQL クエリ処理など その他の環境下では、別の API を使用してパフォーマンスを高めることが推奨されます。以下の項では、 さまざまな処理を最適化する方法について説明します。 ここに示すコード例で、疑似コード「」は、RSqlDatabase のインスタンスを表しています。

4.2

処理の最適化 – 一般的なヒント

この項では、処理を最適化するための指針とヒントを示します。

4.2.1

単一 SQL クエリの複数の処理を最適化する

ここでは、1 つの処理を複数回実行し、そのたびに異なるデータ値を使用する場合を対象としています。 複数の文を直接実行するのではなく、バインド変数を含む 1 つの文を使用します。次に例を示します。 RSqlStatement stmt; CleanupClosePushL(stmt); CleanupStack::PushL(TCleanupItem(&DoRollback, &)); .Exec(_L(“BEGIN”)); stmt.PrepareL(, _L(“INSERT INTO values(?,?,… )”)); for (TUint i=0; i < <no_of_rows_to_insert>; ++i) { User::LeaveIfError(stmt.Exec()); User::LeaveIfError(stmt.Reset()); } .Exec(_L(“COMMIT”)); CleanupStack::Pop(); // TCleanupItem CleanupStack::PopAndDestroy(); //stmt

4.2.2

トランザクションを使用して複数の処理を最適化する

明示的に指定されているかどうかに関わらず、データベース処理を表すすべての SQL 文は、常にトラン ザクションの中で実行されます。つまり、N 個の処理を明示的トランザクションにラッピングせずに指定 すると、N 個のトランザクションが作成され、データベースに N 回コミットされます。これは、N 個の処 理をすべて完了するために要する時間に非常に大きく影響します。N 個の処理をすべて 1 つの明示的トラ ンザクションの中で実行する方がはるかに効率的です。 トランザクションの存続期間は短くするよう注意する必要があります。別のトランザクションが予約ロッ クを保持している間、他のプロセスはデータベースへの書き込みができないからです。

4.2.3

プリコンパイル済みステートメントをキャッシュおよび再利用する

SQL 文がアプリケーションによって再利用される可能性が高い場合、最も頻繁に使用される文を作成した ら、その文をキャッシュに格納しておくと役に立つことがあります(RSqlStatement::Prepare())。 なぜなら、各 SQL 文に対し、SQL サーバは文を実行する前にそれを解析およびコンパイルする必要があ COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 13 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

るため、文をキャッシュに格納しておくと、次にその文が必要になったとき、解析とコンパイルに費やす 時間を省略できるからです。キャッシュされた文に 1 つ以上のバインド変数が含まれている場合は、実行 前に新しい値をそのパラメータに容易に結合できるので、各呼び出しでわずかに異なる処理を実行しやす くなります。 ただし、キャッシュされた各 SQL 文は最大 1KB の RAM を使用する(5.2 を参照)ので、このキャッシ ュ アプローチを使用すると、処理の実行速度と RAM 使用率の間にトレードオフが生じることに注意して ください。

4.2.4

集計関数と単関数を使用する

COUNT() などの集計関数は、複数の値で動作しますが、単一の結果を返します。一方、random() などの 単関数を使用すると、SQL エンジン内でより複雑な処理を実行できます。使用可能な関数の複雑さを理解 しておくと、アプリケーションが通常実行するデータ処理の量を低減するのに役立ちます。 集計関数と共に GROUP BY および HAVING キーワードを使用すると、必要な順序と制約をそれぞれ設定 できます。

4.2.5

EXPLAIN キーワードを使用する

EXPLAIN キーワードを SQL コマンドの前に配置すると、コマンドを実行するために取られた一連の内部 手順のレポートを取得できます。このキーワードは、たとえば文の実行時に索引が使用されるかどうか、 使用されるのはどの索引か、仮テーブルは作成されるか、などを理解する上で非常に役に立ちます。詳細 については、[R1] を参照してください。

4.3

SELECT 処理の最適化方法

SELECT 処理は、さまざまな方法で最適化できます。たとえば、WHERE 句で索引を使用すると、線形の フル テーブル スキャンではなく、バイナリ検索を実行できます。 以下の項では、SELECT 文を最適化する各種方法と、注意すべき共通の問題点について説明します。

4.3.1

索引を使用して WHERE 句の評価を高速化する

文の WHERE 句で索引を使用すると、必要なレコードを検索する時間を大幅に短縮できます。 ただし、WHERE 句が含まれた処理の実行時に索引を使用するには、従うべき規則がいくつかあります。 以下の例では SELECT 処理を使用していますが、WHERE 句に関する規則は UPDATE や DELETE など 他の処理にも適用されます。 ·

規則 1: WHERE 句では、索引付き列に関する演算子式を使用しない。 例 CREATE TABLE T1(i1 INTEGER, i2 INTEGER, t3 TEXT) CREATE INDEX idx1 on T1(i1) // 次の場合は索引が使用されます。 SELECT * FROM T1 WHERE i1=2 // 次の場合は索引が使用されません。 SELECT * FROM T1 WHERE i1+1=4

·

規則 2: WHERE 句で結合(AND)演算子が使用されている場合は、索引が使用される。 例 // 次の場合は索引が使用されます。 SELECT * FROM T1 WHERE i1=2 AND i2=4

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 14 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

·

EXTERNAL - SYMBIAN

ISSUED V1.1

規則 3: WHERE 句で IN 演算子が使用されている場合は、索引が使用される。 例 // 次の場合は索引が使用されます。 SELECT * FROM T1 WHERE i1 IN (5,7)

·

規則 4: WHERE 句で論理和(OR)演算子が使用されている場合、索引は使用されない。 ただし、例外として同一の列に対する複数の等式テストは内部的に IN 演算子を使った処理へ 変更されるため索引が使用されます。 例 // 式が SELECT * FROM T1 WHERE i1 IN(1,2,3) として再度書き込まれるので、 // この場合は索引が使用されます。 SELECT * FROM T1 WHERE i1=1 OR i1=2 OR i1=3 // WHERE 句に、異なる列と共に OR 演算子が含まれるので、この場合は // 索引が使用されません。 SELECT * FROM T1 WHERE i1=2 OR i2=3

·

規則 5: 複数列の索引では、WHERE 句の索引付き列の順序と索引の列の順序が合致していな ければならない。 例 CREATE TABLE T1(i1 INTEGER, i2 INTEGER, t3 TEXT); CREATE INDEX idx1 on T1(i1, i2, t3); // 複数列の索引 // 索引の 3 つの列がすべて使用されているので、この場合は索引が使用されます。 SELECT * FROM T1 WHERE i1=2 AND i2=3 AND t3=’abc’ // 索引の最初の 2 列が使用されているので、この場合は索引が使用されます。 SELECT * FROM T1 WHERE i1=2 AND i2=3 // 索引の先頭列が使用されているので、この場合は索引が使用されます。 SELECT * FROM T1 WHERE i1=2 // i1 が索引の先頭列であるため、この場合は索引が使用されません。 SELECT * FROM T1 WHERE i2=3 AND t3=’abc’ SELECT * FROM T1 WHERE i2=3

·

規則 6: 複数列の索引で、索引付き列のいずれかに不等式制約がある場合、その列は一番最 後に評価され、その後ろにある索引付き列は評価されない。規則 4 にも従う必要がありま す。 最大 1 つの索引付き列が不等式によって制約されます。ただし、列の上限または下限を示す 場合は、例外として同じ索引付き列に 2 つの不等式が許可されます。 例 CREATE TABLE T1 (i1 INTEGER, i2 INTEGER, t3 TEXT)

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 15 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

CREATE INDEX idx1 on T1(i1, i2, i3) // multi-column index // 次の場合、索引は使用されますが、t3 は評価されません。 SELECT * FROM T1 WHERE i1=2 AND i2<3 AND t3=’abc’ // 次の場合は索引が使用されます。 SELECT * FROM T1 WHERE i1>2 AND i1<7

4.3.2

索引を使用して ORDER BY の評価を高速化する

SELECT 文の ORDER BY 句で索引を使用すると、取得したレコードの順序付けにかかる時間を大幅に短 縮できます。 SELECT 文の ORDER BY 句を評価するデフォルトの基本メソッドでは、まず SELECT 文を評価して結 果を仮テーブルに格納し、次に ORDER BY 句に従って仮テーブルを並べ替え、最後に並べ替えた仮テー ブルをスキャンして最終的な出力を生成します。 可能であれば、索引を使用して結果セットの格納と並べ替えを省略できます。その場合、最初のパスの順 序でクエリから結果が返されます。 ただし、ORDER BY 句が含まれた処理の実行時に索引を使用するには、次の規則に従う必要がありま す。 ·

規則 1: ORDER BY 句の後に続く列の順序は、索引の列の順序と合致していなければならな い。ORDER BY 句の前に WHERE 句がある場合、ORDER BY 句で使用される列は WHERE 句で使用される列の直後になければならない。 例 CREATE TABLE T1(i1 INTEGER,i2 INTEGER,t3 TEXT); CREATE INDEX idx1 on T1(i1,i2,t3); // multi-column index // 次の場合は索引が使用されます。 SELECT * FROM T1 ORDER BY i1, i2, t3 SELECT * FROM T1 WHERE i1=2 ORDER BY i2, t3 SELECT * FROM T1 WHERE i1=2 AND i2=3 ORDER BY t3 SELECT * FROM T1 WHERE i1=2 ORDER BY i2 // i1 が索引の先頭列であるため、ここでは索引は使用されません。 SELECT * FROM T1 ORDER BY i2, t3 SELECT * FROM T1 ORDER BY i2 SELECT * FROM T1 WHERE i1=2 ORDER BY t3

4.3.3

LONG テキストまたは BLOB データを取得する

容量が非常に大きいデータを取得する必要がある場合、データ全体をまとめて取得しようとすると、使用 可能なメモリーが大量に必要になります。代わりに、データをいくつかの小さなブロックとして取得する ことをお勧めします。ただし、複数の IPC 呼び出しを伴うため、このアプローチは低速です。 例 RSqlStatement stmt; CleanupClosePushL(stmt); stmt.PrepareL(, _L(“SELECT * FROM
”)); COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 16 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

TInt err = KErrNone; while ( KSqlAtRow == (err = stmt.Next()) ) { // assume column1 is a BLOB RSqlColumnReadStream columnStream; CleanupClosePushL(columnStream); err = columnStream.ColumnBinary(stmt,0); TInt size = stmt.ColumnSize(0); // this call retrieves all of the data in one go but it // may be more optimal to loop and retrieve it in chunks columnStream.ReadL(,size); CleanupStack::PopAndDestroy(); // columnStream } CleanupStack::PopAndDestroy(); //stmt

4.3.4

単一値の取得に最適のインターフェース

TSqlScalarFullSelectQuery インターフェースは、SELECT SQL クエリの実行に使用されます。こ のクエリは、単一の列の値から成る単一レコードを返します。このインターフェースは、IPC 呼び出しが 少ないため、値の取得に最適です。 たとえば、テーブル内の特定の人物の ID 値を取得する場合、次のようになります。 TSqlScalarFullSelectQuery fullSelectQuery(); TInt personId = fullSelectQuery.SelectIntL(_L("SELECT WHERE Name = 'John'"));

4.3.5

ID

FROM

PersonTbl

RSqlStatement API の最適化

クラス RSQLStatement のさまざまな API を使用して、処理の実行をできる限り最適化することができ ます。 TInt RSqlStatement::ParameterIndex(const TDesC &aParameterName) const TInt SqlStatement::ColumnIndex(const TDesC &aColumnName) const 上記の API は、所定の名前を持つパラメータの索引と、所定の名前を持つ列をそれぞれ取得します。 処理の実行に使用されるコードには、パラメータ / 列インデックス / 列名を使用する方が適しています。 これらの API は、主な取得ループの外側で呼び出す必要があります。また、API をいずれかの結合メソ ッドのパラメータとして使用するのではなく、返された索引の値をループ内で格納し、再利用します。次 のコード例を参照してください。 RSqlStatement stmt; CleanupClosePushL(stmt); TInt err = stmt.Prepare(, _L("SELECT i1 FROM Tbl1 WHERE i1 > :Val"));

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 17 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

TInt paramIndex = stmt.ParameterIndex(_L(":Val")); err = stmt.BindInt(paramIndex, 5); TInt columnIndex = stmt.ColumnIndex(_L("i1")); while((err = stmt.Next()) == KSqlAtRow) { TInt val = stmt.ColumnInt(columnIndex); RDebug::Print(_L("val=%d\n"), val); } if(err == KSqlAtEnd) // OK - no more records else CleanupStack::PopAndDestroy(); //stmt TInt RSqlStatement::ColumnText(TInt aColumnIndex, TPtrC& aPtr) const TInt RSqlStatement::ColumnText(TInt aColumnIndex, TDes& aPtr) const 最初の API は、入力された TPtrC オブジェクトを、指定された列データのポイントにセットします(コ ピーは作成されません)。2 つ目の API は、指定された列データの値を 16 ビットディスクリプタとして 取得し、呼び出し側が入力した変更可能な 16 ビットディスクリプタにこのデータをコピーします。 TInt RSqlStatement::ColumnBinary(TInt aColumnIndex, TPtrC8& aPtr) const TInt RSqlStatement::ColumnBinary(TInt aColumnIndex, TDes8& aPtr) const 最初の API は、入力された TPtrC8 オブジェクトを、指定された列データのポイントにセットします (コピーは作成されません)。2 つ目の API は、指定された列データの値を 8 ビットディスクリプタとし て取得し、呼び出し側が入力した変更可能な 8 ビットディスクリプタにこのデータをコピーします。 TPtrC メソッドを使用すると、コピーの回数は少なくなります。TPtrC オブジェクトを使用する場合、 データがクライアント側のバッファに格納されると、TPtrC オブジェクトはそのバッファをポイントする ようにセットされます。TDes オブジェクトを使用すると、クライアント側のバッファのデータは、その TDes オブジェクトによって指定された場所にコピーされます。 例 1 – 大容量の列データの処理に関する SELECT 文。データは、クライアントによって渡されたバッフ ァにコピーされます。 RSqlStatement stmt; CleanupClosePushL(stmt); TInt err = stmt.Prepare(, _L("SELECT BinaryField FROM Tbl1")); TInt columnIndex = stmt.ColumnIndex(_L("BinaryField")); while((err = stmt.Next()) == KSqlAtRow) { TInt size = stmt.ColumnSize(columnIndex); HBufC8* buf = HBufC8::NewLC(size); err = stmt.ColumnBinary(columnIndex, buf->Ptr()); <do something with the data> CleanupStack::PopAndDestroy(); //buf

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 18 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

} if(err == KSqlAtEnd) // OK - no more records else CleanupStack::PopAndDestroy(); //stmt 例 2 – 大容量の列データの処理に関する SELECT 文。データは、コピーされずにクライアントによって アクセスされます。 RSqlStatement stmt; CleanupClosePushL(stmt); TInt err = stmt.Prepare(, _L("SELECT BinaryField FROM Tbl1")); TInt columnIndex = stmt.ColumnIndex(_L("BinaryField")); while((err = stmt.Next()) == KSqlAtRow) { TPtrC8 data; TInt err = stmt.ColumnBinary(columnIndex, data); if(err == KErrNone) { <do something with the data> } } if(err == KSqlAtEnd) // OK - no more records else CleanupStack::PopAndDestroy(); //stmt

4.3.6

サブクエリの中に定数部分式を配置する

クエリ内部で計算を行う必要がある場合、そのサブクエリに式を格納するとより効率的です。次に例を示 します。 // この場合、「function」はレコードごとに呼び出されます。 SELECT * FROM T1 WHERE t3 > function('abc'); // サブクエリの中に関数呼び出しを配置すると、より効率的です。 SELECT * FROM T1 WHERE t3 > (SELECT function('abc'));

4.3.7

LIMIT および OFFSET を使用する

LIMIT 句は、整数型の上限値を指定することにより、SELECT 文で返されるレコード数を制限します。オ プションの OFFSET キーワードは、返されたセットの先頭からいくつレコードを無視するかを指定しま す。文にサブクエリが含まれる場合、最後の SELECT 文でのみ LIMIT 句が使用されている場合がありま す。この場合、指定された制限は、LIMIT 句が含まれる SELECT 文だけでなく、クエリ全体に適用される ことに注意してください。

4.4

INSERT 処理の最適化方法

以下の項では、INSERT 文を最適化する各種方法について説明します。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 19 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

4.4.1

EXTERNAL - SYMBIAN

ISSUED V1.1

単一レコードの挿入を最適化する

単一の INSERT 処理の場合、最適の実行方法は API RSqlDatabase::Exec() を使用することです。 ただし、挿入するレコードに LONG テキストまたは BLOB データが含まれる場合を除きます。これにつ いて次で説明します。

4.4.2

LONG テキストまたは BLOB データの挿入を最適化する

挿入する 1 つ以上のレコードに LONG テキストまたは BLOB データが含まれる場合、最適の実行方法は RSqlStatement::Prepare() 文とバインド変数を使用することです。 BLOB 値を結合するには、次の 2 通りの方法があります。 o

RSqlStatement::BindBinary() を使用する(テキストの場合は BindText() を使用)

o

RSqlParamWriteStream::BindBinary() を使用する(テキストの場合は BindText() を使 用)

例 RSqlStatement stmt; CleanupClosePushL(stmt); stmt.PrepareL(, _L(“INSERT INTO
values(?,?,… )”)); for (TUint i=0; i < <no_of_rows_to_insert>; ++i) { // assume column1 is a BLOB and direct binding is to be used TInt err = stmt.BindBinary(0,<parameter_value>); // assume column2 is a BLOB and streaming is to be used RSqlParamWriteStream stream; CleanupClosePushL(stream); err = stream.BindBinary(stmt,1); stream.WriteL(<parameter_value>,); CleanupStack::PopAndDestroy(); // stream User::LeaveIfError(stmt.Exec()); User::LeaveIfError(stmt.Reset()); } CleanupStack::PopAndDestroy(); // stmt

4.4.3

複数のレコードの挿入を最適化する

複 数 の レ コ ー ド を 挿 入 す る に は 、 4.2.1 で 述 べ た 、 バ イ ン ド さ れ た 値 を 含 む 単 一 の RSqlStatement::Prepare() 文を使用します。 一括挿入の実行時間によってパフォーマンスに問題が生じる場合は、認められる処理速度の低下を緩和す る方法がいくつかあります。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 20 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

テーブルに索引がある場合は、一括挿入の前に、その索引を一時的に「消去」すると有効な場合がありま す。これにより、索引の作成および更新にかかる時間と、一括挿入の実行にかかる時間が分離され、アプ リケーションの応答性を改善できます。 あるいは、最初にレコードを仮テーブル(索引ではない)に挿入し、そのレコードを仮テーブルからメイ ン テーブルへ転送する方法もあります。この方法は、アプリケーションの応答性の制御にも役立ちます。 つまり、仮テーブルへの挿入処理がただちに実行され、レコードは「失われる」のではなく、おそらくは ブロック単位でメイン テーブルへ転送され、適切な場合は、アプリケーションの応答時間の要件を満たす ことができます。

4.5

UPDATE 処理の最適化方法

以下の項では、UPDATE 文を最適化する各種方法について説明します。

4.5.1

単一レコードの更新を最適化する

単一の UPDATE 処理の場合、最適の実行方法は API RSqlDatabase::Exec() を使用することです。 ただし、挿入するレコードに LONG テキストまたは BLOB データが含まれる場合を除きます。これにつ いて次で説明します。

4.5.2

LONG テキストまたは BLOB データの更新を最適化する

これは、LONG テキストまたは BLOB データが含まれた INSERT の場合と似ています。ここでも、 RSqlStatement::Prepare() 文とバインド変数を使用します。 例 RSqlStatement stmt; CleanupClosePushL(stmt); stmt.PrepareL(, _L(“UPDATE
SET column1=?, column2=?”)); for (TUint i=0; i < <no_of_rows_to_insert>; ++i) { // assume column1 is a BLOB and direct binding is to be used TInt err = stmt.BindBinary(0,<parameter_value>); // assume column2 is a BLOB and streaming is to be used RSqlParamWriteStream stream; CleanupClosePushL(stream); err = stream.BindBinary(stmt,1); stream.WriteL(<parameter_value>,); CleanupStack::PopAndDestroy(); // stream User::LeaveIfError(stmt.Exec()); User::LeaveIfError(stmt.Reset()); } CleanupStack::PopAndDestroy(); // stmt

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 21 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

4.5.3

EXTERNAL - SYMBIAN

ISSUED V1.1

複数のレコードの更新を最適化する

これも挿入処理の場合と似ています。複数のレコードを更新するには、4.2.1 で述べた、バインドされた 値を含む単一の RSqlStatement::Prepare() 文を使用します。

4.6

DELETE 処理の最適化方法

DELETE 処理の場合、それほど広範囲な最適化は期待できません。ただし一般的には、一括削除を実行す る(すなわち、多数のレコードを 1 つのトランザクションで一度に削除する)方が、各レコードを個別に 削除するよりもパフォーマンスは高くなります。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 22 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

5 RAM 使用率の最適化 この章では、アプリケーションが SQL を使用する際、RAM の使用率を最小限に抑える各種方法について 説明します。

5.1

データベース接続

シンボルテーブルは、データベース スキーマから作成され、接続がアクティブな状態で各データベース用 のメモリーに格納されます。このシンボルテーブルは、スキーマとほぼ同サイズで、SQL コンパイラによ って使用されます。スキーマが複雑な場合、シンボルテーブルは大きくなります。したがって、RAM の 使用率を最小限に抑えるには、スキーマをできる限り単純にし、不要になったデータベース接続を閉じて おくことが推奨されます。

5.2

事前作成された文

「プリコンパイル済み」ステートメント(4.2.3 を参照)は、キャッシュに格納されるとそれぞれ約 1KB の RAM を使用することに注意してください。これらの文の使用中は、メモリーの使用率が増大します。

5.3

複数のレコードの削除

DELETE 処理は、2 パス方式を使用して実行されます。最初のパスでは、削除対象のすべてのレコードが 特定され、その ROWID がメモリーに格納されます。2 番目のパスでレコードが削除されます。各 ROWID は、8 バイトのメモリーを使用します。特定の状況下でメモリー不足が深刻な場合は、削除処理 を管理しやすいいくつかの部分に分割することが推奨されます。 テーブルからすべてのレコードを削除するのは特殊なケースであり、その場合は ROWID をメモリーに格 納する必要はありません。

5.4

大容量データの操作

大容量のテキスト データおよび/または BLOB データの取得は、RAM 使用率への影響を抑えるために、ス トリームを使用して行う必要があります。

5.5

一時テーブル

ページ キャッシュ(6.3 を参照)には、現在使用中の各ページと、最近使用されたページが格納されます。 ページ キャッシュは、メイン データベース、アタッチデータベース、仮テーブル、および一時テーブル 用に維持されます。使用するメモリーの量を低減するには、不要なアタッチデータベース、仮テーブル、 および一時テーブルの使用をできる限り控えることが推奨されます。したがって、これらのデータベース およびテーブルのうち、明示的に作成する必要があるもの、暗黙的に作成される可能性があるもの、ユー ザーの意図と関係なく作成されるものを理解することが重要です。 アタッチデータベースと仮テーブルは、明示的に作成する必要があり、データベースの接続中、または、 切り離されるか破棄されるまで存続します。 一時テーブルは、クエリの中間結果を格納するため暗黙的に作成され、クエリの実行中のみ存続します。 一時テーブルが使用される状況は次のとおりです。 ·

オプティマイザが「平坦化」できないサブクエリが文に含まれている場合 例 SELECT * FROM t1 WHERE (SELECT ROWID FROM t2 WHERE ROWID < 50); これによって一時テーブルが作成されます。 サブクエリが IN キーワードの右側にある場にも、一時テーブルが作成されます。

·

DISTINCT キーワードが使用される場合

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 23 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

·

UNION、INTERSECT、または EXCEPT キーワードのいずれかが使用される場合

·

ORDER BY または GROUP BY キーワードのいずれかが使用される場合(要求に適合する索 引がない場合)

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 24 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

6

EXTERNAL - SYMBIAN

ISSUED V1.1

データベース構成の最適化

SQL データベースには、SQL Server で構成可能なさまざまなプロパティがあります。以下の項では、こ れらのプロパティについて個別に説明します。 プロパティには、コンパイル時に設定されるものと実行時に設定されるものがあります。ただし、コンパ イル時に SQL Server を変更すると、SQL Server を使用するすべてのクライアント アプリケーションお よびサーバのシステム全体が影響を受けます。したがって、このような変更は、絶対に必要であり、十分 考慮した場合にのみ、コンパイル時に行う必要があります。 実行時に構成可能なプロパティは、データベースの作成前に変更する必要がある場合と、作成後に変更で きる場合があります。 プロパティの設定は「sqlite_macro.mmh」で定義されます。 構成パラメータの規則を次に示します。 · · ·

デフォルトでは、上記の mmh ファイルのパラメータが使用されます(コンパイル時の構成)。 SqlServer.cfg ファイルがある場合は、このファイルで指定されたパラメータによってデフォルト パラメータが上書きされます。 構成文字列が RSqlDatabase::Create() などの API に渡されると、その構成文字列で指定さ れたパラメータによってデフォルトおよび SqlServer.cfg パラメータが上書きされます。この構成 設定は、該当する API に基づいて維持されます。

共有データベースのセキュリティを確保するために、PRAGMA コマンド ベースの設定は無効になります。 Symbian は、PRAGMA を使用する構成方法を推奨いたしません。

6.1

自動バキューム

自動バキューム設定は、SQL Server のコンパイル時に構成されます。一般に、自動バキュームがオフの 場合は、データベースからデータが削除された後も、データベース ファイルは同じサイズで維持されます。 自動バキュームがオンの場合、データが削除されるとデータベース ファイルは小さくなりますが、これは 削除した後にページが空になるかどうかによって異なります。 自動バキュームがオンに設定されたデータベースは、オフに設定されたデータベースよりもサイズが若干 大きくなります。これは、データベースにページを追跡するための追加情報を格納する必要があるためで す。 SQL Server の自動バキュームは、デフォルトでオンに設定されます。

6.2

ページ サイズ

ページ サイズは、データベースが作成される前に設定する必要があります。サポートされるページ サイ ズは、512、1,024、2,048、4,096、8,192、16,384、32,768 バイトです。サイズの上限は、コンパイル中 に SQLITE_MAX_PAGE_SIZE マクロの値を設定することで変更できます。最大上限は 32,768 バイトで す。 SQL Server のデフォルトのページ サイズは 1,024 バイトです。 ページ サイズおよびレコード サイズは、データベースのパフォーマンスに大きく影響することを認識し ておくことが重要です。データベースへのデータの読み書きはページという概念で行われるため、オーバ フロー ページを作成せずに 1 ページで使用できるスペースができる限り広くなるよう、ページ サイズを 設定することが不可欠です。たとえば、平均レコード サイズを 1.1KB、ページ サイズを 2KB とすると、 1 ページに 1 レコードしか格納できません。このため、各ページの約 50% は使用されませんが、データ ベースへの読み書きは行われます。この例の場合、2 レコードを取得するために 2 ページを取得する必要 があり、パフォーマンス上大きな損失となります。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 25 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

1 ページで多数のレコードに対応できるようページ サイズが設定され、テーブルのすべてまたは大半のレ コードに対する要求が主として一括処理である場合、こうしたページ サイズとレコード サイズの比率は、 処理速度を高めます。ただし、単一レコードの数バイトを更新するのみという処理がある場合は、大きい ページへ読み書きするための余分な時間が必要になります。対象となるアプリケーションの処理上の要件 に基づいてサイズのバランスを取ることが必要です。その結果、所定のアプリケーションに最適のページ サイズを判断するために、カスタマイズしたテストを実行する必要が生じる場合もあります。ただし、必 ず最低 1 レコードを格納できる大きさのページ サイズを設定し、オーバフロー ページがただちに必要に なる事態を避けることが重要です。

6.3

キャッシュ サイズ

キャッシュ サイズは、一度にメモリーに格納されるページ数を指定します。キャッシュ サイズが大きい ほど、SQL Server が処理を実行するために ディスク I/O を行う必要性が軽減されます。 ただし、キャッシュ サイズが大きいと、アプリケーションが使用する RAM の容量に大きな影響を与えま す。 SQL Server のデフォルトのキャッシュ サイズは 64 ページです。

6.4

データベースのエンコーディング

SQL Server は、UTF-8 および UTF-16 データベース エンコーディングをサポートしています(したがっ て、データベース ファイルの基本フォーマットは UTF-8 または UTF-16 です)。 SQL Server のデフォルトのデータベース エンコーディングは UTF-16 です。 UTF-8 でエンコードされたデータベースは、UTF-16 でエンコードされたデータベースよりもディスク フ ットプリントが明らかに小さくなります。ただし、データベース エンコーディングは、そのデータベース を使用するアプリケーションが処理できるテキスト文字およびエンコーディングに基づいて選択する必要 があります。たとえば、UTF-16 テキストを UTF-8 でエンコードされたデータベースに挿入すると、それ を格納するために UTF-16 テキストを UTF-8 フォーマットに変換するという余分な負担がかかります。

6.5

クエリのエンコーディング

SQL Server は、UTF-8 および UTF-16 クエリ エンコーディングをサポートしています。したがって、ア プリケーションは UTF-8 または UTF-16 でエンコードされた SQL 文を使用して、SQL Server API を呼び 出すことができます。異なるアプリケーションでさまざまなタイプのテキストを処理できるので、これは 便利です。データベース エンコーディングの場合と同様、アプリケーションのクエリ エンコーディング は、そのアプリケーションが処理できるテキスト文字およびエンコーディングに基づいて選択する必要が あります。データベース エンコーディングとクエリ エンコーディングの選択は、そのデータベースを使 用するアプリケーションの要件を考慮して、できる限り一致させる必要があります。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 26 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

7 マルチユーザー データベースの考慮事項 データの読み取りは、複数のプロセスが同じデータベースから同時に実行できますが、データベースへの 書き込みは、排他的ロックを取得した 1 つのプロセスだけが実行できます。SQL Server は、データベー スのロックをサポートしています。データベースごとに 1 つのテーブルを割り当ててそれぞれにロックを 割り当てると、テーブル レベルでロックをシミュレートできます。詳細については、[R1] を参照してく ださい。 書き込みの「飢餓状態(Starvation)」を防ぐには、トランザクションを短くし、ユーザーの対話から切り離 しておくことが推奨されます。別のトランザクションが予約ロックを保持している間、その他のプロセス はデータベースに書き込みできないからです。その場合、エラー コード KSqlErrBusy が返されます。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 27 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

8 マルチテーブル データベースの考慮事項 マルチテーブル データベースについては、SQLite で SQL92(SQL 標準の第 3 版)を広範囲にサポート することで、Symbian の SQL Server コンポーネントは DBMS コンポーネントを上回る大きな利点を備 えています。SQLite を使用する複数のテーブル処理には、SQL 文を使用するだけで対応でき、クエリを 実行するアプリケーション コードを変更する必要はありません。これは、開発時間とアプリケーション コードの保守性に多大な効果をもたらします。 SQL92 の SQLite 実装に欠けている機能のひとつは、外部キー制約の適用です。ただし、外部キー制約は トリガを使用して SQLite に簡単に実装することができます。トリガはデータベース スキーマの一部であ るため、このような実装によって、DBMS のようにアプリケーション コードで外部キー制約を維持する 必要がなくなります。 Symbian が実施した試験により、SQL Server を使用したマルチテーブル処理は、DBMS より明らかにす ぐれた、あるいは DBMS と同等のパフォーマンス特性を備えていることが証明されています。ただし、 最適のパフォーマンスを実現するには、スキーマとクエリを正しく設計することが依然として不可欠です。 この章では、SQL のマルチテーブルのパフォーマンスに固有の特色をいくつか取り上げます。まず、トリ ガを使用して参照整合性を維持する例を示します。さらに、リレーショナル データベースを使用して多対 多の関係を実装する場合の考慮事項について説明します。

8.1

トリガを使用した参照整合性

参照整合性とは、リレーショナル データベースのテーブル レコード間における関係性が一貫しているこ とを指します。 下の Contacts データベース スキーマの例で、PhoneNumbers テーブルの CONTACT 列は、Contacts テ ーブルのレコードを参照するために使用されます。この CONTACT 列を「外部キー」と呼びます。この 列の各値は、常に Contacts テーブルの列 ID の値に対応している必要があります。つまり、すべての PhoneNumbers.CONTACT 値のセットは、すべての Contacts.ID 値のサブセットであり、その他の値を含 めることはできません。通常、この制約を「外部キー制約」と呼びます。 参照整合性を維持するには、PhoneNumbers.CONTACT または Contacts.ID のいずれかを変更するすべて の 処 理 に 外 部 キ ー 制 約 を 適 用 す る 必 要 が あ り ま す 。 た と え ば 、 PhoneNumbers テ ー ブ ル に CONTACT=100 のレコードがある場合、Contacts テーブルから ID=100 のレコードを削除すると、 PhoneNumbers テーブルに孤立したエントリができてしまいます。同様に、両方のテーブルでの挿入およ び更新によって孤立したエントリ、または存在しないエントリへのリンクができる場合、それらの処理を 許可してはいけません。

cd スキーマ 1 Contacts 列 *PK ID: NAME: SURNAME: PK + ID()

PhoneNumbers

+ID 1

列 *PK ID: +CONTACT * CONTACT(連絡先): NUMBER(番号): * PK + ID()

図 1 連絡先データベース スキーマ例

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 28 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

SQLite には外部キー制約を自動的に適用する機能はありませんが、いくつかの方法で参照整合性を維持 することができます。それらの方法について次で説明します。

8.1.1

トランザクションでのプログラム チェック

参照整合性を維持する第一の方法は、トランザクションでプログラムをチェックすることです。以下に手 順を示します。 ·

トランザクションを開きます。

·

実行する処理が、参照整合性に違反しないことを確認します。たとえば、複数の SELECT 文を実 行し、その結果から違反がないことを確認します。 o

参照整合性に違反する場合は、正しい手順で終了します。

·

INSERT/UPDATE/DELETE 文を実行します。

·

コミットします。

このアプローチの大きな欠点は、プログラマが大量のパーミュテーション検定(並べ替え検定)を実装す る必要が生じる場合があることです。おそらくは、データベースを変更するたびに 1 回必要です。さらに、 参照整合性を維持するコードが複数のトランザクションにまたがり、保守が困難になる可能性があります。

8.1.2

トリガの使用

参照整合性を維持するもう 1 つの方法では、SQLite のトリガ機能を使用します。トリガを使用すると、 特定のデータベース イベントが発生した場合に、任意の SQL クエリを自動的に実行できます。 Contacts の例では、データベースの作成時に作成される少数のトリガによって、参照整合性を維持できま す。参照整合性違反があった場合にデータベースで生成されるエラーを正しく処理するコードを除き、ア プリケーションに参照整合性を維持するためのコードを含める必要はありません。 最初のトリガは、削除された連絡先に対応するすべての電話番号を削除します。 CREATE TRIGGER TRIGGER_CONTACT_DELETE BEFORE DELETE ON CONTACTS BEGIN DELETE FROM PHONENUMBERS WHERE PHONENUMBERS.CONTACT=OLD.ID; END; 2 番 目 の ト リ ガ は 、 人 物 に 対 し て 有 効 な Contacts テ ー ブ ル レ コ ー ド が 存 在 し な い 場 合 に 、 PhoneNumbers テーブルへのレコードの挿入を禁止します。 CREATE TRIGGER TRIGGER_INSERT_NUMBER BEFORE INSERT ON PHONENUMBERS BEGIN SELECT CASE WHEN ( SELECT ID FROM CONTACTS WHERE ID=NEW.CONTACT) IS NULL THEN RAISE ( ABORT, "Foreign key violation" ) END; END; 3 番目のトリガは、電話番号の更新によって孤立したエントリができないようにします。 CREATE TRIGGER TRIGGER_UPDATE_NUMBER_ID BEFORE UPDATE OF CONTACT ON PHONENUMBERS

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 29 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

BEGIN SELECT CASE WHEN ( SELECT ID FROM CONTACTS WHERE ID=NEW.CONTACT) IS NULL THEN RAISE ( ABORT, "Foreign key violation" ) END; END; 通常、Contacts テーブルの ID フィールドは変更できません。アプリケーションがこのフィールドを変更 可能にする必要がある場合は、次のトリガを使用して ID の変更を処理できます。 CREATE TRIGGER TRIGGER_CONTACT_UPDATE BEFORE UPDATE OF ID ON CONTACTS BEGIN UPDATE PHONENUMBERS SET ID=NEW.ID WHERE ID=OLD.ID; END; SQLite トリガは、トランザクションが確定したときではなく、文が発行されるとただちに実行されます。 つまり、実行が失敗するのは文が発行された直後であり、その後の COMMIT が発行されたときではあり ません。アプリケーションは、エラーを適宜処理する必要があります。

8.1.3

パフォーマンスの考慮事項

参照整合性を維持するにはコストがかかります。ただし、参照整合性をできる限り適切に維持できる各種 の方法があります。 8.1.3.1

索引の使用

単一テーブルの処理の場合と同様、索引の使用はデータベースのパフォーマンスに大きく影響します。単 一テーブルと同じく、検索キーとして最も頻繁に使用する列には、索引を付ける必要があります。さらに、 外部キーとして使用する列にも索引を付けます。 8.1.3.2

外部キー列のタイプ

最適のパフォーマンスを実現するには、外部キー列を INTEGER 型にします。整数型以外の外部キーを使 用することはできますが、トリガのパフォーマンスが低下する場合があります。 8.1.3.3

参照整合性

すでに述べたように、参照性合成を維持するにはコストがかかります。上記の例から、Contacts.ID また は PhoneNumbers.CONTACT のいずれかを変更する各書き込み処理では、複数のクエリが実行されるこ とがわかります。これによってパフォーマンスは低下します。評価を必要とする多数の処理(挿入、更新 など)を実行すると、パフォーマンスの低下はより明確になります。しかし、参照整合性は、マルチテー ブル スキーマにおいて絶対不可欠な要素です。なぜなら、参照整合性が失われると多数の問題が発生する からです。たとえば、レコードが削除されずにデータベース サイズが増大し続ける、レコードの「リー ク」などがあります。 上記のトリガが不要なオーバヘッドになる場合もあります。たとえば、以前テーブルにあったすべてのレ コードを 1 つずつ挿入して復元処理(バックアップや復元など)が実行される場合、トリガの存在は望 ましくありません。より適切にバックアップまたは復元を実施するには、個々のレコードではなくデータ ファイルをバックアップまたは復元します。 最後に、先述の 2 つの方法において、参照整合性を維持するためのコストは同じ規模です。ただし、プロ グラムによる制御よりも、トリガを使用する方が明らかに有効であり、推奨される方法です。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 30 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

8.1.4

EXTERNAL - SYMBIAN

ISSUED V1.1

結論

ここまで、参照整合性を維持する複雑さをアプリケーションから排除し、データベースに格納する方法に ついて説明してきました。アプリケーション コードによってエラー状態を正しくチェックする必要は依然 としてありますが、そのプログラムは 8.1.1 で述べた方法を使用するプログラムよりもはるかに単純です。 参照整合性の維持は、パフォーマンス面ではコストがかかりますが、信頼性の高いアプリケーションには 不可欠です。参照整合性が保証されていなければ、アプリケーションは、アプリケーション固有のデータ の不整合を大量に処理しなければなりません。そのために、アプリケーション コードと、アプリケーショ ン間におけるコードの複製は非常に複雑になります。 Symbian の DBMS コンポーネントは、トリガをサポートしていません。DBMS では、プログラムによっ て参照整合性を維持する必要があります。そのため、通常はコードがより複雑でエラーが発生しやすくな ります。

8.2

多対多関係の非正規化

先に示した Contacts の例の 1 対多の関係は、リレーショナル データベースではきわめて一般的なケース です。しかし、多対多の関係を表現する必要が生じる場合もしばしばあります。 以下は、図書館の書籍と著者を表すスキーマの例です。書籍には複数の著者が存在し、著者には複数の著 作が存在する場合があります。 cd スキーマ 1 Books 列n *PK ID: NAME(名前): YEAR(年): AUTHOR(著者)1: AUTHOR(著者)2: * AUTHOR(著者)3: PK + ID()

Authors 列n *PK ID: NAME(名前): BOOK(書籍)1: BOOK(書籍)2: * BOOK(書籍)3: BOOK(書籍)4: PK + ID()

図 2 問題のある書籍 - 著者スキーマの例 上のスキーマでは、著者に定数まで著作を割り当てることができます。同様に、書籍には最大 3 人まで著 者を割り当てできます。これでは明らかに処理が制限されます。著作がきわめて少ない著者もいれば、数 百冊におよぶ著者もいます。このソリューションは、ときに無駄が多く、ときに不適切です。また、多数 の著者によって書かれた出版物も数多く存在します。最後に、このようなスキーマで参照整合性を維持す るには、多数のトリガか、あるいは非常に複雑なアプリケーション コードが必要です。 こうした多対多のスキーマには、非正規化ソリューションを使用するのが一般的です。非正規化では、マ ッピングを維持するために使用する別のテーブルを導入することで、このスキーマの制限を排除します。 非正規化によって、多対多の関係を簡素化し、管理しやすい多対 1 の関係を 2 つ作成できます。非正規化 の実行後は、書籍テーブルに著者への参照はなく、その逆も同様であるため、3 つ目のテーブルを使用し て、任意の数の著者と任意の数の書籍を対応付けることができます。

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 31 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

cd データ モデル 2 Books 列n *PK ID: NAME(名前): YEAR(年): PK + ID()

Reference

+ID

列n *PK ID: AUTHOR(著者): +BOOK BOOK(書籍):

1

*

FK + FK_AUTHOR() + FK_BOOK() PK + ID()

Authors

+AUTHOR

+ID

列n *PK ID: NAME(名前):

1 PK + ID()

*

図 3 非正規化後の書籍と著者のスキーマ 図 3 の Reference テーブルには、AUTHOR と BOOK の 2 つの外部キーがあり、Authors テーブルと Books テーブルをそれぞれ示しています。プライマリ キー(ID 列)は、3 つのテーブルすべてで維持さ れます。Reference テーブルにレコードを追加すると、書籍が著者にマッピングされます。AUTHOR 列 と BOOK 列は、マッピングするレコードの Authors および Books テーブルのプライマリ キー(ID)の値 に設定されます

Books

Reference

Authors

ID

NAME

YEAR

ID

BOOK

AUTHOR

ID

NAME

1

The Difference Engine

1992

1

1

1

1

William Gibson

2

Consider Phlebas

1987

2

1

2

2

Bruce Sterling

3

Excession

1997

3

2

3

3

Ian M Banks

4

3

3

上の表のデータ例には、Ian M Banks 著の「Consider Phlebas」および「Excession」、William Gibson お よび Bruce Sterling 共著の「The Difference Engine」という 3 冊の書籍がリストされています。 図 3 のスキーマは、参照整合性の維持を簡素化できるだけでなく、データベース処理に必要なアプリケー ション コードの複雑さにおいても利点があることを示しています。たとえば、次のクエリを使用して、著 者「Ian M Banks」の書籍のタイトルを検索できます。 SELECT Authors.NAME, Books.NAME, Books.YEAR FROM Books, Authors, Reference WHERE Authors.ID = Reference.AUTHOR AND Books.ID = Reference.Book AND Authors.NAME=”Ian M Banks” Result: Authors.NAME

Books.NAME

BOOKS.year

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

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

----------

Ian M Banks

Consider Phlebas

1987

Ian M Banks

Excession

1997

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 32 OF 33

HOW TO OPTIMISE SYMBIAN SQL SERVER SGL.GT0334.067

EXTERNAL - SYMBIAN

ISSUED V1.1

9 その他の情報 参考資料

9.1 番号

参考文書

バージョン 説明

[R1] www.sqlite.org

-

SQLite Web サイト

[R2] Database Performance on 1.0 Symbian OS v9.3(Symbian OS v9.3 におけるデータベース パフォ ーマンス) [R3] SQLite architecture overview (SQLite アーキテクチャの概要) [R4] B-Tree definition(B-Tree の定義) -

Symbian の SQL Server および DBMS デ ータベース コンポーネントの特性に関する レポート

[R5] B+Tree definition(B+Tree の定義) -

http://en.wikipedia.org/wiki/B+_tree

[R6] Database normalisation definition (データベース非正規化の定義) [R7] Symbian OS Developer Library SQL Server design documentation (Symbian OS 開発者ライブラリ SQL Server 設計マニュアル)

http://en.wikipedia.org/wiki/Database_norm alization

9.2

-

http://www.sqlite.org/arch.html http://en.wikipedia.org/wiki/B-tree

SQL データベース コンポーネントの設計

用語集

用語

説明

ACID

Atomicity(原子性)、Consistency(整合性)、Isolation(隔離性)、 Durability(耐久性)

BLOB

Binary Large Object

I/O

入力 / 出力

UTF

Unicode Transformation Format

RAM

Random Access Memory

COPYRIGHT © SYMBIAN SOFTWARE LTD. 2008. ALL RIGHTS RESERVED.

EXTERNAL - SYMBIAN

PAGE 33 OF 33

Related Documents

How To Optimize Phoenix
August 2019 37
How To Learn Japanese
October 2019 35
Sql Server
November 2019 28
Sql Server
November 2019 18
Sql Server
May 2020 11

More Documents from ""