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

Artigo
02/01/2011

神谷雅紀
SQL Server Eascalation Engineer

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

なぜ？

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

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

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

データ型の優先順位 (Transact-SQL)
https://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)
https://msdn.microsoft.com/ja-jp/library/ms179899.aspx

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

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

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