DO’s&DONT’s #2: 絶対にやらなければいけないこと – データ型を一致させる


神谷 雅紀
SQL Server Eascalation Engineer

クエリを書く場合、比較を行うデータのデータ型は一致させておくことが必要です。

 

なぜ?

データ型が一致していない場合、必ず、どちらかのデータがもう片方のデータのデータ型に変換された後に比較が行われます。つまり、SQL Server は、データ型変換という余分な処理を行わなければならなくなります。また、それだけではなく、データ型を一致させるために、本来読み取る必要のないデータを読み取らなければならなくなることもあります。

 

どのような影響があるのか?

クエリのパフォーマンス悪化です。比較するデータのデータ型が一致していない場合、データ型の優先順位に従って、優先順位の低いデータ型のデータが、優先順位の高いデータ型に変換されます。

 

データ型の優先順位 (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms190309.aspx

 

例えば、以下のクエリを考えます。

 

CREATE TABLE TableA (C1 varchar(10))
GO
SELECT * FROM TableA WHERE C1=N'XXX'
GO

 

SELECT の WHERE 句に指定されている C1 は VARCHAR(10) ですので非 Unicode 型ですが、比較対象の N’XXX’は Unicode 型です。

この場合、何が発生するのでしょう?実行プランを見てみます。

 

|--Table Scan(OBJECT:([testdb1].[dbo].[TableA]), WHERE:(CONVERT_IMPLICIT(nvarchar(10),[testdb1].[dbo].[TableA].[C1],0)=[@1]))

 

CONVERT_IMPLICIT() が入っています。これは、その名のとおり、暗黙のデータ型変換です。データ型が異なっているため、データ型を一致させる処理が必要になります。

もし、データ型が一致していたら、どうでしょうか?

 

SELECT * FROM TableA WHERE C1='XXX'

 

当然、データ型変換は不要になります。

 

|--Table Scan(OBJECT:([testdb1].[dbo].[TableA]), WHERE:([testdb1].[dbo].[TableA].[C1]=[@1]))

 

これは単純な例ですが、実際には、データ型を変換するためには、変換対象のデータを一旦読み取り、データ型を変換し、データを比較という処理が必要になるため、インデックスによる絞込みを効率的に行うことができず、著しくパフォーマンスが悪化することがあります。

 

どのように対応するか?

データ型を一致させます。

 

陥りがちなパターン 1 – decimal/numeric 定数の表記

日本では decimal/numeric が好まれる傾向がありますが、decimal/numeric 定数には、必ず小数点が必要であることは、見落とされがちです。

以下の例では、列 C1 は decimal(10,0) と定義されています。これに対して、SELECT の WHERE 句では定数 1 が指定されています。

 

CREATE TABLE TableB (C1 decimal(10,0))
GO
SELECT * FROM TableB WHERE C1=1
GO

 

列 C1 と定数 1 のデータ型は一致しているでしょうか?実行プランを見てみます。

 

|--Table Scan(OBJECT:([testdb1].[dbo].[TableB]), WHERE:([testdb1].[dbo].[TableB].[C1]=CONVERT_IMPLICIT(decimal(10,0),[@1],0)))

 

CONVERT_IMPLICIT() によって、定数 1 は decimal(10,0) に変換されています。つまり、データ型は一致していません。では、どう記述するべきでしょうか?

答えは、1.0 もしくは 1. です。

 

SELECT * FROM TableB WHERE C1=1.0

 |--Table Scan(OBJECT:([testdb1].[dbo].[TableB]), WHERE:([testdb1].[dbo].[TableB].[C1]=[@1]))

 

Books Online では、decimal 定数は次のように定義されています。

 

decimal 型定数は、小数点を含む数値文字列で表し、引用符では囲みません。

 

定数 (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms179899.aspx

 

陥りがちなパターン 2 – JDBC での接続文字列の指定

JDBC を用いて SQL Server に接続する場合、Prepared Statement の文字列パラメータを Unicode とするか非 Unicode とするかは、接続文字列パラメータsendStringParametersAsUnicode で指定します。このパラメータが適切に設定されていない場合、データ型の不一致が発生します。

 

接続プロパティの設定
http://technet.microsoft.com/ja-jp/library/ms378988.aspx

 

陥りがちなパターン 3 – 文字列定数の N プレフィックス忘れ

NCHAR, NVARCHAR などの Unicode 型との比較を行う文字列定数には、N’ABC’ のように、N プレフィックスが必要です。

 

以上のように、データ型が一致しているかどうかは、クエリの実行プランを見ることで確認できます。実行プランの確認方法は、以下の Books Online トピックに記載されています。

SQL Trace を用いて確認する場合

Showplan All イベント クラス
http://msdn.microsoft.com/ja-jp/library/ms191283.aspx

Showplan Text イベント クラス
http://msdn.microsoft.com/ja-jp/library/ms191314.aspx

Showplan XML イベント クラス
http://msdn.microsoft.com/ja-jp/library/ms189318.aspx

Management Studio 等のクエリ実行ツールからクエリを実行して確認する場合

グラフィカル実行プランの表示 (SQL Server Management Studio)
http://msdn.microsoft.com/ja-jp/library/ms178071.aspx

SET SHOWPLAN_ALL (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms187735.aspx

SET SHOWPLAN_TEXT (Transact-SQL)
http://msdn.microsoft.com/ja-jp/library/ms176058.aspx

Comments (0)

Skip to main content