こんにちは、エニグモ嘉松です。

BUYMAのプロモーションやマーケティングを行っている事業部に所属、その中のデータ活用推進室という部署で会社のデータ活用の推進やマーケティング・オートメーションツール（MAツール）を活用した販促支援、CRMなどを担当しています。

この記事は Enigmo Advent Calendar 2023 の 25 日目の記事です。

はじめに

この記事ではGoogleから提供されているBigQueryのオンライマニュアル「関数のベストプラクティス（Best practices for functions）」を試してみた結果を紹介していきます。

「関数のベストプラクティス」（日本語版）
- https://cloud.google.com/bigquery/docs/best-practices-performance-functions?hl=ja

「関数のベストプラクティス」では、以下の4つのベストプラクティスが紹介されています。

文字列の比較を最適化する
集計関数を最適化する
分位関数を最適化する
UDF を最適化する

はじめは4つ全てを1つの記事で紹介するつもりでしたが、記事を制作していく中で1つでもそれなりの記事ボリュームがあることが分かったので、読みやすさを重視して1つの記事で1つのベストプラクティスを紹介していくことにしました。ということで、この記事は「その１」として「文字列の比較を最適化する」を試した結果を紹介していきます。

文字列の比較を最適化する

ベストプラクティス

可能であれば、REGEXP_CONTAINS ではなく LIKE を使用します。

BigQuery では、REGEXP_CONTAINS 関数または LIKE 演算子を使用して文字列を比較できます。 REGEXP_CONTAINS は多くの機能を提供しますが、実行に時間がかかります。 REGEXP_CONTAINS ではなく LIKE を使用すると、処理時間が短くなります。特に、ワイルドカード一致など、REGEXP_CONTAINS が提供する正規表現をフルに活用する必要がない場合には処理時間が短くなります。

次の REGEXP_CONTAINS 関数の使用を検討してください。

SELECT
  dim1
FROM
  `dataset.table1`
WHERE
  REGEXP_CONTAINS(dim1, '.*test.*');

このクエリは次のように最適化できます。

SELECT
  dim1
FROM
  `dataset.table`
WHERE
  dim1 LIKE '%test%';

なるほど。やはり機能の多い関数、複雑なパターンに対応できる関数よりも、単純なことしか出来ない関数の方が処理は軽い（速い）と。言われてみれば当たり前といえば当たり前ではありますが。単純なことしてできない関数が利用できるケースであれば、そちらを使ったほうが良い、正規表現による検索が必要ない場合は LIKEを使いましょう、ということですね。そもそも REGEXP_CONTAINS より LIKE を使うことを最初に考えるとは思うけどww（REGEXP_CONTAINSの方が汎用性が高いので常にREGEXP_CONTAINSを使っています、みたいな人は注意が必要です！）

試してみた

検証方法

REGEXP_CONTAINSとLIKEを使ったクエリを実行して比較していきます。

比較する候補となる値は 経過時間 と 消費したスロット時間の２つ、クエリの「実行の詳細」から取得できます。

それぞれの意味は以下の通りです。

経過時間
- クエリが開始されてから完了するまでの時間
  - サーバの使用状況などによる待機時間も含まれます
消費したスロット時間
- 「スロットとは、SQL クエリの実行に必要な演算能力の単位です。」（「BigQueryのコンソールの（？）」より）
- スロットについての詳細は以下のGoogleのマニュアル「スロットについて」を参照ください。
  - https://cloud.google.com/bigquery/docs/slots?hl=ja
- BigQueryスロットは、BigQueryでSQLクエリを実行するために使用される仮想CPUということで、ざっくり言うとクエリ（SQLの実行）に使用したCPU使用量です。

今回の検証では 消費したスロット時間 を比較ます。（経過時間だとGoogleのその時のサーバの負荷といった外部要因が加わるため）

クエリはそれぞれ5回ずつ実行してその平均値を比較します。クエリを実行するとクエリの結果がキャッシュされるので、キャッシュを使用しないように設定を行います。

なお、対象のテーブルは約１億件のテーブルを対象にしました。

キャッシュクリアの方法

検証中はキャッシュを使用してしまうと正しい計測ができないので、キャッシュを使用しないように設定を行います。

https://cloud.google.com/bigquery/docs/best-practices-performance-functions?hl=ja#optimize_string_comparison

検証結果

消費したスロット時間（時間：分：秒）

試行回数	REGEXP_CONTAINS	LIKE
1	0:03:06	0:02:50
2	0:03:22	0:02:29
3	0:03:02	0:02:36
4	0:03:06	0:02:20
5	0:02:56	0:02:18

平均	0:03:06	0:02:31

REGEXP_CONTAINSを使用した場合の平均の「消費したスロット時間」は 3分6秒 だったのに対して、LIKEでは 2分31秒 と35秒短縮、減少率は 80.79% と約80%に短縮、約20%改善されました。
この20%の改善をどう取るか。かなり短縮できたと取るか、さほど変わらないと取るか。
個人的には、思ったほど変わらないな、という印象でした。
といのも、SQLではテーブルの結合方法（JOIN）や、絞り込み条件（WHERE句）をチューニングすると実行時間が半分になったり、場合によっては1/10になったりすることもざら、珍しくないため。
ただ、単純に LIKE を使うだけで20%削減されるのであれば、それは価値ありますよね。
このクエリで検索の対象としているカラムの平均の文字数は33文字と少なかったため大きな差が出なかった、もう少し文字数が多いカラムを対象にしたらもう少し差が出るのでは、と考え文字数の多いカラムを使って検証を実施しました。

追加検証結果（時間：分：秒）

平均文字数が758文字のカラムを使って比較

消費したスロット時間（時間：分：秒）

試行回数	REGEXP_CONTAINS	LIKE
1	0:59:53	0:55:26
2	1:02:00	0:53:43
3	1:02:00	0:51:51
4	1:01:00	0:50:43
5	1:04:00	0:52:02

平均	1:01:47	0:52:45

まずカラムの平均文字数が増えたことで「消費したスロット時間」も増加しています。
平均文字数が33文字では約3分だったのに対して、758文字は約60分と約20倍に。
- 当然ですが検索する文字数が増えればその分として処理の時間も増えますね。
REGEXP_CONTAINSを使用した場合の平均の「消費したスロット時間」は 1時間1分47秒 だったのに対して、LIKEでは 52分45秒 と9分2秒短縮、減少率は 85.39% と約85%に短縮、15%改善されました。
平均文字数が33文字のときは約80%に短縮されたのに対して、758文字では約85%と短縮の率は減少しました。
- この辺りの差は文字数のバラツキや検索する文字によっても差が出るのかもしれません。
文字数の多いカラムでは LIKE を使うことでよりパフォーマンスに差が出ると思いましたが、大きな違いはありませんでした。