非ユニコードデータ型をユニコードデータ型に変換する際のベスト・プラクティス

マイクロソフトの植田です。ご無沙汰しております。

少し間が開いてしまいましたが、引き続きSQL Serverのベストプラクティスに関して情報発信していきたいと思います。

今回はSQL Serverにおける、非ユニコードデータ型をユニコードデータ型に変換する際のベスト・プラクティスをご紹介させて頂きます。具体的には、char, varchar, textなどのデータ型をnchar, nvarcharに変換するための最も簡単、かつ、高速な方法や、それらに関する注意点をまとめたいと思います。

本内容は米国のSQL Customer Advisory Teamのサイトにて公開されております。

注）本内容に関する詳細については以下のWebサイトに掲載されているホワイトペーパー（Best Practice for Migrating Non-Unicode Data Types to Unicode）をご参照ください。

本ドキュメントは以下の方を対象としております。

l 開発者、テストエンジニア、データベース・アドミニストレータ

l データベース、および、Microsoft SQL Serverについて基本的な知識をお持ちの方

l Microsoft SQL Serverの照合順序について基本的な知識をお持ちの方
SQL Server 2005の照合順序オプションおよびインターナショナルサポート：https://technet.microsoft.com/ja-jp/library/ms143503.aspx

今回テストに使用したデータベースに含まれるテーブル、および、各テーブルのサイズは以下の通りでした（データベース全体の合計は約30GB）。

テーブル名	行数	テーブルサイズ	インデックスサイズ
REGION	5	8 KB	8 KB
NATION	25	8 KB	24 KB
PART	3,000,000	427 MB	800 KB
SUPPLIER	149,984	23 MB	1.8 MB
PARTSUPP	12,000,000	1.8 GB	132 MB
CUSTOMER	2,249,984	380 MB	728 KB
ORDERS	22,500,000	2.9 GB	628 MB
LINEITEM	89,987,373	14 GB	4.5 GB

テーブル1：テーブルサイズ

今回のデータ型の移行では以下の5通りの方法をテストし、結果を比較しました。

1. ALTER TABLE　T-SQL文を用いて、テーブルのデータ型をインプレースで変更する方法
(例)テーブルスキーマがt1 (c1 char(10),c2 varchar(20),…)の場合
alter table t1 alter column c1 nchar(10);
alter table t1 alter column c2 nvarchar(20);…

2. BCPを用いてデータをフラットファイルに落とし、新しいテーブルに一括挿入する方法
(例)
a) フラットファイルへのエクスポート
bcp testdb.dbo.t1 out E:\t1.dat –w –Slocalhost –T
b) 元のテーブルの削除
drop table t1
c) テーブルをユニコードデータ型で再作成
create table t1 (c1 nchar (10), c2 nvarchar(20),…)
d) テーブルへの一括挿入
bulk insert t1 from ’E:\t1.dat’ with (DATAFILETYPE=’widechar’)

3. INSERT SELECT　T-SQL文を用いて、一時テーブルにデータを格納し、テーブルを置き換える方法
(例)
a) 一時テーブルの作成
create table stg_t1 (c1 nchar (10), c2 nvarchar(20),…)
b) 一時テーブルへのデータ挿入
insert into stg_t1 select * from t1
c) 元のテーブルを削除
drop table t1
d) 一時テーブルの名前を変更
sp_rename ’stg_t1’, ’t1’

4. SELECT INTO　T-SQL文を用いて、一時テーブルにデータを格納し、テーブルを置き換える方法
(例)
a) 一時テーブルへのデータ挿入
select cast(c1 as nchar(10)) as c1, cast(c2 as nvarchar(20)) as c2,… into stg_t1 from t1
b) 元のテーブルを削除
drop table t1
c) 一時テーブルの名前を変更
sp_rename ’stg_t1’, ’t1’

5. SQL Server Management Studio(SSMS)からGUIを用いてテーブルのデータ型を変更する方法
(例)
a) SSMSのオブジェクト・エクスプローラー上でデータベースのツリーを開く
b) 変更対象のテーブルのアイコン上で右クリックし、「デザイン」をクリック
c) テーブル・デザイナーにおいて非ユニコード文字列型のカラムのデータ型をユニコードデータ型に置き換える
d) テーブル・デザイナーを閉じる

上記のホワイトペーパーでは、経過時間、リソース使用状況、照合順序の変更、および、可用性について議論していますが、ここではそれぞれの移行方法の経過時間、ディスク使用量、および、特徴、そして結論について要約させていただきます。

移行にかかる時間の比較

以下のテーブルはそれぞれの手順と、それに要した時間を表しています。

手順　 方法	データ変換、または、移動に要した時間（分）	オブジェクト（Index等）再作成にかかった時間(分)	合計（分）
ALTER TABLE	137	61	198
フラットファイル	122	41	163
INSERT SELECT	18	43	61
SELECT INTO	18	41	59
SSMS	17	38	55

テーブル2：移行にかかる時間

上記の結果より、最も早くに移行を完了させることができた手順はSSMSを用いる方法であることがわかります。ただし、次に続いている、INSERT SELECT、SELECT INTOを用いる方法との差はわずかでした。プロファイラーを用いて、SSMSを使用した時にどのようなリクエストが内部で発行されているかを調べたところ、内部処理はINSERT SELECTと同じであることがわかりました。最も大きく異なる点は、SSMSを用いた場合は移行対象のテーブルにロックがかかり、移行が完了するまで（インデックスが再作成されるまで）テーブルにアクセスができなくなる点でした。

