クエリ パフォーマンスのチューニング (SQL Server Compact)

  • [アーティクル]

SQL Server Compact 4.0 アプリケーションでは、使用するクエリを最適化することによってパフォーマンスを向上させることができます。以下のセクションでは、クエリ パフォーマンスの最適化に使用できる手法の概要を説明します。

インデックスの性能向上

有用なインデックスを作成することは、より最適なクエリ パフォーマンスを得るための最も重要な方法の 1 つです。有用なインデックスを使用すると、データ検索時のディスク I/O 操作とシステム リソースの使用率が少なくて済みます。

有用なインデックスを作成するには、データの用途、クエリの種類とクエリ実行頻度、およびクエリ プロセッサでインデックスを使用してデータをすばやく見つける方法を理解しておく必要があります。

作成するインデックスを選択する際、重要なクエリを調査してください。重要なクエリのパフォーマンスはユーザーの操作性に大きく影響します。インデックスを作成すると、このようなクエリで特に役に立ちます。インデックスを追加した後、パフォーマンスが向上したかどうかを確認するために、クエリを再実行します。パフォーマンスが向上しなかった場合、インデックスを削除してください。

パフォーマンスの最適化技法の多くと同様、検討を要する事項があります。たとえばインデックスが多くなれば、SELECT クエリがより高速に実行される場合があります。ただし、DML (INSERT、UPDATE、および DELETE) 操作では、各操作でより多くのインデックスを保持する必要があるので、その速度は非常に遅くなります。そのため、使用するクエリの多くが SELECT ステートメントの場合、インデックスを多くすると効果があります。アプリケーションで多くの DML 操作を実行する場合は、作成するインデックスの数を少なめにする必要があります。

SQL Server Compact ではプラン表示がサポートされており、クエリへのアクセスとクエリの最適化に使用できます。SQL Server Compact では、SQL Server Compact が演算子のサブセットを使用する場合を除いて、SQL Server 2008 R2 と同じプラン表示スキーマが使用されます。詳細については、https://schemas.microsoft.com/sqlserver/2004/07/showplan/ で Microsoft のプラン表示スキーマ (Showplan Schema) を参照してください。

次の数セクションでは、有用なインデックスの作成に関する追加情報について記載しています。

選択度の高いインデックスの作成

重要なクエリの WHERE 句で使用される列にインデックスを作成すると、パフォーマンスが向上する可能性が高くなります。ただし、これはインデックスの選択度に依存します。選択度とは、特定される行の、合計行数に対する比率です。比率が低い場合、インデックスの選択度は高くなります。これにより、多くの行を除去して、結果セットのサイズを大きく減少させることができます。このような理由から、選択度の高いインデックスが作成すべき有用なインデックスとなります。逆に、選択度が低いインデックスは有用ではありません。

一意インデックスは、選択度が非常に高くなります。1 行にのみ一致するので、1 行のみを返すことを目的としたクエリに最も役立ちます。たとえば、一意な ID 列にあるインデックスを使用すると、特定の行をすばやく見つけることができます。

インデックスの選択度は、SQL Server Compact テーブルに対して sp_show_statistics ストアド プロシージャを実行することによって評価できます。たとえば、"Customer ID" および "Ship Via" という 2 列の選択度を評価する場合、次のストアド プロシージャを実行できます。

sp_show_statistics_steps 'orders', 'customer id';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

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

ALFKI               0            7                   0

ANATR               0            4                   0

ANTON               0           13                   0

AROUT               0           14                   0

BERGS               0           23                   0

BLAUS               0            8                   0

BLONP               0           14                   0

BOLID               0            7                   0

BONAP               0           19                   0

BOTTM               0           20                   0

BSBEV               0           12                   0

CACTU               0            6                   0

CENTC               0            3                   0

CHOPS               0           12                   0

COMMI               0            5                   0

CONSH               0            4                   0

DRACD               0            9                   0

DUMON               0            8                   0

EASTC               0           13                   0

ERNSH               0           33                   0

(90 rows affected)

および

sp_show_statistics_steps 'orders', 'reference3';

RANGE_HI_KEY RANGE_ROWS EQ_ROWS DISTINCT_RANGE_ROWS

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

1               0            320                   0

2               0            425                   0

3               0            333                   0

(3 rows affected)

この結果は、"Customer ID" 列での重複の度合いが非常に低いことを示します。つまり、"Customer ID" 列のインデックスの方が、"Ship Via" 列のインデックスより選択度が高いということになります。

このようなストアド プロシージャの使用については、「sp_show_statistics (SQL Server Compact)」、「sp_show_statistics_steps (SQL Server Compact)」、および「sp_show_statistics_columns (SQL Server Compact)」を参照してください。

