はじめに

Snowflakeにおけるパフォーマンスチューニングには複数の方法があります。
その１つのSearch Optimization Service(検索最適化サービス)について、勉強した内容をまとめておこうと思います。

Search Optimization Serviceとは

DWHは大量データに対する集計が得意ですが、100億件のテーブルから10件のレコードを取得するような少数データの検索は苦手です。

Snowflakeでは、Search Optimization Serviceにより、少数データの検索を高速化することができます。

Search Optimization Serviceは、検索アクセスパスと呼ばれる仕組みを使って、クエリのWHERE句で指定したデータに高速にアクセスします。

検索アクセスパスには、テーブルの列の値が、どのマイクロパーティションに入っているかの情報を保存してします。
（Oracle等のRDBMSでいえばインデックスのような役割を果たします）

Search Optimization Serviceを利用するための手順

　Search Optimization Serviceの利用にはクレジット消費が伴い、さらに、テーブルによってはクエリを高速化できない場合もあります。
そのため、以下①～④の手順で、Search Optimization Serviceの利用を検討することを推奨します。

①Search Optimization Serviceでクエリの高速化が期待できるテーブルであることを確認する
②どの列でクエリを高速化できるか確認する
③Search Optimization Serviceを利用した場合のコストを把握する
④Search Optimization Serviceを有効化する

①Search Optimization Serviceでクエリの高速化が期待できるテーブルであることを確認する

　以下の条件を満たしているテーブルでクエリの高速化が期待できます。
　・容量が300GB以上であること

②どの列でSearch Optimization Serviceでクエリを高速化できるか確認する

　Search Optimization Serviceは、列単位で有効化することができます。
　以下の条件を満たしている列に対して有効化することを推奨します。
　・クエリのWHERE句で=もしくはINで条件指定される列であること
　　（WHERE 列名 = <値> もしくは WHERE 列名 IN <値>）
　・少なくとも100,000個以上の固有値を持つ列であること
　・クエリのレスポンスタイムが10秒以上かかっていること（Search Optimization Serviceを有効化していない状態で）

③Search Optimization Serviceを利用した場合のコストを把握する

　Search Optimization Serviceを有効化すると、以下のコストが発生します。
　■ストレージコスト
　　検索アクセスパスを保存するためのストレージが必要になります。
　　列の値の固有値の種類が多いほどコストが増加します。

　■コンピューティングコスト
　　以下の用途でコンピューティングコストが発生します。
　　・検索アクセスパスの作成
　　　検索アクセスパスを作成するためにウェアハウスが使用されます。

　　・検索アクセスパスのメンテナンス
　　　InsertやUpdate等のDMLでテーブルのデータが変更されると、テーブルデータの変更に対応して検索アクセスパスを最新化する必要があります。検索アクセスパスのメンテナンスにはウェアハウスが使用されます。

　Search Optimization Serviceのコストは以下の要因で左右されます。
　・Search Optimization Serviceを有効にする列数と列の固有値の数
　・変更されるデータの量

　以下の関数を使用することで、Search Optimization Serviceを利用した場合のコストを見積もることができます。

select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('<テーブル名>','<列名>');

　関数は、以下の予想コストを戻り値として返します。

コスト項目	説明	コストの測定単位
BuildCosts	検索アクセスパス作成時のウェアハウスの使用料	クレジット
StorageCosts	検索アクセスパスを保存するためのストレージコスト	TB
MaintenanceCosts	DMLでテーブルデータが変更された場合に検索アクセスパスをメンテナンスするためのウェアハウスの使用料	クレジット

　予想コストを踏まえた上で、Search Optimization Serviceを利用することを推奨します。

④Search Optimization Serviceを有効化する

　以下のDDLを実行して、Search Optimization Serviceを有効にするテーブルと列名を指定します。

alter table <テーブル名> ADD SEARCH OPTIMIZATION ON EQUALITY(列名1, 列名2, 列名3・・・);

Search Optimization Serviceの効果の検証

　Search Optimization Serviceにより、クエリのレスポンスタイムがどれだけ短縮できるかを以下の手順で検証しました。
<検証手順>
　手順1：Search Optimization Service設定前のクエリのレスポンスタイムを計測する
　手順2：Search Optimization Serviceを利用した場合のコストを見積もる
　手順3：Search Optimization Serviceを設定する
　手順4：Search Optimization Service設定後のクエリのレスポンスタイムを計測する

<検証条件>
　・検証対象のテーブル：web_returns (容量：約300GB)
　・実行するクエリ：WHERE句にSearch Optimization Serviceを有効化した列を指定したクエリ(7,200,334,357行中、1行を取得する)
　・利用するウェアハウス：Sサイズ
　・リザルトキャッシュ：利用しない

<検証結果>
⇒クエリのレスポンスタイムを1/50以下に短縮できた。
　・Search Optimization Service設定前：53秒
　・Search Optimization Service設定後：1.2秒

以下は、手順に従って行った検証です。
手順1：クラスタリング設定前のクエリのレスポンスタイムを計測する

手順2：Search Optimization Serviceを利用した場合のコストを見積もる
　SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTSを使って見積コストを確認します。

以下は、SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTSの出力結果です。
　・BuildCosts(検索アクセスパス作成時のウェアハウスの使用料)：1.807034クレジット
　・StorageCosts(検索アクセスパスを保存するためのストレージコスト)：0.035153TB
　・MaintenanceCosts(DMLでテーブルデータが変更された場合に検索アクセスパスをメンテナンスするためのウェアハウスの使用料)：NotAvailable

{
  "tableName" : "WEB_RETURNS",
  "searchOptimizationEnabled" : false,
  "costPositions" : [ {
    "name" : "BuildCosts",
    "costs" : {
      "value" : 1.807034,
      "unit" : "Credits"
    },
    "computationMethod" : "Estimated",
    "comment" : "estimated via sampling"
  }, {
    "name" : "StorageCosts",
    "costs" : {
      "value" : 0.035153,
      "unit" : "TB",
      "perTimeUnit" : "MONTH"
    },
    "computationMethod" : "Estimated",
    "comment" : "estimated via sampling"
  }, {
    "name" : "MaintenanceCosts",
    "computationMethod" : "NotAvailable",
    "comment" : "Insufficient data to compute estimate for maintenance cost. Table is too young. Requires 7 day(s) of history."
  } ]
}

手順3：Search Optimization Serviceを設定する
　DDLを実行してSearch Optimization Serviceを有効化します。

手順4：Search Optimization Service設定後のクエリのレスポンスタイムを計測する

その他のパフォーマンスチューニングについて

Search Optimization Service以外のパフォーマンスチューニングの方法には以下があります。

参考にしたサイト

・Snowflakeドキュメント(https://docs.snowflake.com/ja/user-guide/search-optimization-service#managing-the-costs-of-the-search-optimization-service)
・Snowflakeコミュニティ(https://community.snowflake.com/s/article/Search-Optimization-When-How-To-Use)

ご案内

株式会社ジールでは、「ITリテラシーがない」「初期費用がかけられない」「親切・丁寧な支援がほしい」「ノーコード・ローコードがよい」「運用・保守の手間をかけられない」などのお客様の声を受けて、オールインワン型データ活用プラットフォーム「ZEUSCloud」を月額利用料にてご提供しております。
ご興味がある方は是非下記のリンクをご覧ください：
https://www.zdh.co.jp/products-services/cloud-data/zeuscloud/?utm_source=qiita&utm_medium=referral&utm_campaign=qiita_zeuscloud_content-area

Snowflakeにおけるパフォーマンスチューニング：Search Optimization Service