二,采用DataAdapter实现批量插入" W2 K) I8 h3 M; o1 S
VB.NET
m& K) J/ u9 P3 g% r7 Y [% u4 m 1 sw.Start(): s4 H( n4 ]; J* p* z0 P
2 ''''Read Z02J from SQL Server
* b8 A/ `+ d* q 3 Dim sqlCmd As New SqlCommand()8 d: g5 T/ Q- w; y# m! }1 e
4 sqlCmd.Connection = sqlConnection
+ v; i6 S1 c# p. Z7 E1 ? C 5 sqlCmd.CommandText = "SELECT * FROM Z02J"; R# l, H' ?2 E4 x4 o }, ]
6* n4 Y2 L) v4 a, F
7 Dim sqlDr As SqlDataReader
, `( h9 _; b# S2 u3 f) L 8 sqlDr = sqlCmd.ExecuteReader()
! [6 B8 \( Y$ k0 ]; F 9
' M& }( Q. D% |8 _* X 10 Dim cmdInsertZ02J As New OracleCommand()
7 e" V0 F3 h& n+ @& D 11 cmdInsertZ02J.Connection = oraConnection
: g* z. ~7 z6 T. f6 S8 W 12 cmdInsertZ02J.CommandText = BuildSQLStatement(SQLType.Insert,"z02j")0 @2 I' E) A" s, p) z7 ]* Y h
13
9 N0 N, b: @; T* H+ z' W& i 14 Dim dtSqlZ02J As New DataTable1 h2 c" F" V( _0 T& L
15 dtSqlZ02J.Columns.Add("plantLever")
`0 E1 u+ b& Q' X0 H i 16 dtSqlZ02J.Columns.Add("material")
8 ]1 B8 v. `$ j- k 17 dtSqlZ02J.Columns.Add("oldMaterialNum")/ ^* m) m+ n+ t0 P9 I/ }
18 dtSqlZ02J.Columns.Add("materialDescription")
& D, y/ m2 Y. _8 H1 e 19
0 A* R. Y3 k6 l6 C( v0 G6 n/ s$ ^! U/ ~ D 20 Dim plantLever, material, oldMaterialNum, materialDescription As Object0 c$ d3 a8 p5 p* K% Q
21 While sqlDr.Read()
! j2 _, h* e( [/ S 22 plantLever = ReadSqlDataReader(sqlDr, 0, "")
* ?( ]; i7 H& [! Q% p9 e% ^ 23 material = ReadSqlDataReader(sqlDr, 1, ""), r2 X* q0 \& z+ F6 [" R
24 oldMaterialNum = ReadSqlDataReader(sqlDr, 2, "")9 r% e2 P$ \! \8 Y
25 materialDescription = ReadSqlDataReader(sqlDr, 3, "")
* {/ k% r$ g( }8 E$ H i# P 26 dtSqlZ02J.Rows.Add(plantLever, material, oldMaterialNum, materialDescription)5 h8 m9 X7 {3 M3 x& |6 x) C4 m
27 End While
# Y: m3 l1 R& P+ e* S 28 sw.Stop()
0 q) I2 U- Z8 y g 29 Loger.Info("Reading z02j form sql sever used", sw.Elapsed.TotalSeconds.ToString())) {$ c" D5 {6 l' r/ D$ m
30+ A8 v7 A& l O9 V
31 sw.Start()! g. E: { O. R7 E4 W' D) m" X1 U3 z
32 Dim oraDa As New OracleDataAdapter()5 X, I1 T" x9 P( K2 V' b
33 oraDa.InsertCommand = cmdInsertZ02J; J8 z4 }! N+ z; c- g; m& H9 E
34 oraDa.InsertCommand.Parameters.Add(":plantLever", OracleType.Char, 255, "plantLever")
8 ]/ L# G# c( V E& _0 h6 T 35 oraDa.InsertCommand.Parameters.Add(":material", OracleType.Char, 255, "material")
8 J! N# x \# z/ U3 D 36 oraDa.InsertCommand.Parameters.Add(":oldMaterialNum", OracleType.Char, 255, "oldMaterialNum")
4 [1 F% R$ C9 F, q6 L) `4 P2 X2 h& T 37 oraDa.InsertCommand.Parameters.Add(":materialDescription", OracleType.Char, 500, "materialDescription")
$ }7 C9 g: N @0 S 38
% B+ T4 c1 a4 F7 v( z% {$ U 39 oraDa.InsertCommand.UpdatedRowSource = UpdateRowSource.None; H$ k" b8 `/ {
40 oraDa.UpdateBatchSize = 20 '''Adjust the batch size based on testing result( G( B" F' w, u8 i9 U
41( Z* I4 h# V2 G* W0 c# N" S; s
42 oraDa.Update(dtSqlZ02J)6 Y; d% Z* N' `$ T3 n; C! U
43 sw.Stop()! z u% @" s/ x$ h) P6 m5 I3 x
44 Loger.Info("Insert to oracle used", sw.Elapsed.TotalSeconds.ToString())
! p% o" @5 K% e9 w2 g4 Z2 D 在我的环境中批量插入24万笔记录用时大约260s左右。
0 Y6 w* l0 N( [9 U; z4 v 貌似SQL Server中。net驱动程序提供了SqlBulkCopy类来提高大量数据导入的性能。有需要的朋友可以查下MSDN. |