Agent Grow Tech Notes
🛠️

【SQL】DS検定で公開されている模擬問題をチューニングをしてみた

2024/12/06に公開

この記事は Agent Grow Advent Calendar 2024 の記事です。

記事の概要

SQLチューニングについて簡単な例として、DS検定(データサイエンティスト検定)にて公開されている模擬問題を利用して紹介する。

尚、模擬問題の利用については、事前にデータサイエンティスト協会事務局様より許可をいただいた。

対象者

初級開発SEデータエンジニアであるが、そうでなくとも下記のような方々の参考になれば幸いである。

  • 仕様通りに動かすだけで精一杯
  • チューニングしたいが、何をすれば良いか分からない

題材

データサイエンティスト協会が公開している模擬問題PDFが公開されているので、まずそちらを参照頂くのが良いが、これから記載するSQLのみを使っても問題ない。
今回扱うのは下記の問題である。
【出典】2021年度 データサイエンティスト検定 模擬問題② データエンジニアリング力
■模擬問題5

SQLに起こしてみた

DDLとDMLを用意したので、環境さえあればこれだけで確認可能である。

-- 模擬問題からSQLを書いてデータを作成し検証する
-- ※PostgreSQLに対応

-- 【出典】2021年度 データサイエンティスト検定 模擬問題② データエンジニアリング力
-- https://www.datascientist.or.jp/dscertification/what/

-- ◆事前準備 ---------------------------------------------------

-- テーブル定義
CREATE TABLE 売上テーブル (
  販売Seq INTEGER PRIMARY KEY
  , 製品 VARCHAR(20) NOT NULL
  , 営業店 VARCHAR(20) NOT NULL
  , 販売個数 INTEGER DEFAULT 0 NOT NULL
 )
;

-- データ登録
INSERT INTO 売上テーブル
VALUES
(1,'商品A','東京',15)
,(2,'商品B','大阪',30)
,(3,'商品A','東京',27)
,(4,'商品B','東京',24)
,(5,'商品C','東京',30)
,(6,'商品B','大阪',15)
,(7,'商品B','大阪',25)
,(8,'商品A','大阪',20)
,(9,'商品C','名古屋',40)
,(10,'商品C','東京',30)
,(11,'商品B','名古屋',19)
,(12,'商品B','名古屋',22)
;

-- 全データ確認用のSQL
SELECT * FROM 売上テーブル
ORDER BY 販売Seq -- 主キー順にソート
;

-- ◆本題 ---------------------------------------------------

-- 【掲載されているSQL】
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;

-- 模擬問題では、上記SQLの実行結果が何件になるか?を問われている。
-- ※正解は出典元の解答を参照するか、実際に実行してみる。

環境はどうすれば?

PostgreSQLで作成したのだが、環境がなくても大丈夫
WEBブラウザで実行可能なDB Fiddleを使えば確認可能だ。
※使い方はご自身で確認して頂きたい。

チューニングの観点

この模擬問題は、条件に該当するデータの件数を数えるSQLである。
では、条件が変わってしまわないように、且つ同じ結果早く取得するようにSQLを変えてみる
よって、変えるのは抽出条件の書き方である。

問題文のSQL実行計画

下記の『cost』を見ると、問題文のSQLでは初期コスト/トータルコストともに31.8程度である。
この値を基準に検証する。

-- ※再掲【掲載されているSQL】
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;

QUERY PLAN
Aggregate (cost=31.83..31.84 rows=1 width=8)
-> Seq Scan on "売上テーブル" (cost=0.00..31.82 rows=1 width=0)
Filter: (("販売個数" >= 20) AND ("販売個数" < 30) AND (("製品")::text = '商品B'::text) AND ((("営業店")::text = '大阪'::text) OR (("営業店")::text = '名古屋'::text)))

条件の確認

そもそも条件は何だ? ということで、読み解くと下記のような条件である。

  • 製品が『商品B
  • 販売個数が『20個』以上『30個』未満
  • 営業店が『大阪』もしくは『名古屋

では、条件が分かったので1つずつチューニングを試してみよう。

条件1:製品が『商品B』

ここは特に何もすることはないと考えられる。

製品 = '商品B'

条件2:販売個数が『20個』以上『30個』未満

問題文では、販売個数の条件に『20個以上』と『30個未満』の2つの条件が組合されている

販売個数 >= 20 AND 販売個数 < 30

つまり『20個以上』という判定がされ、更に『30個未満』という判定がされるイメージである。
では、これを変更してみよう

販売個数 BETWEEN 20 AND 29

BETWEENを使い『20個から29個まで』とした。
条件としては変わっていない。30個未満は29個以下であるからだ。
そして2つだった条件が1つにまとめられている。

実行計画の比較

では、変更したSQLとコストを見てみよう。

-- 販売個数の条件を[>= AND <]から[BETWEEN]に変更
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND 販売個数 BETWEEN 20 AND 29 -- ←ここが変更箇所
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;

QUERY PLAN
Aggregate (cost=31.83..31.84 rows=1 width=8)
-> Seq Scan on "売上テーブル" (cost=0.00..31.82 rows=1 width=0)
Filter: (("販売個数" >= 20) AND ("販売個数" <= 29) AND (("製品")::text = '商品B'::text) AND ((("営業店")::text = '大阪'::text) OR (("営業店")::text = '名古屋'::text)))

