【SQL Server】性能改善で感じた重要なこと！

業務で作成したバッチ処理の処理性能が悪く、改善活動を行いました。次同じ様な処理を作成する場合は、次こう進めた方がいいと感じたことや重要なことについて発信します。

本記事では、SQL Serverのインデックスについて言及していますが、概要はこちらの記事をください！

また、言及していく内容は大量データ環境下での処理を要する場合についてです。データ数が多くない場合は無駄にインデックスを作成したりクエリを複雑にしても、かえって悪影響になってしまいます。前提として、非機能要件に合わせて、設計していくことが重要になります。

バッチ概要

構成

FW：Spring Batch 、DB：SQL Server

処理概要

2つのバッチ処理を作成しましたが、以下のようなバッチ処理になります。いずれも数百万〜数千万のテーブルを結合しながら処理することが前提となっています。

① 様々なテーブルを結合（7テーブル）しながらデータ取得し、集計・加工して別のテーブルへインサートする処理
② 大量データ（数十万〜数百万）を取得し、外部サービスへのリクエストおよび完了後にテーブルをデータ更新する処理

前提、仮定

テストデータとして、以下の様なテーブル・データがある前提として解説していきます。

-- カテゴリー
CREATE TABLE blog.category (
    category_id INT NOT NULL
    , category_name NVARCHAR(20) NOT NULL
    , PRIMARY KEY(category_id)
)

-- 店舗
CREATE TABLE blog.shop (
    shop_id INT NOT NULL
    , shop_name NVARCHAR(20) NOT NULL
    , category_id INT NOT NULL
    , status VARCHAR(20) NOT NULL
    , start_date DATE NOT NULL
    , end_date DATE NOT NULL
    , PRIMARY KEY(shop_id)
    , FOREIGN KEY(category_id) REFERENCES blog.category(category_id) 
)

-- ユーザ
CREATE TABLE blog.my_user (
    user_id INT NOT NULL
    , first_name NVARCHAR(20) NOT NULL
    , last_name NVARCHAR(20) NOT NULL
    , age INT NOT NULL
    , status VARCHAR(20) NOT NULL
    , PRIMARY KEY(user_id)
)

-- 取引
CREATE TABLE blog.sales (
    sales_id INT NOT NULL
    , amount INT NOT NULL
    , transaction_date DATE NOT NULL
    , status VARCHAR(20) NOT NULL
    , cancel_flg BIT NOT NULL
    , shop_id INT NOT NULL
    , user_id INT NOT NULL 
    , PRIMARY KEY(sales_id)
    , FOREIGN KEY(user_id) REFERENCES blog.my_user(user_id) 
    , FOREIGN KEY(shop_id) REFERENCES blog.shop(shop_id) 
)

・店舗（shop）データ
数万データあり、かつステータスは99%以上が「有効」である。
※ ステータス：有効、一時停止、退会の3種

・ユーザ（my_user）データ
数万データあり、かつステータスは99%以上が「有効」である。
※ ステータス：有効、一時停止、退会の3種

・取引（sales）データ
数十万データあり、取引日は「2022-01-01」〜「2022-12-31」の間で、かつステータスは99%以上が「成功」である。
※ ステータス：成功、失敗の2種

実際にやったこと

設計、クエリ見直し

ざっくりとやったことは以下の様なことです！無駄な結合条件の削除は表題の通りですが、その他解説していきます。

• 無駄な結合条件の削除
• サブクエリで絞り込んでから結合
• 必要なカラムのみセレクト
• 1度に全ての結合を行わない
• 全てのデータを一度に処理しきらず、細かな単位で処理

SELECT *
FROM blog.sales 
    LEFT OUTER JOIN 
        blog.my_user 
        on sales.user_id = my_user.user_id
WHERE
    sales.transaction_date = '2022-01-01'
    and my_user.age >= 30

