InsightTechnology 旧ブログ

決勝戦のTPC-Hのチューニングとしてパーティショニングとカラムストアインデックスに加えて、実行計画で不足しているインデックスや統計情報を追加などを実施しました。ですが、22のクエリのうち以下の2つが実行に時間が掛かっていました。

1. Query9

2. Query13

それぞれ実際には以下のようなSQLが実行されます。

1. Query9

   SELECT
       nation,
       o_year,
       SUM(amount) AS sum_profit
   FROM
       (
       SELECT
           n_name AS nation,
           DATEPART(yy,o_orderdate) AS o_year,
           l_extendedprice * (1 - l_discount) - ps_supplycost * l_quantity AS amount
       FROM
           part,
           supplier,
           lineitem,
           partsupp,
           orders,
           nation
       WHERE
           s_suppkey = l_suppkey AND
           ps_suppkey = l_suppkey AND
           ps_partkey = l_partkey AND
           p_partkey = l_partkey AND
           o_orderkey = l_orderkey AND
           s_nationkey = n_nationkey AND
           p_name LIKE '%:1%'
       ) profit
   GROUP BY
       nation,
       o_year
   ORDER BY
       nation,
       o_year DESC OPTION(maxdop $maxdop)
   

2. Query13

   SELECT
       c_count,
       COUNT(*) AS custdist
   FROM
       (
       SELECT
           c_custkey,
           COUNT(o_orderkey) AS c_count
       FROM
           customer
           LEFT OUTER JOIN
           orders ON
           c_custkey = o_custkey AND
           o_comment NOT LIKE '%:1%:2%'
       GROUP BY
           c_custkey
       ) c_orders
   GROUP BY
       c_count
   ORDER BY
       custdist DESC,
       c_count DESC OPTION(maxdop $maxdop)
   

特にQuery13は決勝参加者の多くが実行時間が長いと認識しており、各々が対策を打ったクエリでした。
私はQuery9,Query13を他の20クエリと比較して、この2つだけで使用されているあるものに気が付きました。
それはLIKE句に部分一致の文字列検索を含んでいるということです。

だからと言って何の対策も思い付かずにいたときに、他の参加者と話をしているとチューニング内容もほぼ一緒なのにも関わらず自分よりもQuery13の実行が圧倒的に速い人がいることに気が付きました。
詳しく話を聞いてみるとどうやら構築時のトラブルもあり、英語版のWindows Server 2016に英語版のSQL Server 2016をインストールしたというのが唯一の違いです。
この時にあることが閃きます。

英語で文字列を検索するのと日本語で文字列を検索する速度は一緒なんだろうか?

そこでOSとSQL Serverを英語版で再インストールしてみましたが改善されませんでした。
条件は同じはずなのに何故だろう?
原因がわからずオプションを眺めていると、Japanese_CI_ASと書かれている項目を発見しました。
英語版でインストールしたにも関わらず、照合順序の設定が日本語のままになっています。
再度、SQL Serverをインストールしなおし、確認するとインストーラーのデフォルトでは照合順序がJapaneseになっています。
どうやらWindowsのロケール設定で照合順序が自動的に選択されるようです。
この為、英語環境でのデフォルトであるSQL_Latin1_General_CP1_CI_ASに選択し直してインストールしました。

照合順序の設定方法はいくつかあります。

1. SQL Serverのインストール時に指定する
   • データベースのデフォルト設定に影響
2. データベースのオプションで指定
3. カラム単位で指定

そこでインストール後にデータベースオプションで、Japanese_CI_ASとSQL_Latin1_General_CP1_CI_ASを変更してQuery13の実行速度を比較したところ、SQL_Latin1_General_CP1_CI_ASの方が4～5倍ほど処理が速いようです。

ただ疑問が残ります。
そもそも照合順序で何が変わるのか?

答えはMicrosftのサイトに記載されていました。
https://technet.microsoft.com/ja-jp/library/ms175194(v=sql.105).aspx

インストール時の選択画面にも表示されていましたが照合順序には2種類あるようです。
さらに文末には明確に「パフォーマンスが異なります」と書かれています。

1. Windows照合順序
2. SQL Server照合順序

日本語の場合はWindows照合順序となりUnicodeデータとして取り扱われるため2倍の領域が確保され、結果としてパフォーマンスが悪くなってしまうようです。
加えてUnicode以外の日本語の場合はコードページが932になるとのことなのでキャラクタセットはShift-JISということになります。
Shift-JISは2バイト目にASCIIコードが出現するという欠点があり、その判定処理はEUC-JP,UTF-8に比べて複雑になってしまいます。
これら2つの要因が英語と日本語での実行速度に影響を与えていたようです。

日本語データを格納するようなデータベースでは、カラム毎の用途に応じて照合順序を適切に設定することで恩恵を受けることができそうです。