投稿者:Mitsuhiro Okamoto | 投稿日:2013年10月21日(月) 09:56

※本エントリーは米国Developer Forceサイトのエントリを翻訳した物です。元の記事は以下をご覧下さい。
http://blogs.developerforce.com/engineering/2013/09/collecting-selectivity-statistics-for-force-com-queries.html

 

Statistics2_inhu8y

効率性にすぐれた SOQL クエリ、レポート、リストビューを作成するための鍵は、検索条件のセレクティビティ (選択度) とインデックスです。オブジェクトで大量のレコードを扱う場合、これは特に重要になります。Force.com でのクエリの最適化について学べる格好のリソースとしては、私(Steve Bobrowski)が以前執筆したブログ記事Web セミナークエリ & 検索最適化のための早見表 (いずれも英語) があり、選択度の評価に使われるしきい値などの情報を確認できます。

 今回の記事では、これらのリソースから学べる知識を実際の開発に生かす方法を紹介します。このあと述べるシンプルな手順によって、作成した検索条件をクエリ内で実際に使ってみる前に、選択度を評価できます。

自分が作成した検索条件の選択度は?

これからの説明は、「組織内でもっとも大きなオブジェクトの 1 つである Opportunity (商談) に対する SOQL クエリ (またはレポート、リストビュー) を作成する」というシナリオを使って行います。検索条件は 1 つで、WHERE 句を使用してオブジェクトから必要な行だけを取得します。ここでの最大の課題は、次のような問いに答えることです。

 

"自分が作成した検索条件では、Force.com クエリオプティマイザが有効なインデックスを使用できる水準の選択度が実現されているか?"

 

この問いには、シンプルな SOQL クエリで答えられます。統計データをすばやく取得して、検索条件の対象になっている項目の値で選択度を評価するのです。

SOQL を使って検索条件の選択度を評価する

WHERE 句に 1 つの条件を指定した、次のような基本的な検索条件を例に考えてみましょう。

SELECT Id, Name FROM Opportunity WHERE Stagename = 'Closed Won'

検索条件の選択度に関する統計データを取得するには、アドホッククエリを実行できる Force.com ツール (開発者コンソールのクエリエディタ、Workbench など) を使用します。ここでは、次のようなクエリを実行します。 

SELECT Stagename, COUNT(id) FROM Opportunity GROUP BY ROLLUP(Stagename)

: オブジェクトのサイズが非常に大きくクエリがタイムアウトになる場合は、セールスフォース・ドットコムのカスタマーサポートに問い合わせ、担当者の案内に従って手順を進めるようにしてください。

クエリを実行すると、選択リスト項目 Stagename (商談フェーズ名) の値ごとにレコード件数が取得されます。最終行には Opportunity レコードの合計数が表示されます。

 

 

Stagename 項目の値

レコードの数

1

Prospecting

50177

2

Qualification

49755

3

Needs Analysis

49965

4

Value Proposition

50135

5

Id. Decision Makers

50012

6

Perception Analysis

49840

7

Proposal/Price Quote

49943

8

Negotiation/Review

50158

9

Closed Won

49899

10

Closed Lost

50116

11

 

500000

 

このデータにより、Stagename 項目を使った検索条件の選択度を評価できます。先ほど紹介した早見表によると、標準項目を含む単一の検索条件では、1,000,000 件目までのレコードの中での一致率が 30% を切る場合に、セレクティブである、すなわち、「Force.com クエリオプティマイザが有効なインデックスを使用できる水準の選択度が実現されている」と評価されます。上の例では、オブジェクトのレコード数は 1,000,000 より少ないため、30% という数値がそのまま適用され、しきい値は 500,000 の 30%、つまり 150,000 になります。今回の検索条件は、「Stagename が 'Closed Won' と一致する」というものでしたが、該当する値のレコード数を見ると 49,899 になっています。150,000 よりも値が小さいため、この検索条件は「セレクティブである」と言えます。

 

より複雑な検索条件の選択度を評価する

より複雑な検索条件での選択度については、John Tan が執筆した Force.com ブログの記事 (英語) にわかりやすい事例が取り上げられています。ここでは、その内容をあらためて紹介することはしません。補足として、前のセクションで実行したような GROUP BY ROLLUP クエリを使うことで、さまざまな条件の選択度を評価するための統計データを簡単に取得できるという点を説明したいと思います。

複雑な検索条件の例として、日付項目と AND 演算子を使用した次のようなクエリを考えてみましょう。 

SELECT Id, Name FROM Opportunity WHERE Stagename = 'Closed Won' AND CloseDate = THIS_WEEK

Stagename 項目に関する統計データはすでに把握済みなので、ここでは、日付項目 CloseDate (商談の完了予定日) を対象にして、各年の週別にデータを収集します。有難いことに、SOQL には、このようなデータを簡単に取得できる便利な日付関数が用意されています。 

SELECT WEEK_IN_YEAR(CloseDate), CALENDAR_YEAR(CloseDate), COUNT(id) FROM opportunity GROUP BY ROLLUP(WEEK_IN_YEAR(CloseDate),CALENDAR_YEAR(CloseDate)) ORDER BY CALENDAR_YEAR(CloseDate), WEEK_IN_YEAR(CloseDate)

上記のクエリを実行すると、Opportunity レコードの件数が、CloseDate の値にもとづいて、各年の週別に振り分けられて返されます。

 

レコードの数

1

2012

3131

2

2012