またフラットファイルを用いた移行では、データのインポート/エクスポート処理をCPU数分の並列スレッドで行うことにより処理を高速化できます。上記のデータは1プロセスでの処理時間ですが、8CPUのマシンで8プロセスの並列処理を行うことにより、約37%パフォーマンスが向上することが確認できました。さらに処理を高速化させる方法としては、インポート対象のテーブルをパーティション分割する方法が考えられますが、今回のテストではテーブルのパーティション化は行いませんでした。

移行にかかる時間の比較

以下のテーブルはそれぞれ移行方法について、ディスク使用量を表しています。「その他」に含まれる領域として、フラットファイルの領域、および、TempDBの領域があります。

使用用途 方法	Data file (GB)	Log file (GB)	その他 (GB)	合計（GB）
ALTER TABLE	60	81	19	159
フラットファイル	48	57	67	172
INSERT SELECT	73	57	19	149
SELECT INTO	73	57	19	149
SSMS	67	57	0	127

テーブル3：ディスク使用量

上記の結果より、最もディスク容量を使用する移行方法はフラットファイルを利用する方法であることがわかりました。ただし、フラットファイルは、データベースが置かれているストレージとは別のストレージに配置することもできるため、ほかの方法に比べて自由度が高く、データファイルの容量に注目すると、最もディスク使用量が少ない点が特徴です。INSERT SELECT、SELECT INTOに関しては一時テーブルのための領域を確保しているため、ディスク使用量が大きくなっています。ただし、これら2つの方法は同時に、移行前のテーブルを保持することができるため、高い可用性を求められるケースでは有効です。SSMSではインデックス作成の際にTempDBを使用できないため、元のテーブルが存在するファイルグループに十分な容量があることを確認しておく必要があります。

移行手法の特徴

ALTER TABLE

この手法は移行に最も時間がかかる方法でした。また、ALTER TABLEを行うためには、テーブルに作成されたインデックスやトリガーなどのオブジェクトを削除しなければならず、テーブル変更後に再作成するという作業が必要になるため、手順が複雑になる恐れがあります。また一時テーブルのためのディスク容量は必要ありませんが、その分元のテーブルが大きくなってしまう点も考慮する必要があります。加えて、ログのサイズも肥大化するため、十分なディスク容量が必要となります。

フラットファイル

この手法は2番目に時間がかかる方法でした。ただし、移行元のテーブルが複数のファイルにエクスポート可能である場合、並列処理を行うことによって移行時間を短縮できます。移行に際してデータのクレンジングなどの処理が必要であるシナリオではこの方法は効果的です。だたし、フラットファイルのためのディスク領域を確保する必要があります。

INSERT SELECT

この手法は3番目に高速な方法でデータ移行を実現します。SELECT INTOとのもっとも大きな違いは、あらかじめテーブルを作成した上で移行を行えるため、パーティション化されたテーブルにも適応可能です。また旧データを保持しながら、データ移行を行えるので高い可用性を提供します。ただし、一時テーブルのためのディスク領域を確保する必要があります。

SELECT INTO

この手法は2番目に高速な方法でデータ移行を実現できます。なお、データ挿入時のログ書き込みが最も少ない手法でした（テーブル3のログファイルのサイズはインデックス作成時に膨張）。ただし、この手法ではテーブルをあらかじめ用意しないため、以下の2点の制限があります。

1． テーブルはデフォルト・ファイルグループ上に作成される

2． データ挿入先のテーブルをパーティション化できない

デフォルト・ファイルグループはALTER DATABASEコマンドで変更可能ですが、SELECT INTOではパーティションスキームを指定することができないため、2に関する制約はSELECT　INTOを利用不可にする可能性があります。可用性、および、ディスク使用量についてはINSERT SELECTと同様です。

SQL Server Management Studio

最も簡単に、最も早くデータ移行を行うことができます。ただし、移行中はテーブルが排他ロックされるため、その間テーブルはアクセスできなくなります。また、マニュアルでのオペレーションのため、人為的なミスにも配慮する必要があります。内部的にはINSERT SELECTと同様の処理が行われます。インデックスの作成にはTempDBが使用できないため、ファイルグループにインデックス作成のための十分な領域が必要です。

結論

非ユニコードデータ型のデータをユニコードデータ型に移行する際のベストプラクティスについて検証を行いました。その結果以下のような指針を得ました。

l データ移行の際はメンテナンスの時間を確保し、移行前にデータのバックアップを採取しておくことを推奨します。

l データ移行に関して、データファイル、ログファイル、TempDBのためのディスク容量が十分にあることをご確認ください。

l 移行するテーブルがパーティション分割されておらず、可用性を考慮する必要があり、最も短い時間で移行を完了させたい場合はSELECT INTOを使用してデータ移行することをお勧めします。

l 可用性を考慮する必要がなく、作業手順をスクリプト化する必要がない場合は、SQL Server Management Studioを使用することをお勧めします。

l 移行先のテーブルをパーティション分割する必要があり、ファイルグループに自由度を持たせたい場合はINSERT SELECTを使用してデータ移行することをお勧めします。

l データを一旦フラットファイルに落として移行を行いたい場合、可能であればフラットファイルをCPU数分、分割して並列処理を行うことをお勧めします。

コミュニティにおけるマイクロソフト社員による発言やコメントは、マイクロソフトの正式な見解またはコメントではありません。