DO’s&DONT’s #18: やった方がいいこと - .NET Framework アプリケーションでパラメータクエリを実行する場合にはパラメータのデータ型やサイズを明示的に指定する
高橋 理香
SQL Developer Support Escalation Engineer
SQL Server では、パラメータ付きのストアドプロシージャを作成して実行したり、sp_executesql を使用してパラメータ クエリを実行することができます。このパラメータ クエリを実行する際には、パラメータのデータ型やパラメータ値が必要となります。また、文字列型やバイナリ型の場合には、サイズの指定も必要です。
一方で .NET Framework Data Provider for SQL Server (以降 SqlClient) を使用するアプリケーションからストアドプロシージャやパラメータ クエリを実行する場合、SqlParameter クラスを使用してパラメータのデータ型等を設定できますが省略することもできます。これは、SqlClient が SqlParameter の Value プロパティに代入された値を基にデータ型やサイズの推論を行っているからです。
しかしながら、パラメータのデータ型やサイズを明示的に指定しなかった場合には、意図しないデータ型やサイズとなることで、クエリのパフォーマンス劣化が発生したり、エラーによって実行できないなどの問題が発生する可能性もあります。そのため、可能な限りパラメータのデータ型やサイズは明示的に記述しておいた方が、アプリケーションの堅牢性の面からもよいでしょう。
推奨事項
- パラメータのデータ型は SQL Server 側のデータ型に合わせて明示的に指定する。
- 文字列やバイナリのデータ型の場合、サイズも明示的に指定する。
- varchar(max) 等の max を使用したデータ型についてはサイズに -1 を指定する。
なぜデータ型を明示的に指定した方がいいのか？
以前に以下のブログで紹介しているように、データ型が一致していない場合にはデータ型変換を行ってから比較を行うことになります。つまり、クエリ パフォーマンスが悪化し、ひいては、アプリケーションのパフォーマンス劣化につながります。
DO's&DONT's #2: 絶対にやらなければいけないこと - データ型を一致させる
データ型変換が発生する例をご紹介しましょう。
ADO.NET では、次のようにパラメータのデータ型やサイズを指定しないパラメータ クエリを実行するコードを記述することができます。
SqlCommand command = new SqlCommand("SELECT * FROM Sales.Customer WHERE AccountNumber = @AccountNumber", connection); command.Parameters.Add(new SqlParameter("@AccountNumber", "AW00011354")); SqlDataReader reader = command.ExecuteReader(); |
この例の場合、パラメータ ＠AccountNumber のデータ型の指定がないため、渡された値である "AW00011354" を基にデータ型とサイズを推論します。.NET Framework では文字列は Unicode で扱いますので、この結果は、nvarchar(10) になります。したがって、SQL Server で実行される SQL 文は次のようになります。
| exec sp_executesql N'SELECT * FROM Sales.Customer WHERE AccountNumber = @AccountNumber', N'@AccountNumber nvarchar(10)', @AccountNumber = N'AW00011354' |
しかし、サンプル データベース AdventureWorks 上の Sales.Customer テーブルの AccountNumber 列は varchar(10) です。したがって、AccountNumber 列をいったん nvarchar (10) に変換し、その結果とパラメータ値を比較する操作が行われることになり、処理に非常に時間がかかる結果となります。
上記を次のように書き換えるだけで、ある環境では 1分以上も要していたクエリが 100ミリ秒程度で完了するようになりました。
SqlCommand command = new SqlCommand("SELECT * FROM Sales.Customer WHERE AccountNumber = @AccountNumber", connection); command.Parameters.Add(new SqlParameter("@AccountNumber", SqlDbType.VarChar)).Value = "AW00011354"; SqlDataReader reader = command.ExecuteReader(); |
データ型として SqlDbType.VarChar を指定しただけでこんなにも違うのであれば、指定した方がいいですよね。
なぜサイズを明示的に指定した方がいいのか？
サイズを明示的に指定しない場合に発生しうる問題として次の2つがあります。
- 最大サイズが使用されることで、作業テーブルを利用したクエリの処理において、テーブルの行の最大許容サイズを超え、エラー 1701 が発生する。
- 推論によって設定されたサイズが最大サイズを超えるため、SQL Server の仕様に沿わないリクエストと判断され、エラーが発生する。
順にどのような状況下で発生するかご説明しましょう。
作業テーブル利用でエラー 1701 が発生するシナリオ
先の例では、Value プロパティに設定されたデータから推論が行われ、データが 10 文字であったためにサイズに 10 が設定されました。
では、NULL 値をパラメータ値として渡した場合、このサイズはどうなるでしょう？
Value プロパティからは長さを推論できませんね。このような場合、指定されているデータ型の最大値がサイズに指定されることになります。
例えば SqlDbType.Char がデータ型として指定されている場合、そのサイズは 8000 と設定されることになります。ここで問題となるのは、SQL Server における SQL 文の処理で並び替えなどのために作業テーブルを必要とする場合です。作業テーブル自体は、他の列に加えて char(8000) の列を含めることになっても作成は成功します。しかし、実際にこの作業テーブルには、SQL Server におけるテーブル行の最大許容サイズの 8094 バイトを超えるレコードが格納されることになるため、制限によって次のエラー 1701 が発生する結果となります。
Msg 1701, Level 16, State 1, Line 1 Creating or altering table 'FakeWorkTable' failed because the minimum row size would be 16017, including 4 bytes of internal overhead. This exceeds the maximum allowable table row size of 8094 bytes |
メッセージ 1701, レベル 16, 状態 1, 行 1 4 バイトの内部オーバーヘッドを含めて、最小行サイズが 16017 になるので、テーブル 'FakeWorkTable' を作成または変更できませんでした。このサイズは、テーブル行の最大許容サイズの 8094 バイトを超えています。 |
パラメータに相当する列が char(2) であれば、あらかじめ SqlParameter の Size プロパティに 2 を設定しておくことで、作業テーブルが作成される場合でも、余分なリソースを使うことを避けることができ、かつ、エラーも回避できます。
SqlCommand command = new SqlCommand("SELECT * FROM test1 WHERE column1 = @column1", connection); SqlParameter parameter1 = new SqlParameter(); parameter1.ParameterName = "@column1"; parameter1.SqlDbType = SqlDbType.Char; parameter1.Size = 2; command.Parameters.Add(parameter1); SqlDataReader reader = command.ExecuteReader(); |
推論によって設定されたサイズが最大サイズを超えるシナリオ
非常に長い文字列をパラメータ値として使用する場合について考えてみましょう。
仮にサイズの指定を行っていないと推論が行われることになりますが、基本的にはこの推論は Value プロパティに指定された文字列の長さで決定されることになりますので、8001 バイト以上の文字列の場合には、8001 以上のサイズとして扱われることになります。
しかしながら SQL Server では、varchar(max) や nvarchar(max) などのデータ型で 2GB までのデータの格納を許容できるも���の、これらのデータ型における明示的なサイズの指定は varchar の場合には 8000 まで、nvarchar の場合には 4000 までになります。そのため、varchar の場合には 8001 といった数値は不適切なサイズということになります。
推論の結果が SQL Server では不適切なサイズであると判断された場合、SQL Server からは以下のようなエラーが返されます。
| 着信の表形式のデータ ストリーム (TDS) リモート プロシージャ コール (RPC) プロトコル ストリームが不適切です。パラメーター 24 ("パラメータ名"): データ型 0xA7 に、無効なデータ長またはメタデータ長が指定されています。 |
あらかじめ SQL Server 側のデータ型が varchar(max) などの max を使用するデータ型であった場合には、Size プロパティに -1 を指定しておくことで、この推論を避けることができ、varchar(max) のデータ型にて SQL 文が実行されるようになります。
SqlCommand command = new SqlCommand("INSERT INTO table1 VALUES (@column1, @column2)", connection); command.Parameters.Add(new SqlParameter("@column1", SqlDbType.Int)).Value = 1; command.Parameters.Add(new SqlParameter("@column2", SqlDbType.VarChar, -1)).Value = longText; command.ExecuteNonQuery(); |
参考情報
nchar および nvarchar (Transact-SQL)