ほとんど変わってない
今回はデータ件数が12件と少ないので、差が感じられないのかもしれない。
件数が多かったり、他に影響する条件が絡むと差が出るのではないかとは思う。
よって、「確実にどちらが早い」とは言い切れない

条件3:営業店が『大阪』もしくは『名古屋』

問題文では、営業店の条件にORを使い『大阪』もしくは『名古屋』どちらかに該当することとしている。

営業店 = '大阪' OR 営業店 = '名古屋'

では、これを変更してみよう

営業店 IN ('大阪','名古屋')

ここではINを使っているが、条件としては変わっていない。
要はカッコの中のいずれかに当てはまるものであるからだ。

実行計画の比較

では、これも変更したSQLとコストを見てみよう。
なお、条件1は元に戻してあるので純粋に条件2だけの変更である。

-- 営業店の条件を[OR]から[IN]に変更
SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND 営業店 IN ('大阪','名古屋') -- ←ここが変更箇所
;

QUERY PLAN
Aggregate (cost=29.40..29.41 rows=1 width=8)
-> Seq Scan on "売上テーブル" (cost=0.00..29.40 rows=1 width=0)
Filter: (("販売個数" >= 20) AND ("販売個数" < 30) AND (("営業店")::text = ANY ('{大阪,名古屋}'::text[])) AND (("製品")::text = '商品B'::text))

ちょっと早くなった!
31.8 → 29.4
ちょっとだけなのは、件数が12件しかないからだと思いたいところである(汗)

条件2と条件3の両方を変更

先程までの変更を両方組み合わせたらどうなるか?
やってみよう。

SELECT COUNT(*) FROM 売上テーブル
WHERE 製品 = '商品B'
AND 販売個数 BETWEEN 20 AND 29 -- ←ここが変更箇所
AND 営業店 IN ('大阪','名古屋') -- ←ここが変更箇所
;

QUERY PLAN
Aggregate (cost=29.40..29.41 rows=1 width=8)
-> Seq Scan on "売上テーブル" (cost=0.00..29.40 rows=1 width=0)
Filter: (("販売個数" >= 20) AND ("販売個数" <= 29) AND (("営業店")::text = ANY ('{大阪,名古屋}'::text[])) AND (("製品")::text = '商品B'::text))

条件3の時と同じである。
条件2で変わらなかったのだから、当然ではあるが。
組合せ効果で変わることを期待したのだが、期待外れということでw

条件以外は変えるところ無いの?

そもそも単純なSQLなので、これ以上は思いつかないのだが。
唯一あるとすれば、COUNTだろうか?

SELECT COUNT(*) FROM 売上テーブル

このように 『*』 を『1』に変えると早くなるとかならないとか論争を見たことがあるので、試しに変えてみる。

SELECT COUNT(1) FROM 売上テーブル

実行計画の比較

では、これもコストを見てみよう。
まずは条件を変更していないバージョンである。

SELECT COUNT(1) FROM 売上テーブル
WHERE 製品 = '商品B'
AND (販売個数 >= 20 AND 販売個数 < 30)
AND (営業店 = '大阪' OR 営業店 = '名古屋')
;

QUERY PLAN
Aggregate (cost=31.83..31.84 rows=1 width=8)
-> Seq Scan on "売上テーブル" (cost=0.00..31.82 rows=1 width=0)
Filter: (("販売個数" >= 20) AND ("販売個数" < 30) AND (("製品")::text = '商品B'::text) AND ((("営業店")::text = '大阪'::text) OR (("営業店")::text = '名古屋'::text)))

変わらない(31.8)
続けて、条件2と条件3を変更したバージョンである。

SELECT COUNT(1) FROM 売上テーブル
WHERE 製品 = '商品B'
AND 販売個数 BETWEEN 20 AND 29
AND 営業店 IN ('大阪','名古屋')
;

QUERY PLAN
Aggregate (cost=29.40..29.41 rows=1 width=8)
-> Seq Scan on "売上テーブル" (cost=0.00..29.40 rows=1 width=0)
Filter: (("販売個数" >= 20) AND ("販売個数" <= 29) AND (("営業店")::text = ANY ('{大阪,名古屋}'::text[])) AND (("製品")::text = '商品B'::text))

まあ、条件だけを変えた時と同じ(29.4)

ちなみに 『*』 と『1』の違いはNULLの判断ロジックの有無だそうだ。
今回のデータでは、あまり関係なさそうな感じか?

最後に

ということで、今回は簡単なチューニングについて考えてみた。
この企画をやろうと思ったきっかけは、DS検定の勉強をしていた時に
「自分ならこう書くかな」などと考え、検証したくなったのである。

私がパッと思いつく限りでは、今回の程度となる。
それでも、やってみようとなること自体が面白いと私は思う。

それでは、今回は以上にて締めようと思う。
ここまで読んで頂き、多謝!!m(_ _)m

Agent Grow Tech Notes
Agent Grow Tech Notes

Discussion