複数列のインデックスの作成

複数列のインデックスは、1 列のインデックスが拡張されたものです。複数列のインデックスは、キー列のプレフィックス セットと一致するフィルタ式を評価する際に役立ちます。たとえば、複合インデックス CREATE INDEX Idx_Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) では、次のクエリを評価できます。

  • ...WHERE "Last Name" = 'Doe'

  • ...WHERE "Last Name" = 'Doe' AND "First Name" = 'John'

  • ...WHERE "First Name" = 'John' AND "Last Name" = 'Doe'

ただし、次のクエリには役立ちません。

  • ...WHERE "First Name" = 'John'

複数列のインデックスを作成する場合、選択度が高い列ほどキーの左端に置く必要があります。これにより、いくつかの式と一致するときにインデックスの選択度がより高くなります。

小さいテーブルのインデックス作成の回避

小さいテーブルでは、コンテンツが 1 つまたは少数のデータ ページに含まれます。インデックス作成は主にテーブル スキャンの実行時に効果的なので、非常に小さいテーブルのインデックスは作成しないようにしてください。これにより、インデックス ページを読み込んで処理するコストが減少します。非常に小さいテーブルにはインデックスを作成しないことで、オプティマイザがインデックスを選択する処理を取り除きます。

SQL Server Compact では、4 KB のページにデータを格納します。ページ数は、次の数式を使用して概算することができますが、ストレージ エンジンのオーバーヘッドにより実際の数は概算値より少し大きくなる場合があります。

<列の合計サイズ (バイト単位)> x <行数>

<ページ数> = -----------------------------------------------------------------

4096

たとえば、テーブルに次のスキーマが含まれているとします。

列名

型 (サイズ)

Order ID

INTEGER (4 バイト)

Product ID

INTEGER (4 バイト)

Unit Price

MONEY (8 バイト)

Quantity

SMALLINT (2 バイト)

Discount

REAL (4 バイト)

テーブルには 2,820 行あります。数式によると、このテーブルではデータを格納するために約 16 ページが使用されます。

<ページ数> = ((4 + 4 + 8 + 2 + 4) x 2,820) / 4,096 = 15.15 ページ

インデックスを作成する対象の選択

主キーには常にインデックスを作成することをお勧めします。外部キーにインデックスを作成することも多くの場合、役に立ちます。これは、主キーと外部キーがテーブルを結合する際に使用されることがよくあるためです。これらのキーのインデックスは、オプティマイザによってより効率的なインデックス結合アルゴリズムと見なされます。クエリで他の列を使用してテーブルを結合する場合、同じ理由でこれらの列にインデックスを作成することは多くの場合、役に立ちます。

主キーと外部キーの制約が作成されると、SQL Server Compact により自動的に制約に対するインデックスが作成され、作成されたインデックスがクエリの最適化時に使用されます。主キーと外部キーのサイズは小さいまま維持してください。結合はこのようにして高速に実行されます。

フィルタ句でのインデックスの使用

インデックスを使用して、特定の種類のフィルタ句の評価速度を上げることができます。すべてのフィルタ句によりクエリの最終結果セットが減少しますが、一部のフィルタではスキャンする必要のあるデータ量を減少させることができます。

検索引数 (SARG) は、検索での完全一致、値の範囲、または AND で結合された複数のアイテムの組み合わせを指定して検索を制限します。SARG は次のいずれかの順序で記述します。

  • 列 演算子 <定数または変数>
  • <定数または変数> 演算子 列

SARG 演算子には、=、>、<、>=、<=、IN、BETWEEN があります。また、LIKE 演算子も使用されます (LIKE 'John%' のようにプレフィックスを一致させる場合)。SARG には、AND で結合した複数の条件を含めることができます。SARG は次のような特定の値と一致するクエリにすることができます。

  • "Customer ID" = 'ANTON'

  • 'Doe' = "Last Name"

SARG は次のような値の範囲と一致するクエリにすることもできます。

  • "Order Date" > '1/1/2002'

  • "Customer ID" > 'ABCDE' AND "Customer ID" < 'EDCBA'

  • "Customer ID" IN ('ANTON', 'AROUT')

SARG 演算子を使用しない式では、パフォーマンスが向上しません。これは、SQL Server Compact のクエリ プロセッサによって各行を評価してフィルタ句と一致するかどうかを特定する必要があるためです。したがって、インデックスは SARG 演算子を使用しない式では有用ではありません。SARG 以外の演算子には、NOT、<>、NOT EXISTS、NOT IN、NOT LIKE、および組み込み関数があります。

