ハイパフォーマンスなSQLを作成するためのヒント
以下は、ハイパフォーマンスなストアドプロシージャを作成するときに広く当てはまるヒントです。あいにく、一部の例外を除き、大半のヒントは、機械的に適用することはできません。クエリする対象データの性質とスキーマをまず考慮する必要があります。
1.カーソル（およびその他のループ構造）の使用はできるだけ避ける
カーソルは効率が悪く、データベースエンジンによるループの実装は、一般にはパフォーマンスの面で最適な実装とは言えません。
通常、データベース側では、カーソルを使用するコードの代わりとして、ベクトルテーブルを使用するSQLの集計ステートメント（SELECT、INSERT、UPDATE）を使用できます。どのデータベースエンジンも、集計ステートメントに関しては非常に最適化されています。従って、ループを使用せざるを得ない場合でも、いくつかの集計ステートメントを実行するループを少ない回数だけ反復する方が、カーソルを作成して単純なステートメントを何回も反復するよりも確実に優れています。
初期段階のパフォーマンステスト（特に少量のデータで実行したテスト）で、複雑な集計ステートメントを使用するよりカーソルを使用する方が処理効率が高いという結果が得られた場合でも、データ数が絶対に増加しないという確証がない限りは、細かな部分ごとに処理を分割するなどの方法による最適化を検討するのが得策です。カーソルを使用する方法は、スケーラビリティに欠けています。
2.データを上手にフィルタする
カーソルに代わる手法の1つが、フォールスルー（fall through）の手法です。一連のデータ記憶域（例えば物理テーブル、一時テーブル、テーブル変数など）に対して、複数のステップから成るフィルタリングと集計を行うというものです。通常は、集計ステートメントに集計フィルタを組み込み、処理に必要のない大半のデータを除外して、処理するデータ量を絞り込むという方法がベストです。そのうえで、結合とフィルタの処理を行うようにすれば、結合処理の対象データの数を抑えることができます。
3.通常、単一の条件を指定したステートメントを複数実行する方が、複数のOR条件を指定したステートメントを1つ実行するより効率が良い
これは、複数のユーザーが同時にアクセスする可能性のある永続的なデータベーステーブルに対してUPDATEステートメントとDELETEステートメントを実行するときの話です。このヒントは、スケーラビリティの観点からは特に重要ですが、パフォーマンスの観点では、さしたる違いはありません。このヒントが成り立つ最大の理由は、データベースレコードのロックと、その背後で生じるロックの昇格です。
4.一時テーブルとテーブル変数を上手に使い分ける
テーブル変数はメモリ内の構造であり、一時テーブルより2～100倍高速に動作する可能性があります。ただし、テーブル変数に含まれるデータの量が増えるほど、アクセスが遅くなるという点を考慮に入れる必要があります。どこかの時点で、テーブル変数が、使用可能なメモリの量を超え、パフォーマンスに支障が生じます。従って、テーブル変数は、データの中身が予想外に大きくならないことが確実な場合に限り使用するようにしましょう。
分岐点となるサイズはおよそ数千レコードです。データの量がそれより多い場合は、クラスタ化インデックスを使用した一時テーブルをお勧めします。
面白いことに、単一のクラスタ化インデックスを使用する一時テーブルの方が、複数の単純なインデックスを使用するよりも高速なことが多いようです。一方、物理テーブルの場合は、複数の単純なインデックスを使用する方が、単一のクラスタ化インデックスを使用するよりも通常は高速です。
5.確固たるルールと前提とを区別する
これは、どちらかと言うとビジネスデザインに関するヒントであり、パフォーマンスとスケーラビリティの設計全般よりも、コード設計に当てはまる話です。しかし現実には、設計が適切でない場合に真っ先に支障が出るのは、パフォーマンスとスケーラビリティなのです。ルールが前提として実装されると、不要な計算を実行することになりがちで、パフォーマンスに影響が生じます。
一方、前提がルールとして実装されると、エラーやアルゴリズムの誤りが生じがちです。その場合、通常は、早急な再設計が必要になります。すると大抵は、ビジネス上の制約がある中で再設計を行うことになり、最終的なアルゴリズムは効率の悪いものになってしまいます。なぜなら、設計に関する判断の誤りを修正するときには、十分なリソースがないままに急いで行うケースが多いからです。時には、顧客の業務が重大な段階にあるときに問題が表面化し、その間も運用を継続しなくてはならないという状況で、顧客からのプレッシャーを浴びながら修正を行うこともあります。
6.結合順序に注意を払う
適切な結合順序を使用すると、最適な量のレコードで結合を実行するヒントをデータベースエンジンに生成させることができます。大半のデータベースエンジンは強制的なヒントに対応していますが、通常は、強制的なヒントの使用は避け、ジョブの最適な実行方法をデータベースエンジン自身に判断させるのが良いでしょう。
7.複雑なビューの結合には注意する
これは、複雑なSELECTステートメントでビューを他のビューやデータベーステーブルに結合するときの話です。
膨大な量のデータが格納されているデータベースでは、SQL Serverエンジンは、得られたステートメントの実行プランを再計算することがよくあります。この結果、効率の悪い実行プランとなることが多々あり、パフォーマンスに支障が出る場合があります。最もやっかいなのは、SQL Serverエンジンの動作がその点に関して一貫しておらず、データベースのサイズ、インデックス、外部キー、およびその他のデータベース構造や制約によって大きく変わるという点です。
一貫性のある回避策としては、適切なプレフィルタを使用して、基になるビューから一時テーブルにデータを事前に選択したうえで、その一時テーブルを、基になるビューの代わりとして使用するという方法があります。
8.一時テーブルへのインデックス作成は上手に行う
ヒント4でも触れたように、一時テーブルでのパフォーマンスという面では、通常はクラスタ化インデックスが最適です。しかし、インデックスの作成を、一時テーブルへのデータ挿入の前に行うのと後に行うのとでは違いがあります。インデックスの作成を挿入の前に行うと、挿入が複雑になります。データベースエンジンが選択の順序を調整する必要があるからです。
ヒント7で述べたような複雑な選択では、追加的な順序調整によってステートメント全体が複雑になりすぎ、パフォーマンスが大幅に低下するおそれがあります。一方、インデックスの作成を挿入の後で行うと、ストアドプロシージャが呼び出されるたびに、ストアドプロシージャの実行プランの再計算をデータベースエンジンに強制的に行わせることになります。従って、どちらを選択するかは常にトレードオフであり、2つの可能性の相対的なコストに基づいて判断する必要があります。
9.一般的に、実行プランの再計算は避ける
再計算の原因としてよくあるものの1つが、ストアドプロシージャの中に、パラメータで渡された値に応じて決まる複数のパスがあるというケースです。しかし、再計算を回避できるかどうかは、ストアドプロシージャの複雑さや、その他の条件（ヒント8で述べた状況など）に応じて変わってきます。エンジンが実行プランの再計算を行うときには、パフォーマンスに必ず影響が生じます。一方、呼び出す側の実行プランが再計算されても、呼び出される側のプロシージャ（またはビューや関数）の実行プランの再計算は必ずしも実行されません。
従って、回避策としては、ストアドプロシージャを、渡されたパラメータに対応する複数のプロシージャに分割し、条件に応じて親から子を呼び出すという方法が考えられます。ただし、この分割はきわめて注意深く行う必要があります。保守が非常に大変になるおそれがあるからです。それでも、十分なデータベースパフォーマンスとスケーラビリティを実現するには、これが唯一の方法に思えることもあります。
もう1つのヒント
最後にもう1つ。これはパフォーマンスやスケーラビリティに関するヒントではありませんが、ストアドプロシージャのスクリプトは、ぜひとも読みやすいスタイルで記述してもらいたいと思います。句の順序や書式のルールなどの表記規則を、あらかじめ社内で統一しておくのがベストです。エラーの防止に役立つのに加え、ステートメントの論理構造が明確になり、フィルタや結合の誤りを把握するのに役立つ場合も多々あります。
以上のヒントは、完全無欠のものでは決してありませんが、パフォーマンスとスケーラビリティに関する特に重要な要因はおおむねカバーできているものと思います。とはいえ、ポイントを理解するうえでは、具体例に勝るものはありません。ここからは、ヒント1～6の手法の例として、「数独」というパズルの解法プログラムを紹介します。
