More than 3 years have passed since last update.

RDBMS特にDWHにおけるユーザー定義関数呼び出しオーバーヘッドによる性能劣化について

Last updated at 2020-09-29Posted at 2020-09-27

はじめに

多くの RDBMS では手続き型言語でユーザー定義関数（ストアドファンクション）を作成することができます。コード・ロジック再利用の観点からは有用ですが、SQL文からのユーザー定義関数の呼び出しはかなりオーバーヘッドがあり、呼び出し回数によっては性能劣化の原因になることが多いです。

この事は DWH 系システムでは特に顕著で、データモデルやシステムのアーキテクチャの設計方針に影響を与えるため、DB/DWH の技術者は大体知っているのですが、アプリ技術者には知らない人も多いようです。

そこで今回は、Oracle, SQL Server, MySQL, PostgreSQL において、ユーザー定義関数を利用した場合とそうでない場合でどれぐらい性能差があるかを実測してみたいと思います。

また、最後の章で回避策もいくつか紹介します。

実測環境

今回は Amazon EC2 のm5.large（vCPU 2コア、メモリ 8GB）上の CentOS 7 の環境で測定してます。利用している RDBMS のバージョンは以下です。

Oracle：19.3.0.0
SQL Server：2019 (15.0.4053.23)
MySQL：8.0.21
PostgreSQL：13.0

実測対象の処理ですが、以下のカラムを持つテーブル orders（注文テーブル）に対して、customer_age（顧客年齢）列から算出した年代毎にorder_amt（注文金額）の合計を集計する処理とします。

order_id：注文ID
customer_age：顧客年齢
order_amt：注文金額

実測の仕方についていくつか注釈を

同一の1,000万件のCSVデータを Oracle, SQL Server, MySQL, PostgreSQL の ordersテーブルに読み込んでいます。
キャッシュサイズとソートに利用するメモリサイズは十分大きい値を設定しています。
どの RDBMS においてもパラレルクエリは利用していません。
実行計画作成やディスクI/Oなどの影響を除くために、4回連続で処理を実施し、2～3回目の実行の経過時間の平均を評価します。

実測結果

結果だけ先に示します。ユーザー定義関数の利用ありで遅くなる程度は RDBMS により異なりますが、どのケースでも無視できない程度の性能劣化になります。

RDBMS	ユーザー定義関数あり	ユーザー定義なし	性能劣化
Oracle	18.61秒	1.71秒	10.9倍
SQL Server	42.49秒	1.89秒	22.5倍
MySQL	60.14秒	10.27秒	5.9倍
PostgreSQL	7.28秒	2.68秒	2.7倍

Oracle の場合

年代を算出するためのユーザー定義関数は以下になります。

CREATE OR REPLACE FUNCTION get_age_group(age NUMBER)
RETURN VARCHAR
AS
BEGIN
    RETURN CASE WHEN age < 20 THEN '20歳未満'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;
END;
/

このユーザー定義関数を使った SQL 文は以下です。

SELECT
    get_age_group(customer_age) age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    get_age_group(customer_age);

ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。

SELECT
    CASE WHEN customer_age < 20 THEN '20歳未満'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    CASE WHEN customer_age < 20 THEN '20歳未満'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END;

この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が10.9倍遅いです。

処理	2回目	3回目	4回目
ユーザー定義関数あり	18.62	18.51	18.69
ユーザー定義関数なし	1.70	1.74	1.70

SQL Server の場合

年代を算出するためのユーザー定義関数は以下になります。

CREATE FUNCTION get_age_group(@age SMALLINT)
RETURNS VARCHAR(10)
AS
BEGIN
	RETURN CASE WHEN @age < 20 THEN '20歳未満'
			WHEN @age < 30 THEN '20代'
			WHEN @age < 40 THEN '30代'
			WHEN @age < 50 THEN '40代'
			WHEN @age < 60 THEN '50代'
			ELSE '60歳以上' END;
END

このユーザー定義関数を使った SQL 文は以下です。

SELECT
	dbo.get_age_group(customer_age) age_group,
	sum(order_amt) sum_order_amt
FROM
	orders
GROUP BY
	dbo.get_age_group(customer_age)

ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。

SELECT
	CASE
		WHEN customer_age < 20 THEN '20歳未満'
		WHEN customer_age < 30 THEN '20代'
		WHEN customer_age < 40 THEN '30代'
		WHEN customer_age < 50 THEN '40代'
		WHEN customer_age < 60 THEN '50代'
		ELSE '60歳以上' 
	END age_group,
	sum(order_amt) sum_order_amt