3095

3

2012

3344

4

2012

3220

5

2012

3197

6

2012

3185

.

1

2013

3151

2

2013

3395

3

2013

3374

4

2013

3220

5

2013

3593

6

2013

3286

.

 

再び先ほどの早見表を見ると、AND を使って複数の条件を指定した場合、一連の検索条件で取得されるレコード件数が次の基準を下回っていれば、Force.com クエリオプティマイザでは、検索条件全体としては"セレクティブ"という評価を下します。

  • 個々の検索条件のレコード件数 – しきい値にもとづく件数の 2 倍
  • 一連の項目の積集合のレコード件数 – しきい値にもとづく件数

今回の例では、次のように評価が行われます。 

  • 「Status = 'Closed Won'」は"セレクティブ" (「49,899」で、「150,000」を下回る)
  • 「CloseDate = THIS_WEEK」は"セレクティブ" (3,000 台で、「150,000」を下回る)
  • 以上 2 つの理由から、この検索条件は"セレクティブ"であると言える

では、もう一段ハードルを上げて、選択度が適切な狭さを持たない検索条件が 1 つ含まれるケースについても考えてみましょう。たとえば、「Status = 'Closed Won'」で 250,000 件のレコードが取得されたとします。この場合、以下のいずれかに該当していれば、検索条件全体としては"セレクティブ"と評価されます。 

  • 各検索条件で取得されるレコード件数が 300,000 より少ない (しきい値にもとづく件数を 2 倍した値を下回る)
  • 「Stagename = 'Closed Won'」と「CloseDate = THIS_WEEK」の積集合として取得されるレコード件数が 150,000 を下回る

 

この例では 1 つ目の条件が満たされているため、"セレクティブ"と評価されます。

: OR 演算子を使用する場合は、それぞれの検索条件で、しきい値にもとづく基準が満たされている必要があります。

 

削除済みレコードにも注目

先ほど触れた John Tan のブログ記事には、削除済みのレコードがクエリのパフォーマンスに大きな影響を与える可能性に関する指摘があります。では、選択度を評価する統計データを収集する際に、削除済みのレコードを含めたり除外したりするにはどうしたらよいのでしょう? 答えは簡単です。標準オブジェクトでもカスタムオブジェクトでも利用できる Boolean 型の項目「IsDeleted」を使うのです。

 

前のセクションで取り上げたクエリでは、ROLLUP 関数を使ってすべての Opportunity レコードを対象にデータを収集しました。一方、削除済みレコードについて考慮したうえでデータを収集するには、IsDeleted が true/false のいずれであるかを指定します。たとえば、Opportunity の StageName 項目に関するデータを収集する際に、削除済みレコードを対象から除外したい場合は、次のようなクエリを実行します。 

SELECT Stagename, COUNT(id) FROM opportunity WHERE IsDeleted=false GROUP BY Stagename

: Workbench を使うと、手順がさらに簡単になります。[Deleted and archived records] オプションリストで [Exclude] を選択するだけで済みます。

 

必ずインデックスがあることを確認

クエリ、レポート、リストビューなどを運用環境に実装する前に検索条件の選択度をきちんと評価できたとしても、その条件を適用する項目にインデックスが付与されていなければ、あらゆる作業が無駄に終わります。適切なインデックスが存在しない場合、Force.com ではターゲット行を取得するためにフルスキャンを実行しなければなりません。

先ほどの早見表 (Database Query & Search Optimization Cheat Sheet) では、デフォルトでインデックスが付与される標準項目のリストを確認できます。Id、Name、OwnerId、CreatedDate、SystemModstamp、RecordType のほか、主従関係や参照関係の設定されたすべての項目がサポートされています。

一方、検索条件にカスタム項目が含まれている場合は、やや厄介です。残念ながら、現在のところ、Force.com のユーザインターフェース上でカスタムインデックスの一覧を確認することはできません。セールスフォース・ドットコムのカスタマーサポートに問い合わせて、自分たちが作成した項目にカスタムインデックスを追加できるかどうかを確かめる必要があります。さらに、値が一意に定まらない数式項目など、インデックスを付与できない項目も存在します。

 

"セレクティブ"と評価できた項目にインデックスがないと考えられる場合は、カスタマーサポートに問い合わせて、インデックスの追加を依頼してください。インデックスを利用できるようになると、クエリの実行速度が上がり、ユーザの生産性も向上します。

 

まとめ

SOQL クエリ、レポート、リストビューの検索条件の選択度を Force.com クエリオプティマイザがどのように評価するのかを理解できたら、実際にやってみましょう。シンプルな SOQL クエリを使って、皆さんが使用する検索条件の選択度の評価に使える統計データを簡単に取得できます。"セレクティブ"と評価された項目にはインデックスを付与します。これにより、その項目に関連付けられた検索条件を含むクエリを効率的に実行できるようになり、ユーザの生産性がアップします。

 

関連リソース

 

執筆者について

Steve Bobrowski は、セールスフォース・ドットコムの Customer-Centric Engineering グループに属する Technical Enablement チームのメンバーで、アーキテクトエバンジェリストとして活躍しています。チームのミッションは、「お客様の Salesforce ソリューションに対する理解を助け、技術的な判断にもとづいて的確に導入を進めていただけるようにする」ことです。DeveloperForce の「アーキテクト向けコアリソース」ページには、チームメンバーが集めたさまざまなリソースがまとめられています。ぜひチェックしてください。