クエリ オプティマイザの使用

ベース テーブルのアクセス方法を特定する際、SQL Server Compact オプティマイザにより、SARG 句に対するインデックスが存在するかどうかが判断されます。インデックスが存在する場合、オプティマイザは返される行数を計算することでインデックスを評価します。その後、インデックスで特定される行を検索するコストを算出します。テーブル スキャンよりもコストが低い場合、インデックス付きアクセスが選択されます。インデックスは、テーブルの先頭列または列のプレフィックス セットが SARG で使用され、SARG で下限値、上限値、または両方を設定して検索を制限する場合に、役立つ可能性が高くなります。

応答時間と合計時間について

応答時間は、クエリが最初のレコードを返すまでの時間です。合計時間は、クエリがすべてのレコードを返すまでの時間です。対話型アプリケーションの場合、クエリが処理されたことをユーザーが視覚的に確認するまでの時間が応答時間にあたり、非常に重要です。バッチ アプリケーションの場合、合計時間はスループット全体を示します。アプリケーションとクエリについてパフォーマンスの条件を判断し、それに従ってアプリケーションとクエリを設計する必要があります。

たとえば、クエリが 100 個のレコードを返し、最初の 5 個のレコードを含むリストを作成するために使用されるとします。この場合、100 個のレコードがすべて返されるまでにかかる時間を気にする必要はありません。代わりに、クエリが最初の数個のレコードをすばやく返して、リストを作成できるようにすることが望まれます。

多くのクエリ演算は、中間結果を保存せずに実行できます。これらの演算は、パイプライン処理と呼ばれます。パイプライン処理される演算の例は、投影、選択、結合です。これらの演算を使用して実装されたクエリは結果をすぐに返すことができます。SORT や GROUP-BY などの他の演算は、すべての入力を使用してから、親演算に結果を返す必要があります。これらの演算では、実体化が必要になります。このような演算子を使用して実装されたクエリは、実体化を行うので、最初に遅延が発生するのが一般的です。この最初の遅延後は、通常、レコードが非常にすばやく返されます。

応答時間に条件が伴うクエリでは、実体化が発生しないようにする必要があります。たとえば、インデックスを使用して ORDER-BY を実装すると、並べ替えを使用した場合よりも、より優れた応答時間が得られます。次のセクションでは、これについて詳しく説明します。

ORDER-BY、GROUP-BY、DISTINCT 句の列でインデックスを作成して、より優れた応答時間を得る

ORDER-BY、GROUP-BY、および DISTINCT 演算が並べ替えの全種類です。SQL Server Compact のクエリ プロセッサは、2 種類の方法で並べ替えを実装しています。レコードがインデックスで既に並べ替えられている場合、プロセッサはインデックスのみを使用する必要があります。それ以外の場合、プロセッサは最初に一時的な作業テーブルを使用してレコードを並べ替える必要があります。このような準備段階の並べ替えにより、CPU が低電力でメモリに制限があるデバイスでは最初に発生する遅延が長時間に及ぶことがあります。応答時間が重要な場合は、このような準備段階の並べ替えを行わないでください。

複数列のインデックスでは、ORDER-BY または GROUP-BY 句で特定のインデックスが考慮されるように、ORDER-BY または GROUP-BY 句の列がインデックス列のプレフィックス セットと正確な順序で一致する必要があります。たとえば、インデックス CREATE INDEX Emp_Name ON Employees ("Last Name" ASC, "First Name" ASC) では、次のクエリを最適化できます。

  • ...ORDER BY / GROUP BY "Last Name" ...

  • ...ORDER BY / GROUP BY "Last Name", "First Name" ...

次のクエリは最適化できません。

  • ...ORDER BY / GROUP BY "First Name" ...

  • ...ORDER BY / GROUP BY "First Name", "Last Name" ...

DISTINCT 演算で複数列のインデックスが考慮されるようにする場合、投影リストがすべてのインデックス列と一致する必要があります。ただし、インデックス列が正確な順序で一致する必要はありません。このようなインデックスは、次のクエリを最適化できます。

  • ...DISTINCT "Last Name", "First Name" ...

  • ...DISTINCT "First Name", "Last Name" ...

次のクエリは最適化できません。

  • ...DISTINCT "First Name" ...

  • ...DISTINCT "Last Name" ...

注意

クエリが常に一意な行自体を返す場合、DISTINCT キーワードを指定しないでください。このキーワードを指定すると、オーバーヘッドが増加するだけです。

サブクエリを書き換えて JOIN を使用