FROM
	orders
GROUP BY
	CASE
		WHEN customer_age < 20 THEN '20歳未満'
		WHEN customer_age < 30 THEN '20代'
		WHEN customer_age < 40 THEN '30代'
		WHEN customer_age < 50 THEN '40代'
		WHEN customer_age < 60 THEN '50代'
		ELSE '60歳以上' 
	END

この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が22.5倍遅いです。

処理	2回目	3回目	4回目
ユーザー定義関数あり	42.56	42.50	42.40
ユーザー定義関数なし	1.89	1.89	1.89

MySQL の場合

年代を算出するためのユーザー定義関数は以下になります。

CREATE FUNCTION get_age_group(age SMALLINT)
RETURNS VARCHAR(10) DETERMINISTIC NO SQL
    RETURN CASE WHEN age < 20 THEN '10代'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;

このユーザー定義関数を使った SQL 文は以下です。

SELECT
    get_age_group(customer_age) age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    age_group;

ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。MySQL は GROUP BY に列別名を使えるのは良いですね。

SELECT
    CASE WHEN customer_age < 20 THEN '10代'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    age_group;

この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が5.9倍遅いです。

処理	2回目	3回目	4回目
ユーザー定義関数あり	60.21	59.84	60.38
ユーザー定義関数なし	10.2	10.32	10.25

PostgreSQL の場合

年代を算出するためのユーザー定義関数は以下になります。

CREATE OR REPLACE FUNCTION get_age_group(age SMALLINT)
RETURNS VARCHAR(10)
AS
$$
BEGIN
    RETURN CASE WHEN age < 20 THEN '10代'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;
END;
$$ LANGUAGE plpgsql;

このユーザー定義関数を使った SQL 文は以下です。

SELECT
    get_age_group(customer_age) age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    age_group;

ユーザー定義関数を使わずに、年代算出ロジックを直接埋め込んだ SQL 文は以下です。PostgreSQL も GROUP BY に列別名を使えます。

SELECT
    CASE WHEN customer_age < 20 THEN '10代'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END age_group,
    sum(order_amt)
FROM
    orders
GROUP BY
    age_group;

この2つの SQL 文の実行経過時間は以下になりました。ユーザー定義関数ありの方が2.7倍遅いです。

処理	2回目	3回目	4回目
ユーザー定義関数あり	7.38	7.32	7.15
ユーザー定義関数なし	2.68	2.68	2.68

回避策

コード・ロジックの再利用ができないは流石に辛いのですが、こういうときはビューを使うのが一般的です。

Oracle を例に示します。

CREATE VIEW v_orders
AS
SELECT
    order_id,
    customer_age,
    order_amt,
    CASE WHEN customer_age < 20 THEN '20歳未満'
         WHEN customer_age < 30 THEN '20代'
         WHEN customer_age < 40 THEN '30代'
         WHEN customer_age < 50 THEN '40代'
         WHEN customer_age < 60 THEN '50代'
         ELSE '60歳以上' END age_group
FROM
    orders;

このビューを使った以下の SQL 文はユーザー定義関数なしとほぼ同じ性能が出ます。

SELECT
    age_group,
    sum(order_amt)
FROM
    v_orders
GROUP BY
    age_group;

ただし、ビューを利用する方法は、様々なテーブルで同じロジックを適用したい場合にはテーブル毎にビューを作成する必要があるので、冗長性は残ります。

ビューを使う以外には、RDBMS の機能を利用する手段もあります。私が知っている範囲で紹介します。

Oracle の場合

PRAGMA UDF を利用すると性能劣化の程度が多少軽減されます。今回のケースでは2.3倍程度に性能劣化が抑えられます。

CREATE OR REPLACE FUNCTION get_age_group_pg(age NUMBER)
RETURN VARCHAR
AS
PRAGMA UDF;  // この要素を追加
BEGIN
    RETURN CASE WHEN age < 20 THEN '20歳未満'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;
END;
/

【2020/9/29 追記】
また、以下のようにユーザー定義関数を DETERMINISTIC にする、またはファンクション結果キャッシュを使った場合、PRAGMA UDF には及びませんが、ほんの少し改善されます。

DETERMINISTIC の場合：7.18倍

