はじめに
大前提として、相関サブクエリとウィンドウ関数は、それぞれ異なる役割を持って設計されています。
- 相関サブクエリ: 外側のクエリの各行に対して個別に処理を行い、サブクエリの結果を返します。行ごとの処理が必要な場合に利用します
- ウィンドウ関数: データセット全体に対して行やグループに基づいた集計や分析を効率的に実行するためのもので、一度に複数の行を扱うのが特徴です
両者は異なる使い方をしますが、相関サブクエリで集計を行っているプログラムを散見するため、相関サブクエリをウィンドウ関数で書き換えるメリットをパフォーマンスの観点で比較してみます。
サンプルテーブルの作成
データがある程度必要ので、ランダムデータ生成させる。
-- サンプルテーブルの作成
CREATE TABLE Sales (
SalesID INT PRIMARY KEY,
EmployeeID INT,
SaleAmount DECIMAL(10, 2),
SaleDate DATE
);
-- サンプルデータの挿入
-- ランダムなデータを生成するための一時テーブル
CREATE TABLE #RandomData (
ID INT IDENTITY(1,1) PRIMARY KEY,
RandomValue DECIMAL(10, 2)
);
-- 一時テーブルにランダムな値を挿入
INSERT INTO #RandomData (RandomValue)
SELECT TOP 1000 RAND() * 1000
FROM sys.objects s1
CROSS JOIN sys.objects s2;
-- Salesテーブルにランダムなデータを挿入
INSERT INTO Sales (SalesID, EmployeeID, SaleAmount, SaleDate)
SELECT
ID,
ABS(CHECKSUM(NEWID())) % 5 + 1, -- EmployeeID: 1から5のランダムな値
RandomValue,
DATEADD(DAY, -ABS(CHECKSUM(NEWID())) % 365, GETDATE()) -- 過去1年以内のランダムな日付
FROM #RandomData;
-- 一時テーブルの削除
DROP TABLE #RandomData;
相関サブクエリを使用した集計
SET STATISTICS IO ON;
SET STATISTICS TIME ON;
WITH MonthlySales AS (
SELECT
EmployeeID,
FORMAT(SaleDate, 'yyyy-MM') AS MonthDate,
SUM(SaleAmount) AS TotalSales
FROM
Sales
GROUP BY
EmployeeID,
FORMAT(SaleDate, 'yyyy-MM')
)
SELECT
m1.EmployeeID,
m1.MonthDate,
m1.TotalSales,
ISNULL((SELECT TOP 1 m2.TotalSales
FROM MonthlySales m2
WHERE m2.EmployeeID = m1.EmployeeID
AND m2.MonthDate < m1.MonthDate
ORDER BY m2.MonthDate DESC), 0) AS PreviousMonthSales,
CASE
WHEN NOT EXISTS (SELECT 1
FROM MonthlySales m2
WHERE m2.EmployeeID = m1.EmployeeID
AND m2.MonthDate < m1.MonthDate) THEN '-'
WHEN m1.TotalSales >= ISNULL((SELECT TOP 1 m2.TotalSales
FROM MonthlySales m2
WHERE m2.EmployeeID = m1.EmployeeID
AND m2.MonthDate < m1.MonthDate
ORDER BY m2.MonthDate DESC), 0) * 1.5 THEN '↑'
WHEN m1.TotalSales <= ISNULL((SELECT TOP 1 m2.TotalSales
FROM MonthlySales m2
WHERE m2.EmployeeID = m1.EmployeeID
AND m2.MonthDate < m1.MonthDate
ORDER BY m2.MonthDate DESC), 0) * 0.5 THEN '↓'
ELSE '→'
END AS SalesComparison
FROM
MonthlySales m1
ORDER BY
m1.EmployeeID, m1.MonthDate;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
ウィンドウ関数に書き換え
SET STATISTICS TIME ON;
SET STATISTICS IO ON;
WITH MonthlySales AS (
SELECT
EmployeeID,
FORMAT(SaleDate, 'yyyy-MM') AS MonthDate,
SUM(SaleAmount) AS TotalSales
FROM
Sales
GROUP BY
EmployeeID,
FORMAT(SaleDate, 'yyyy-MM')
)
SELECT
EmployeeID,
MonthDate,
TotalSales,
ISNULL(LAG(TotalSales) OVER (PARTITION BY EmployeeID ORDER BY MonthDate), 0) AS PreviousMonthSales,
CASE
WHEN LAG(TotalSales) OVER (PARTITION BY EmployeeID ORDER BY MonthDate) IS NULL THEN '-'
WHEN TotalSales >= LAG(TotalSales) OVER (PARTITION BY EmployeeID ORDER BY MonthDate) * 1.5 THEN '↑'
WHEN TotalSales <= LAG(TotalSales) OVER (PARTITION BY EmployeeID ORDER BY MonthDate) * 0.5 THEN '↓'
ELSE '→'
END AS SalesComparison
FROM
MonthlySales
ORDER BY
EmployeeID, MonthDate;
SET STATISTICS TIME OFF;
SET STATISTICS IO OFF;
比較
まず、ソース量が全然違うので、読みやすさが違いますね。
そして、パフォーマンスの比較は以下の通りです。
| 指標 | 相関サブクエリ | ウィンドウ関数 |
|---|---|---|
| スキャン数 | 240 | 1 |
| 論理読み取り数 | 1440 | 6 |
| 物理読み取り数 | 0 | 0 |
| ページ サーバー読み取り数 | 0 | 0 |
| 先読みの読み取り数 | 0 | 0 |
| ページ サーバー先読みの読み取り数 | 0 | 0 |
| LOB 論理読み取り数 | 0 | 0 |
| LOB 物理読み取り数 | 0 | 0 |
| LOB ページ サーバー読み取り数 | 0 | 0 |
| LOB 先読みの読み取り数 | 0 | 0 |
| LOB ページ サーバー先読みの読み取り数 | 0 | 0 |
結果の分析と考察
STATISTICS IOで生じた差異を分析してみます。
スキャン数について
相関サブクエリは外側のクエリのレコード1件ずつに対して内側のクエリが実行されるためテーブル全体に対するスキャンの数が増えます。
一方で、ウィンドウ関数は一度データをスキャンして、全体のデータに対して一括で集計処理を行うことができます。
論理読み取り数について
相関サブクエリは行ごとにサブクエリを実行するため、メモリ内のデータページへのアクセス回数が増え、論理読み取り数が多くなると考えられます。この頻繁なページアクセスが、パフォーマンスに悪影響を及ぼすことがあります。
ウィンドウ関数は、一度読み込んだデータに対して集計処理を行うため、データページへのアクセスも最小限にしか発生しない。
まとめ
結果は見ての通りウィンドウ関数のほうがパフォーマンスは優れています。
ウィンドウ関数は、全体のデータセットをスキャンした後にメモリ上で処理を行うため、繰り返しデータをスキャンする必要がなく、I/Oの負荷を大幅に軽減できます。
DBに限らずI/Oがパフォーマンスのボトルネックになることが多いので、この点がウィンドウ関数を採用すべき大きなメリットになると思います。
逆に、相関サブクエリは、外部の各行に対してサブクエリを繰り返し実行するため、処理回数が増え、その分データスキャンや読み取り回数が増加します。
大規模なデータセットに対しては、相関サブクエリはパフォーマンスを著しく落とす原因になります。
おわりに
相関サブクエリを利用して集計している箇所は、ウィンドウ関数への置き換えを積極的に推進した方がいいかも！