oo4oを使ったOracleへのデータ処理を高速化したい

質問

タブ区切りのtsvファイルの１項目目に並んでいるデータをDBのプライマリーキーとみなして、DB上に既にあればupdate、なければinsertします。
下のPGは、DBに３０万件のデータが入った状態で実行すると、遅すぎて使えません。もっと速くすることはできませんでしょうか。

Public Const OraSQL_01 = "INSERT INTO CUSTOMER (COMPANY_CODE, TEST1) Values (:データ０, :データ１)"

Public Const OraSQL_02 = "UPDATE CUSTOMER SET COMPANY_CODE = :データ０, TEST1 = :データ１ WHERE COMPANY_CODE = :データ０"

wkFile1 = "c:\test\test.csv"
Open wkFile1 For Input As #1

OraSession.BeginTrans
Set rs = OraDatabase.CreateDynaset("SELECT COMPANY_CODE FROM CUSTOMER", ORADYN_DEFAULT)
OraDatabase.Parameters.Add "データ０", 0, ORAPARM_INPUT
OraDatabase.Parameters("データ０").ServerType = ORATYPE_NUMBER
OraDatabase.Parameters.Add "データ１", 0, ORAPARM_INPUT
OraDatabase.Parameters("データ１").ServerType = ORATYPE_VARCHAR2

Do Until EOF(1)
Line Input #1, Text
sec = Split(Text, vbTab)

OraDatabase.Parameters("データ０").Value = sec(0)
OraDatabase.Parameters("データ１").Value = sec(1)

Dim flg As Boolean
Do Until rs.EOF
flg = False
If rs("COMPANY_CODE") = sec(0) Then
OraDatabase.ExecuteSQL OraSQL_02
flg = True
Exit Do
Else
rs.MoveNext
End If
Loop

If flg = False Then
OraDatabase.ExecuteSQL OraSQL_01
End If
rs.MoveFirst
Loop
OraSession.CommitTrans

回答No.1

anmochi · ベストアンサー

ざっと眺めただけだが確かに遅すぎて使えそうにないな。

アプローチ１．tsvから1行読み込んだら、1列目(顧客コード)をキーにselectして引っかかればupdate、引っかからなければinsertを実行するように変更する。
　これはあれなのかな？　ひょっとしたらtsvに同じ顧客コードが2行以上あった時に、毎回selectするとエラーが発生せずに1回目に出てきたの餡餅商事はinsert→2回目以降に出てきた餡餅商事はupdateでエラーが発生せずに終わってしまうからダメなのかな？　それならこのアプローチを取らなかった理由は分かる。

アプローチ２．顧客コード一覧をダイナセットで取る時に顧客コードを昇順でソートし、バイナリサーチを使うように変更する。
　これは説明の必要もないだろう。シーケンシャルサーチはありえんだろう。30万行やし。

お礼

コードの記述方法がわかりました。
Set rs = OraDatabase.CreateDynaset("SELECT COMPANY_CODE FROM CUSTOMER WHERE COMPANY_CODE = :データ０", ORADYN_DEFAULT)

If rs("COMPANY_CODE") = sec2(0) Then
OraDatabase.ExecuteSQL OraSQL_02
Else
OraDatabase.ExecuteSQL OraSQL_01
End If

でできました。
有難うございました。

補足

anmochiさんご回答有難うございます。
恐縮ですが、アプローチ１をとる場合の書き方がわかりません。
１列目をキーにSELECTしてダイナセットで取るには

Set rs = OraDatabase.CreateDynaset("SELECT COMPANY_CODE FROM CUSTOMER WHERE COMPANY_CODE = :データ０", ORADYN_DEFAULT)
でよいかと思いますが、ひっかかっる、ひっかからないのコードはどのように記述すれば宜しいのでしょうか、わかれば教えて頂けませんでしょうか。

宜しくお願いします。

回答No.4

taka_tetsu

こんな手も。

1.空のテンポラリの表を作成。主キーもつけておく。

2.テキストファイルの内容をテンポラリ表にSQL*Loaderでロード

3.更新対象のデータをCUSTOMERから削除
delete from CUSTOMER where COMPANY_CODE in (select COMPANY_CODE from テンポラリ表);

4.テンポラリ表の内容をCUSTOMERに追加
insert into CUSTOMER select * from テンポラリ表;

5.テンポラリ表の削除。

＃CUSTOMERテーブルの行数だけではなく、テキストファイルの行数も多ければ、下手にロジック組むよりこの方が高速です。

回答No.3

tsukasa-12r

Oracle のバージョンはいくつでしょうか？
9i 以降だったら MERGE が使えますけど。

回答No.2

taka_tetsu

selectというか、Dynaset不要。insertして一意制約違反だったらupdateすればいいだけ。
updateの際は、主キーの項目はsetに含めない。