CREATE OR REPLACE FUNCTION get_age_group_de(age NUMBER)
RETURN VARCHAR
DETERMINISTIC // 関数が決定的であると明示的に指定
AS
(略)

ファンクション結果キャッシュの場合：7.04倍

CREATE OR REPLACE FUNCTION get_age_group_rs(age NUMBER)
RETURN VARCHAR
RESULT_CACHE // ファンクション結果キャッシュを有効化
AS
(略)

（ファンクション結果キャッシュはもっと速いかと思ったら、そうでもなかった…）

まだ正式リリースされていない Oracle20c になると、SQL マクロという機能が追加され、性能劣化なしでロジックの再利用ができるそうです。

SQL Server の場合

SQL Server 2019 からはスカラー UDF のインライン化という最適化機能が実現されています。

Microsoft SQL Server でのスカラー UDF のインライン化 - SQL Server

ある一定の条件を満たすユーザー定義関数呼び出しは SQL 文実行時にインライン化され、その場合は性能劣化なしでユーザー定義関数が利用できます。

ただし、この「一定の条件」の中に「スカラー UDF を呼び出すクエリの GROUP BY 句で、スカラー UDF 呼び出しが参照されていない。」という条件があり、今回の SQL 文ではインライン化されません。以下のように小手先の対応をすることもできますが。

SELECT
	a.age_group,
	sum(a.order_amt)
FROM
	(
	SELECT
		dbo.get_age_group(customer_age) age_group,
		order_amt
	FROM
		orders
	) a
GROUP BY
	age_group

このSQL文では、性能劣化は1.7倍まで抑えることができます。

（しかし、GROUP BYでユーザー定義関数が使えないのは痛いなぁ。他にいくつか苦しい条件もあるし）

PostgreSQL の場合

PostgreSQL ではユーザー定義関数の利用において、手続き型言語（今回は　PL/pgSQL）だけではなく、他に3種類の利用方法があります。（37.3. ユーザ定義関数参照）

問い合わせ言語関数（SQLで作成された関数）
手続型言語関数（PL/pgSQLやPL/Tclなどで作成された関数）
内部関数
C言語関数

このうち、1番目の問い合わせ言語関数を利用することで、今回のケースでは性能劣化を回避することができます。というか、今回の単純さであれば普通はこちらを利用しますね。

CREATE OR REPLACE FUNCTION get_age_group_sql(age SMALLINT)
RETURNS VARCHAR(10)
AS
$$
    SELECT CASE WHEN age < 20 THEN '10代'
                WHEN age < 30 THEN '20代'
                WHEN age < 40 THEN '30代'
                WHEN age < 50 THEN '40代'
                WHEN age < 60 THEN '50代'
                ELSE '60歳以上' END;
$$ LANGUAGE sql;

さいごに

本記事では、ユーザー定義関数の利用が大きな性能劣化の原因になることを紹介しました。

ユーザー定義関数の呼び出しオーバーヘッドがかなり大きいのは DB/DWH 技術者の間では常識ですが、だからといって納得している・仕方ないと思っているかというと、全くそんなことはなく、正直怒りしか沸いてきません。もちろん、内部で SQL 文を実行できてしまうなど自由度が非常に高い手続き型言語を SQL 文から呼び出すことの難しさは分からなくもないのですが…

Oracle や SQL Server では RDBMS の機能として解決しようとなりつつあるように見えるので、今後に期待したいです。

あと、新しめの DWH 系 DB（Redshift や Snowflake など）ではどうなんでしょうね。過去のしがらみがない分、初めからこの問題がないと良いのですが。

【2020/9/29 追記】
データは違いますが、Snowflakeで確認してくださった方がいるようです。ご参考まで。

ユーザー定義関数ありで 3.2 倍悪化
ストアドプロシージャで動的SQL使用すると劣化は 1.59 倍に抑えられる

Snowflakeでどうなるか気になると書いて頂いていたので、試してみました。テーブルはSNOWFLAKE_SAMPLE_DATA.TPCH_SF10.ORDERSをlimit 1000000したものです。#SnowflakeDB

■前提条件：
・X-Smallというウェアハウス(8vcpu)を使用
・リザルトキャッシュは無効化
・ローカルディスクキャッシュは有効化 https://t.co/EfGozKLm7N
— Mineaki Motohashi (@mmotohas) September 28, 2020

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up