バインド変数の利用
where句に指定されている条件をパラメータ変数にする事で、OracleのSQL解析が少なくなりパフォーマンスの向上につながります。(同時に、共有プールにキャッシュされたSQLを利用する為に、SQLの意味が同じでもスペースの個数、改行位置、大文字、小文字などを開発者間でルール化し記述するようにするといいでしょう)
具体的には、OracleCommandクラスのParameters.addメソッドでパラメータをセットしSQLを実行します。
Using cmd As New OracleCommand("select ENAME from EMPTEST where EMPNO = :P_EMPNO", cn) '名前指定パラメータにする cmd.BindByName = True 'OracleCommandにパラメータを追加 cmd.Parameters.Add("P_EMPNO", OracleDbType.Int32, ParameterDirection.Input) cmd.Parameters.Item("P_EMPNO").Value = 12345 End Using
サンプル
EMPTEST表(EMPと同内容で件数100万件)をEMPNOを条件にしてDataReaderでデータを取得するのを指定回数繰り返します。
Imports Oracle.DataAccess.Client Public Class frmParameterBind Const ConnectionString As String = "DATA SOURCE=ORCL;User ID=scott;password=tiger;" '''''' バインド無し ''' ''' ''' '''Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click Dim rd As OracleDataReader = Nothing Dim sw As New System.Diagnostics.Stopwatch() Dim i As Integer Try Using cn As New OracleConnection(ConnectionString) cn.Open() Using cmd As New OracleCommand("", cn) 'ストップウォッチを開始する sw.Start() '指定回数ループする For i = 0 To CInt(TextLoopCount.Text) - 1 cmd.CommandText = "select ENAME from EMPTEST " & _ "where EMPNO = " & (i + 1).ToString rd = cmd.ExecuteReader rd.Close() Next 'ストップウォッチを止める sw.Stop() '結果を表示する TextTime1.Text = sw.Elapsed.ToString End Using cn.Close() End Using Catch ex As Exception MessageBox.Show(ex.Message) Finally If Not IsNothing(rd) Then If Not rd.IsClosed Then rd.Close() End If rd.Dispose() End If End Try End Sub ''' ''' パラメータバインド ''' ''' ''' '''Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click Dim rd As OracleDataReader = Nothing Dim sw As New System.Diagnostics.Stopwatch() Dim i As Integer Const cmdSelect As String = "select ENAME from EMPTEST where EMPNO = :P_EMPNO" Try Using cn As New OracleConnection(ConnectionString) cn.Open() Using cmd As New OracleCommand(cmdSelect, cn) 'ストップウォッチを開始する sw.Start() '名前指定パラメータ指定 cmd.BindByName = True 'OracleCommandにパラメータを追加 cmd.Parameters.Add("P_EMPNO", OracleDbType.Int32, ParameterDirection.Input) '指定回数ループする For i = 0 To CInt(TextLoopCount.Text) - 1 cmd.Parameters.Item("P_EMPNO").Value = i + 1 rd = cmd.ExecuteReader rd.Close() Next 'ストップウォッチを止める sw.Stop() '結果を表示する TextTime2.Text = sw.Elapsed.ToString End Using cn.Close() End Using Catch ex As Exception MessageBox.Show(ex.Message) Finally If Not IsNothing(rd) Then If Not rd.IsClosed Then rd.Close() End If rd.Dispose() End If End Try End Sub End Class
実行結果
実行時間が短縮されているのがわかります。
10,000回ループしました。
バインド変数無しの方は、約27秒掛かっています。一方バインド変数を利用した方は約18秒となりました。
20,000回ループしました。
バインド変数無しの方は、約62秒掛かっています。一方バインド変数を利用した方は約37秒となりました。
履歴
- 2011/07/17
- 公開