こんにちは。株式会社divxのエンジニア西岡です。
現在、進行中のプロジェクトにおいて、開発ルールに従ってストアドプロシージャを使用してデータ取得を行っています。
この手法はデータベース内でSQL文を直接記述できるため、柔軟なデータ操作が可能になります。
また、ストアドプロシージャを使用することで、多くの処理をサーバー側で実行できるため、場合によってはネットワークトラフィックを低減できます。
しかし、これまでORMを使用してきた私にとっては必要なデータを取得してから計算処理を行うのが一般的なイメージだったので、アプリケーション側で行う計算の一部をストアドプロシージャで実装することについて、どこまで処理を任せるべきか迷うことがありました。
そこで、ストアドプロシージャにおける計算処理とアプリケーション側での計算処理のパフォーマンスを比較し、どちらの方法がより効果的かを検証することにしました。
この結果を基に、今後の開発プロセスでの意思決定をより良いものにするための参考データを得ることを目指しています。
ストアドプロシージャとは
ストアドプロシージャは、データベース内に保存された一連のSQL文や制御構文を集めたプログラムです。これにより、複数のSQL操作を一括で実行することができ、アプリケーションからはそのストアドプロシージャを呼び出すだけで複雑なデータ処理を行えます。
具体的には、データの挿入、更新、削除、集計など、データベースの操作が効率的に行えるため、大規模なデータを扱う場合に特に力を発揮します。
また、サーバー側での処理が行われるため、データの量や複雑さに依存するので必ずではないですが、ネットワークの負荷を軽減することができます。このような特徴から、ストアドプロシージャは、データベース操作の効率化とアプリケーションの柔軟性を高める重要な要素となっています。
参考URL
※ORM（Object-Relational Mapping）とは その名の通りプログラミング言語のオブジェクトとデータベースのデータをマッピングする技術です。これにより、開発者は直接SQLを記述することなく、データベースの操作（例えば、データの取得、挿入、更新、削除）を行うことができます。これにより、コーディングの効率性と可読性が向上し、多くのプロジェクトで活用されています。
参考URL
実際に比較
使用環境
Windows10
CPU : 12th Gen Intel Core i7-1255U 1.70 GHz
メモリ : 16.0 GB
Visual Studio2022 17.7.6
SQL Server Management Studio 19.2.56.2
使用データ
データは5000人の顧客の集計をランダムに作成したもの。
各顧客の製品A・B・Cの使用率をランダムに200日分記録。全100万件。
100万件のデータから、大・中・小のデータ量を計算し、それぞれの実行速度を比較。
CREATE TABLE customer (
create_date DATE,
customer_name NVARCHAR(4),
region NVARCHAR(10),
age_group INT CHECK (age_group IN (20, 30, 40)),
device_type CHAR(1),
product_A INT CHECK (product_A BETWEEN 0 AND 100),
product_B INT CHECK (product_B BETWEEN 0 AND 100),
product_C INT CHECK (product_C BETWEEN 0 AND 100)
);
出来上がったデータはこんな感じです。
比較方法
取得方法は原則ストアドプロシージャ。
時間の計測はC#のStopwatch クラスを使用し、ストアドプロシージャの呼び出しから計算終了までの全体の時間を計測する。
実装内容
コードは以下のように実装しました。
ビジネスロジック部分
実行のメソッド（それぞれアンコメントして動作を確認していきます。）
public void SpeedTestResult()
{
}
ストアドプロシージャで計算した結果を取得するメソッド
public void GetAllSummaryTest(string region, string customer_name)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
var results = dbCon.GetAllSummaryResults(region, customer_name);
stopwatch.Stop();
Debug.WriteLine($"データ取得開始から終了までの時間: {stopwatch.ElapsedMilliseconds} ms");
}
ストアドプロシージャで必要なデータを全件取得し、アプリケーション側で計算するメソッド
public void GetAllTest(string region, string customer_name)
{
Stopwatch stopwatch = new Stopwatch();
stopwatch.Start();
List <GetAll_Result> customerList = dbCon.GetAllResults(region, customer_name);
stopwatch.Stop();
int ms = (int)stopwatch.ElapsedMilliseconds;
Debug.WriteLine($"データ取得の時間: {ms} ms");
stopwatch.Restart();
var summary = customerList .GroupBy(s => new
{
s.CustomerName,
s.Region,
s.AgeGroup,
s.DeviceType
})
.Select(g => new GetAllSummary_Result
{
CustomerName = g.Key.CustomerName,
Region = g.Key.Region,
AgeGroup = g.Key.AgeGroup,
DeviceType = g.Key.DeviceType,
AvgProductA = (decimal)g.Average(s => s.ProductA),
AvgProductB = (decimal)g.Average(s => s.ProductB),
AvgProductC = (decimal)g.Average(s => s.ProductC),
MinProductA = g.Min(s => s.ProductA),
MinProductB = g.Min(s => s.ProductB),
MinProductC = g.Min(s => s.ProductC),
MaxProductA = g.Max(s => s.ProductA),
MaxProductB = g.Max(s => s.ProductB),
MaxProductC = g.Max(s => s.ProductC),
}).ToList();
stopwatch.Stop();
ms += (int)stopwatch.ElapsedMilliseconds;
Debug.WriteLine($"データ取得開始から終了までの時間: {ms} ms");
}
モデル部分
public List<GetAllSummary_Result> GetAllSummaryResults(string region, string customer_name)
{
List<SqlParameter> parameters = new List<SqlParameter>()
{
new SqlParameter("@region", region ?? (object)DBNull.Value),
new SqlParameter("@customer_name", customer_name ?? (object)DBNull.Value)
};
try
{
return this.Database.SqlQuery<GetAllSummary_Result>(@"EXEC GetAllSummary @region, @customer_name", parameters.ToArray()).ToList();
}
catch(Exception e)
{
Debug.WriteLine($"エラー： {e.Message}");
}
return null;
}
public List<GetAll_Result> GetAllResults(string region, string customer_name)
{
List<SqlParameter> parameters = new List<SqlParameter>()
{
new SqlParameter("@region", region ?? (object)DBNull.Value),
new SqlParameter("@customer_name", customer_name ?? (object)DBNull.Value)
};
try
{
return this.Database.SqlQuery<GetAll_Result>(@"EXEC GetAll @region, @customer_name", parameters.ToArray()).ToList();
}
catch(Exception e)
{
Debug.WriteLine($"エラー： {e.Message}");
}
return null;
}
ストアドプロシージャ部分
CREATE PROCEDURE [dbo].[GetAllSummary]
(
@region nvarchar(10),
@customer_name nvarchar(4)
)
AS
BEGIN
SELECT
customer_name AS CustomerName,
region AS Region,
age_group AS AgeGroup,
device_type AS DeviceType,
CAST(AVG(product_A) AS DECIMAL) AS AvgProductA,
CAST(AVG(product_B) AS DECIMAL) AS AvgProductB,
CAST(AVG(product_C) AS DECIMAL) AS AvgProductC,
MIN(product_A) AS MinProductA,
MIN(product_B) AS MinProductB,
MIN(product_C) AS MinProductC,
MAX(product_A) AS MaxProductA,
MAX(product_B) AS MaxProductB,
MAX(product_C) AS MaxProductC
FROM
customer
WHERE
(@region IS NULL OR region = @region)
AND (@customer_name IS NULL OR customer_name = @customer_name)
GROUP BY
region,
customer_name,
age_group,
device_type
END
CREATE PROCEDURE [dbo].[GetAll]
(
@region nvarchar(10),
@customer_name nvarchar(4)
)
AS
BEGIN
SELECT
customer_name AS CustomerName,
region AS Region,
age_group AS AgeGroup,
device_type AS DeviceType,
product_A AS ProductA,
product_B AS ProductB,
product_C AS ProductC
FROM
customer
WHERE
(@region IS NULL OR region = @region)
AND (@customer_name IS NULL OR customer_name = @customer_name)
END
結果
以下のような検証結果になりました。
大きめのデータの比較
// 全データを集計した結果を取得（100万件のデータ計算）
getAllSummary_Results(null, null);
データ取得開始から終了までの時間: 1812 ms
// 全データの結果を取得して集計（100万件のデータ計算）
//getAll_Results(null, null);
データ取得の時間: 4215 ms
データ取得開始から終了までの時間: 4777 ms
中くらいのデータ比較
// 地域Aの全データを集計した結果を取得（20万件程度のデータ計算）
//GetAllSummaryTest("地域A", null);
データ取得開始から終了までの時間: 1561 ms
// 地域Aの全データの結果を取得して集計（20万件程度のデータ計算）
//GetAllTest("地域A", null);
データ取得の時間: 1993 ms
データ取得開始から終了までの時間: 2119 ms
小さめのデータ比較
// 1人の顧客の全データを集計した結果を取得（200件程度のデータ計算）
//GetAllSummaryTest("地域C", "TBJL");
データ取得開始から終了までの時間: 1716 ms
// 1人の顧客の全データの結果を取得して集計（200件程度のデータ計算）
//GetAllSummaryTest("地域C", "TBJL");
データ取得の時間: 1733 ms
データ取得開始から終了までの時間: 1735 ms
テーブルにPKやインデックスを貼っていない状態ではありますが、ある程度想定していた結果になりました。
やはりデータが大きいほど差は出ますね。
まとめ
計算処理をストアドプロシージャに任せるかどうかという検証でしたが、結論としてはプロジェクトで扱うデータ量に応じて積極的に検討することが重要だと感じました。
データ量がそれほど多くない場合は、データを取得してから計算を行った場合と速度的にはあまり変わらなかったので、メンテナンス性やメリット・デメリット、さらには今後の運用を考慮しながら、柔軟にアプローチを決定するといいと思います。
また、ストアドプロシージャを効果的に利用するためにはSQLに関する知識が必要不可欠です。SQLを使いこなせる方にとっては、ストアドプロシージャはその経験を存分に生かせる場となるでしょうし、逆にあまり触れたことがない方にとっては、良い学びの機会となるかもしれません。
新たな技術を取り入れることは常にチャレンジですが、その分得られるものも多いと思います。
ストアドプロシージャの導入を検討する際は、今回の検証が参考になると幸いです。
お悩みご相談ください
