VB.NET2010年1月20日ろっきー
アプリケーションのパフォーマンスでボトルネックになりやすい要因の一つに、データベース（DB）へのアクセスがあります。
今回はDBへのアクセス（特にSQL文の発行）に関して、バインド変数を使ったパフォーマンス向上の方法を紹介し、それぞれの方法でどれだけパフォーマンスに違いが出るのかを検証してみたいと思います。
なお、前提事項として、以下の開発環境で検証をしました。
・言語：VB.NET
・フレームワーク：.NET Framework 2.0
・DBサーバ：SQL Server 2005 Express Edition
検証を行なうに当たり大量のデータが必要なので、zpnaviでもお世話になっている、郵便番号検索 – 日本郵便より提供されている住所データを使用します。
10万件の住所データに対して、1から順番にシーケンス番号を振ります。 そのデータを1件づつ読み込んで、10万件目が終わるまでの処理時間を計測します。
●動的にSQLを生成したケース
動的にSQLを生成したケースでは、以下の様に動的にSQL文を組み立てて、その内容に対してDBにSQLを発行します。
Imports System.Data.SqlClient
Imports System.Text
sub Test()
Debug.Print(Now)
Dim SqlConn As New SqlConnection
Dim SqlCmd As SqlCommand
Dim SqlRd As SqlDataReader
Dim StSQL As String
Dim i As Long
Dim TownName As New StringBuilder
SqlConn.ConnectionString = "(DB接続文字列)"
SqlConn.Open()
SqlCmd = SqlConn.CreateCommand
For i = 1 To 100000
StSQL = "SELECT NEW_ZIP FROM T_ADDRESS WHERE SEQ = " & i
SqlCmd.CommandText = StSQL
SqlRd = SqlCmd.ExecuteReader
SqlRd.Read()
TownName.Append(SqlRd(0).ToString)
SqlRd.Close()
Next
SqlCmd.Dispose()
SqlConn.Close()
Debug.Print(Now & ":" & TownName.Length)
End Sub
●バインド変数を使用してSQLを生成したケース
Imports System.Data.SqlClient
Imports System.Text
sub Test()
Debug.Print(Now)
Dim SqlConn As New SqlConnection
Dim SqlCmd As SqlCommand
Dim SqlPrm As SqlParameter
Dim SqlRd As SqlDataReader
Dim StSQL As String
Dim i As Long
Dim TownName As New StringBuilder
SqlConn.ConnectionString = "(DB接続文字列)"
SqlConn.Open()
SqlCmd = SqlConn.CreateCommand
SqlPrm = SqlCmd.Parameters.Add("@SEQ", SqlDbType.Int)
SqlPrm.Direction = ParameterDirection.Input
StSQL = "SELECT NEW_ZIP FROM T_ADDRESS WHERE SEQ = @SEQ"
SqlCmd.CommandText = StSQL
For i = 1 To 100000
SqlPrm.Value = i
SqlRd = SqlCmd.ExecuteReader
SqlRd.Read()
TownName.Append(SqlRd(0).ToString)
SqlRd.Close()
Next
SqlCmd.Dispose()
SqlConn.Close()
Debug.Print(Now & ":" & TownName.Length)
End Sub
なお、上記のプログラムを作成するに当たり、.NET アプリケーションのパフォーマンスとスケーラビリティの向上 – 第 12 章 「ADO.NET パフォーマンスの向上」を参考にしました。
前者と後者のプログラムの違いは、各プログラムの19行目のDBに発行するSQL文が動的か、固定かの違いです。
この様な実装方法にする理由はmsdnのサイトには記載されていませんでしたが、Oracleの場合でもバインド変数を使った実装の方が、パフォーマンスがいいと言われています。
具体的な理由もありました。
Oracle Database 10gがOracle Database 10gがSQL文を受け取った場合、共有プール（メモリ領域）をチェックして、文がすでに存在しメモリに格納されているかどうかを確認します。文がメモリに存在し、Oracle Database 10gがその文を再利用できる場合、データベースは文を解析および最適化するタスクをスキップできます。バインド変数を使用すると、SQL文がメモリに格納される可能性が大幅に高まります。これによって、そのSQL文を必要とする次の操作で迅速にそのSQL 文を使用できます。
※Oralceでバインド変数を使用する為には、「Oracle.DataAccess」コンポーネントを参照追加する必要があります。
上記の内容は、値のバインドの「バインド変数が重要な理由」より抜粋
SQL Serverの場合は上記の様な具体的な理由までは記述されていませんでしたが、恐らく同様の理由だと思います。
では動的にSQLを生成したケースと、バインド変数を使用してSQLを生成したケースとではどれくらいパフォーマンスに違いが出るのか？
結果は後日報告したいと思います！
