□投稿者/ コスケ (6回)-(2008/02/07(Thu) 20:34:07)
|
分類:[.NET 全般]
お世話になっております。コスケと申します。
超VB.NET初心者です...
Win2003 VB.net Oracle10gにて開発しております。
約600万件のテーブルに約40万件のCSVデータをInsert処理を
行うプログラムの開発を行っております。
現在、下記の通りバインド変数を用いてInsert処理を行っていますが、
処理時間が遅い為、バインド配列を用いて、Insert処理を何万件が
まとめて発行したいと考えております。
しかしながらバインド変数の記述の仕方がいまいちわからずここに
投稿させていただきました。
ソースを記載します。
Module TableInsert
Public Sub TblInsert()
Dim strTD As String
Dim blnFn1eof As Boolean
'===tableの列定義===
strTD = mTD_1 & mTD_2 & mTD_3 & mTD_4 & mTD_5
'SQL作成
strSQL = "INSERT INTO TD_ZAIKO (" + strTD + ") VALUES ("
strSQL = strSQL & ":HANCD,:SSCD,:COMCD,:NSTCD,"
strSQL = strSQL & ":TGNSU,:TGNTA,:TGNKI,"
strSQL = strSQL & ":TJZSU,:TJZTA,:TJZKI,"
strSQL = strSQL & ":SHISU,:SHIKO,:SHIKI,"
strSQL = strSQL & ":TNSSU,:TNSKO,:TNSKI,"
strSQL = strSQL & ":TNKSU,:TNKKO,:TNKKI,"
strSQL = strSQL & ":URISU,:URIKO,:URIKI,"
strSQL = strSQL & ":ZGNSU,:ZGNKO,:ZGNKI,"
strSQL = strSQL & ":GTZSU,:GTZKO,:GTZKI,"
strSQL = strSQL & ":RIEKI,:SHIMED,:TATENE,"
strSQL = strSQL & ":SCOMCD,:SNSTCD,:SHIMEYM,"
strSQL = strSQL & "to_date('" & mDate & " " & mTime & "','YYYY/MM/DD/ HH24:MI:SS'),"
strSQL = strSQL & "'" & mUuid & "'"
strSQL = strSQL & ") "
blnFn1eof = False
'===約40万件のCSVをInsert===
Do Until blnFn1eof = True
'===各項目にCSVから読み込んだデータをセット===
Dim HANCD As String = Mid(strBuf, 1, 6)
Dim SSCD As String = Mid(strBuf, 8, 6)
Dim COMCD As String = Mid(strBuf, 15, 6)
Dim NSTCD As String = Mid(strBuf, 22, 3)
Dim TGNSU As String = Mid(strBuf, 26, 13)
Dim TGNTA As String = Mid(strBuf, 40, 10)
Dim TGNKI As String = Mid(strBuf, 51, 12)
Dim TJZSU As String = Mid(strBuf, 64, 13)
Dim TJZTA As String = Mid(strBuf, 78, 10)
Dim TJZKI As String = Mid(strBuf, 89, 12)
Dim SHISU As String = Mid(strBuf, 102, 13)
Dim SHIKO As String = Mid(strBuf, 116, 8)
Dim SHIKI As String = Mid(strBuf, 125, 12)
Dim TNSSU As String = Mid(strBuf, 138, 13)
Dim TNSKO As String = Mid(strBuf, 152, 8)
Dim TNSKI As String = Mid(strBuf, 161, 12)
Dim TNKSU As String = Mid(strBuf, 174, 13)
Dim TNKKO As String = Mid(strBuf, 188, 8)
Dim TNKKI As String = Mid(strBuf, 197, 12)
Dim URISU As String = Mid(strBuf, 210, 13)
Dim URIKO As String = Mid(strBuf, 224, 8)
Dim URIKI As String = Mid(strBuf, 233, 12)
Dim ZGNSU As String = Mid(strBuf, 246, 13)
Dim ZGNKO As String = Mid(strBuf, 260, 8)
Dim ZGNKI As String = Mid(strBuf, 269, 12)
Dim GTZSU As String = Mid(strBuf, 282, 13)
Dim GTZKO As String = Mid(strBuf, 296, 8)
Dim GTZKI As String = Mid(strBuf, 305, 12)
Dim RIEKI As String = Mid(strBuf, 318, 12)
Dim SHIMED As String = Mid(strBuf, 331, 2)
Dim TATENE As String = Mid(strBuf, 334, 1)
Dim SCOMCD As String = Mid(strBuf, 336, 6)
Dim SNSTCD As String = Mid(strBuf, 343, 3)
Dim SHIMEYM As String = Mid(strBuf, 347, 6)
mDate = String.Format(Today, "yymmdd")
mTime = String.Format(TimeOfDay, "hhmmss")
Try
Dim cmd As OracleCommand = New OracleCommand
cmd.Connection = conn
cmd.CommandText = strSQL
'===パラメータセット===
Dim pHANCD As OracleParameter = cmd.Parameters.Add("HANCD", HANCD)
Dim pSSCD As OracleParameter = cmd.Parameters.Add("SSCD", SSCD)
Dim pCOMCD As OracleParameter = cmd.Parameters.Add("COMCD", COMCD)
Dim pNSTCD As OracleParameter = cmd.Parameters.Add("NSTCD", NSTCD)
Dim pTGNSU As OracleParameter = cmd.Parameters.Add("TGNSU", TGNSU)
Dim pTGNTA As OracleParameter = cmd.Parameters.Add("TGNTA", TGNTA)
Dim pTGNKI As OracleParameter = cmd.Parameters.Add("TGNKI", TGNKI)
Dim pTJZSU As OracleParameter = cmd.Parameters.Add("TJZSU", TJZSU)
Dim pTJZTA As OracleParameter = cmd.Parameters.Add("TJZTA", TJZTA)
Dim pTJZKI As OracleParameter = cmd.Parameters.Add("TJZKI", TJZKI)
Dim pSHISU As OracleParameter = cmd.Parameters.Add("SHISU", SHISU)
Dim pSHIKO As OracleParameter = cmd.Parameters.Add("SHIKO", SHIKO)
Dim pSHIKI As OracleParameter = cmd.Parameters.Add("SHIKI", SHIKI)
Dim pTNSSU As OracleParameter = cmd.Parameters.Add("TNSSU", TNSSU)
Dim pTNSKO As OracleParameter = cmd.Parameters.Add("TNSKO", TNSKO)
Dim pTNSKI As OracleParameter = cmd.Parameters.Add("TNSKI", TNSKI)
Dim pTNKSU As OracleParameter = cmd.Parameters.Add("TNKSU", TNKSU)
Dim pTNKKO As OracleParameter = cmd.Parameters.Add("TNKKO", TNKKO)
Dim pTNKKI As OracleParameter = cmd.Parameters.Add("TNKKI", TNKKI)
Dim pURISU As OracleParameter = cmd.Parameters.Add("URISU", URISU)
Dim pURIKO As OracleParameter = cmd.Parameters.Add("URIKO", URIKO)
Dim pURIKI As OracleParameter = cmd.Parameters.Add("URIKI", URIKI)
Dim pZGNSU As OracleParameter = cmd.Parameters.Add("ZGNSU", ZGNSU)
Dim pZGNKO As OracleParameter = cmd.Parameters.Add("ZGNKO", ZGNKO)
Dim pZGNKI As OracleParameter = cmd.Parameters.Add("ZGNKI", ZGNKI)
Dim pGTZSU As OracleParameter = cmd.Parameters.Add("GTZSU", GTZSU)
Dim pGTZKO As OracleParameter = cmd.Parameters.Add("GTZKO", GTZKO)
Dim pGTZKI As OracleParameter = cmd.Parameters.Add("GTZKI", GTZKI)
Dim pRIEKI As OracleParameter = cmd.Parameters.Add("RIEKI", RIEKI)
Dim pSHIMED As OracleParameter = cmd.Parameters.Add("SHIMED", SHIMED)
Dim pTATENE As OracleParameter = cmd.Parameters.Add("TATENE", TATENE)
Dim pSCOMCD As OracleParameter = cmd.Parameters.Add("SCOMCD", SCOMCD)
Dim pSNSTCD As OracleParameter = cmd.Parameters.Add("SNSTCD", SNSTCD)
Dim pSHIMEYM As OracleParameter = cmd.Parameters.Add("SHIMEYM", SHIMEYM)
cmd.ExecuteNonQuery()
Catch e As OracleException
strErrText = "Code: " & e.ErrorCode & vbCrLf & _
"Message: " & e.Message
'ログファイル出力
Print(fn2, mDate & mTime)
Print(fn2, strDbName & strUser & strPass & " データ挿入失敗!")
Print(fn2, strErrText)
Call AbortClose()
End
End Try
'最終レコードの場合 blnFn1eofをTrueに設定(LOOP終了条件)。それ以外は一行読み込み
If EOF(fn1) = True Then
blnFn1eof = True
Else
strBuf = LineInput(fn1)
cntInp = cntInp + 1
End If
Loop
End Sub
End Module
これをどのようにバインド配列をもちいて記述すればよいのでしょうか?
このような投稿の仕方で誠に申し訳ありませんが、どうか宜しくお願い致します。
|
|