SELECT *
FROM (
        SELECT * 
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        blog.my_user 
        on sales.user_id = my_user.user_id
WHERE 
    my_user.age >=30

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
WHERE 
    my_user.age >=30

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
    , shop.shop_name
    , category.category_name
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
    LEFT OUTER JOIN -- 店舗データを追加
        (
            SELECT shop_id,shop_name,category_id
                FROM blog.shop 
        ) shop
        on sales.shop_id = shop.shop_id
    LEFT OUTER JOIN -- カテゴリーデータを追加
        blog.category 
        on shop.category_id = category.category_id
WHERE 
    my_user.age >=30

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
    , sales.shop_id -- 店舗IDを追加
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
WHERE 
    my_user.age >=30

SELECT 
    shop.shop_id
    , shop.shop_name
    , category.category_name
FROM (
        SELECT * 
            FROM blog.shop
            WHERE shop_id IN (<作成した店舗IDリスト>)
    ) shop
    LEFT OUTER JOIN
        blog.category 
        ON shop.category_id = category.category_id

小さい粒度でクエリ測定

設計・クエリを見直しつつ、クエリの最もネストが深い部分や結合の元になるデータのクエリを測定します。

SELECT 
    sales.sales_id
    , sales.amount
    , my_user.first_name
    , shop.shop_name
    , category.category_name
FROM (
        SELECT sales_id,amount,user_id,shop_id
            FROM blog.sales
            WHERE transaction_date = '2022-01-01'
    ) sales
    LEFT OUTER JOIN 
        (
            SELECT user_id,first_name,age
                FROM blog.my_user
        ) my_user
        on sales.user_id = my_user.user_id
    LEFT OUTER JOIN -- 店舗データを追加
        (
            SELECT shop_id,shop_name,category_id
                FROM blog.shop 
        ) shop
        on sales.shop_id = shop.shop_id
    LEFT OUTER JOIN -- カテゴリーデータを追加
        blog.category 
        on shop.category_id = category.category_id
WHERE 
    my_user.age >=30

上記の様なクエリを検索する場合は、まずは取引のクエリを測定します。

SELECT 
    sales_id
    , amount
    , user_id
    , shop_id
    FROM blog.sales
    WHERE transaction_date = '2022-01-01'

このクエリが遅い場合は、その後結合しても絶対に遅くなるため、インデックスや検索条件や仕様を調整しつつ性能改善していく必要があります。インデックスを作成するとしたら、以下の様な付加列インデックスを作成すると最速な検索になることが予想されます。

CREATE NONCLUSTERED INDEX IX_sales_01 
    ON blog.sales(transaction_date) 
    INCLUDE (sales_id,amount,user_id,shop_id)

その他サブクエリについてもサブクエリ単体で性能改善した後に、結合すると高速な検索になります。

インデックスチューニング

上記で例で出した様に付加列インデックス・クラスターインデックス・非クラスターインデックスを駆使して高速な検索を目指します。インデックスの詳細については、SQL Serverのインデックスについての記事を見てみてください！

大事なこと

設計段階から性能を意識すること

インデックスで対応できることには限りがあるため、やはり設計段階から性能を考慮する必要があります。どんな単位でデータ取得するか、あるいは更新・挿入するか？どういった結合が最適か？それらを総合的に判断して、プログラムもそうですしクエリも作成することが重要だと感じました。数万レコードや最大でも数十万レコードの場合はそこまで気にしなくても問題ないことはありますが、数百万・数千万以上のデータを扱う場合は設計はかなり重要です。

設計段階から性能測定すること

とはいえ性能の勘所がない場合は設計段階での最適な設計は難しいかもしれないですし、勘所があっても実際に測ってみると想定通りにならなかったりします。なので早い段階から性能試験環境を用意して、測定することが重要だと感じています。特に、結合元のサブクエリやネストの深い最小単位のクエリで性能が出ない場合は、その後に結合してもかなり遅いことが予想されます。小さな単位からクエリを測って徐々に結合して問題ないかを検証していけば、勘所がなくても対処できるはずです。

注意点

ORM経由でのクエリ発行は最適な実行計画になるとは限らない

業務では、jOOQ というORMライブラリを使用してクエリ発行しています。ORM経由だと、DBクライアントツールからクエリ発行する際と異なる実行計画になることがあります。ツールで検索した際に高速検索できているのに、プログラムからクエリを実行して遅くなる場合は、実行計画を比較することが必要です。
私の場合は最終的には、文字列結合によりクエリを組み立てて、ツールから実行した際と同一の実行計画にする様に修正しました。プリペアドステートメントが効かなくセキュリティ的には良くないですが、入力値などを用いた検索ではないため許容しました。

インデックスは最適なものを最小限に

インデックスはデータ検索を高速にする反面、データ更新を行う場合はインデックスページの更新も必要になりコストが高くなるため、以下の様な注意が必要です。

• 数を最低限にする
• ON句に指定するカラムは最小限にする
• INCLUDE句に指定するカラムは最小限にする

性能環境のデータ分布は要考慮

大量データを要する性能試験環境を用意するには、BCPコマンドを用いたデータ一括インポートが有効です！使用方法については以下の記事を読んでみてください！

データを作る上での注意点としては、検索条件になりそうな箇所やインデックスのON句になりそうなカラムのデータはなるべくバラけさせてリアリティのあるデータを作成することです。インデックスを貼っても全てのデータで同じ値が入っているとインデックスが効かず性能検証にならない場合があります。

まとめ

とにかく設計段階から性能を意識した設計にすること、また実際に測ってみること が重要だと思っています。

インデックスで改善しきれることには限界がありますし、前述の通りインデックスを作成しすぎるのも悪影響があります。まずは、設計段階からどれくらいのデータ数の中からどのくらいのデータ量を扱うのか？どんな単位で処理すべきか？性能目標はどの程度か？といったことを考慮していくことが重要です。諸々のテストが完了した後に性能がダメで作成し直しになると、テストもやり直しになるため手戻りが多くなります。

実際に測ってみることも大事なアクションだと思います。測ってみると思い描いていた実行計画と異なり全く性能が出ないこともあるので早めに測ることが重要です。性能について勘所がないうちは、実際にどんどん動かして測っていくことで勘所を掴みながら改善できるようになっていきます。