日付範囲クエリが極端に遅くなっていませんか? SQLのパフォーマンスが低下していませんか? 皆様のお役に立ちそうな、変わったトリックをご紹介します!
データ追加時にタイムスタンプを記録するクラスがある場合、そのデータはIDKEYの値で順番に並べられます(具体的には、テーブル内のすべてのIDとTimeStampについてTimeStamp1 < TimeStamp2 が成立し、かつ ID1 < ID2 が成立する場合に限られています)。この知識を活かせば、TimeStampで範囲指定をする場合よりもクエリのパフォーマンスを向上させることができます。 次のテーブルについて考えてみましょう。
このテーブルに、過去30日間の日付を含む30,000,000行のランダムなレコードを入力すると、1日あたり1,000,000レコードが出来上がります。 特定日の情報を照会する場合は、次のように記述します。
間違いなく合理的なクエリです。 しかし、筆者のシステムでは、このクエリを処理するのに2,172,792回のグローバル参照が発生し、7.2秒かかりました。 ただし、IDとTimeStampが同じ順序になっていることが分かっている場合は、TimeStampsを使用してIDの範囲を取得することができます。 次のクエリについて考えてみましょう。
この新しいクエリは5.1秒で完了し、999,985回のグローバル参照しか発生しませんでした*!
この手法は、インデックス付きフィールドが多いテーブルと複数のWHERE句を持つクエリに対してより実用的に適用できます。 サブクエリから生成されたIDの範囲はビットマップ形式にすることができます。これにより、複数のインデックスを持つソリューションを取得するときに驚異的な速度を得ることができます。 このトリックが機能する良い例には、Ens.MessageHeaderテーブルがあります。
これはこの手法が効果的であることを示す一例です。 この手法は、同じテーブル内のWHERE句に多くの条件がある場合(また、これらには明確にインデックスが付けられています)にさらに効果を発揮します! ご自身のクエリでお試しください!
* 非常に大量のレコードを返すクエリをテストする場合、管理ポータルでこれを実行すると、ほとんどの時間がデータの表示に費やされることになります。 テストする場合は、埋め込みSQLや動的SQLを使用して結果を出力(表示)せずに確認し、グローバル参照件数の確認にはSQLシェルを使用するのが適切です。 また、その目的でSQL Statsを使用することもできます。