サブクエリを書き換えて JOIN を使用すると、より優れたパフォーマンスを得られることがあります。JOIN を作成する利点は、クエリで定義された順序とは異なる順序でテーブルを評価できることです。サブクエリを使用する利点は、サブクエリの式を評価する際にサブクエリからすべての行をスキャンする必要がほとんどないことです。たとえば、EXISTS サブクエリは最初に特定された行を確認した時点で、TRUE を返すことができます。

注意

SQL Server Compact のクエリ プロセッサは常に IN サブクエリを書き換えて JOIN を使用します。IN サブクエリの句を含むクエリを使用してこのアプローチを試行する必要はありません。

たとえば、25 パーセント以上割引されている商品を少なくとも 1 つ含んだ注文をすべて特定するには、次のような EXISTS サブクエリを使用できます。

SELECT "Order ID" FROM Orders O

WHERE EXISTS (SELECT "Order ID"

FROM "Order Details" OD

WHERE O."Order ID" = OD."Order ID"

AND Discount >= 0.25)

JOIN を使用してこれを書き換えることもできます。

SELECT DISTINCT O."Order ID" FROM Orders O INNER JOIN "Order Details"

OD ON O."Order ID" = OD."Order ID" WHERE Discount >= 0.25

Outer JOIN を使用した制限

OUTER JOIN は INNER JOIN とは別に扱われます。オプティマイザは、OUTER JOIN 句のテーブルの結合順序を再調整しようとしませんが、INNER JOIN 句のテーブルに対しては再調整を行います。外部テーブル (LEFT OUTER JOIN 句の左側のテーブルと RIGHT OUTER JOIN 句の右側のテーブル) が最初にアクセスされ、その後内部テーブルがアクセスされます。このように結合順序が固定されていることから、実行プランの最適性は低下します。

INNER JOIN を含むクエリの詳細については、Microsoft サポート技術情報を参照してください。

パラメータ化クエリの使用

アプリケーションで一部の制約だけが異なる一連のクエリを実行する場合、パラメータ化クエリを使用してパフォーマンスを向上できます。たとえば、個別の顧客による注文を返すには、次のクエリを実行できます。

SELECT "Customer ID" FROM Orders WHERE "Order ID" = ?

パラメータ化クエリを使用すると、クエリが一度だけコンパイルされ、コンパイル済みのプランが複数回実行されることで、パフォーマンスが向上します。プログラム上では、キャッシュされたクエリ プランを含むコマンド オブジェクトを指定しておく必要があります。前のコマンド オブジェクトを破棄して新しいコマンド オブジェクトを作成すると、キャッシュされたプランが破棄されます。これにより、クエリの再コンパイルが必要になります。いくつかのパラメータ化クエリを交互に実行する必要がある場合、それぞれがパラメータ化クエリの実行プランをキャッシュするように、いくつかのコマンド オブジェクトを作成できます。このようにすると、すべてのクエリが再コンパイルされるのを効率的に回避できます。

必要時のみのクエリ

SQL Server Compact のクエリ プロセッサは、リレーショナル データベースに格納されているデータに対してクエリを実行するための優れたツールです。ただし、どのようなクエリ プロセッサにも伴う本質的なコストが存在します。プランの実行という実際の作業を開始する前に、実行プランをコンパイル、最適化、および生成する必要があります。これは、すぐに完了する単純なクエリに特に当てはまります。そのため、クエリを独自に実装すると、パフォーマンスが大幅に向上する場合があります。重要なコンポーネントでミリ秒単位の配慮が必要な場合、単純なクエリを独自に実装するという代替手段を検討することをお勧めします。大きく複雑なクエリの場合は、その作業をクエリ プロセッサに任せるのが最善です。

たとえば、注文 ID で並べ替えられた一連の注文に対する顧客 ID を照合するとします。これを達成するには 2 つの方法があります。まず、それぞれの照合に対して次の手順を実行します。

  1. Orders ベース テーブルを開きます

  2. 特定の "Order ID" を使用して行を検索します

  3. "Customer ID" を取得します

または、それぞれの照合に対して次のクエリを発行します。

SELECT "Customer ID" FROM Orders WHERE "Order ID" = <the specific order id>

クエリ ベースのソリューションは、手動でのソリューションよりも単純ですが低速になります。これは、SQL Server Compact のクエリ プロセッサによって、宣言の SQL ステートメントが、手動で実装可能な同じ 3 つの操作に変換されるためです。変換後、これらの 3 つの手順が順に実行されます。どの方法を使用するかは、アプリケーションで単純さとパフォーマンスのどちらがより重要であるかを考慮して決めてください。