二,采用DataAdapter实现批量插入" u( ?: |- _# x5 H
VB.NET
$ g, \. r# P5 U6 M 1 sw.Start()
: ~9 Q4 C- j3 B' N0 W8 D( V 2 ''''Read Z02J from SQL Server
- j P- T+ U; m2 t. I 3 Dim sqlCmd As New SqlCommand()1 Y- O4 m- o+ |* b1 }$ ^) b
4 sqlCmd.Connection = sqlConnection( z4 v6 R! V+ M. W( N7 D
5 sqlCmd.CommandText = "SELECT * FROM Z02J"2 B% O. [' _% w4 k6 ~& V4 y
6
0 E3 Q$ I5 ]+ P 7 Dim sqlDr As SqlDataReader
' y* D5 s) Y/ T9 t u( E 8 sqlDr = sqlCmd.ExecuteReader()' O5 i- C7 r% z; z. _) [. G& }
9
0 p5 ~) A) M6 `) o- K! m 10 Dim cmdInsertZ02J As New OracleCommand()
) y9 h; l6 P t& I; k) _$ b# s P 11 cmdInsertZ02J.Connection = oraConnection5 M; L) {' B1 b+ L5 V
12 cmdInsertZ02J.CommandText = BuildSQLStatement(SQLType.Insert,"z02j")9 g3 k) S7 F& F, l
13
/ K3 z% {4 `* Y- i6 d 14 Dim dtSqlZ02J As New DataTable
% r9 @$ @& y: a( G$ l5 J* D: _4 d+ { 15 dtSqlZ02J.Columns.Add("plantLever")9 \& p$ | ^; s4 m/ I- K
16 dtSqlZ02J.Columns.Add("material")! ]/ I) v1 q' |' c. s" V h3 y7 }
17 dtSqlZ02J.Columns.Add("oldMaterialNum")4 x0 C |: J/ h2 o% G
18 dtSqlZ02J.Columns.Add("materialDescription")
% Y! ?; i8 W5 r6 j* ]; x6 A- b 19( l, U# w' e6 s2 C' u
20 Dim plantLever, material, oldMaterialNum, materialDescription As Object$ q% d. D3 u- K! c) x4 W
21 While sqlDr.Read()+ E2 L. P% {, a3 ~: ?' n
22 plantLever = ReadSqlDataReader(sqlDr, 0, "")
: Z. c2 T# F* ^3 I3 e6 r+ W 23 material = ReadSqlDataReader(sqlDr, 1, "")
- Y' f% w4 Y) g1 q2 P' s$ n 24 oldMaterialNum = ReadSqlDataReader(sqlDr, 2, "")4 o6 f& i: j2 m9 e; r7 p& N4 ?
25 materialDescription = ReadSqlDataReader(sqlDr, 3, "")/ z% _$ e2 x$ ~) l4 t
26 dtSqlZ02J.Rows.Add(plantLever, material, oldMaterialNum, materialDescription)
& h, b7 @6 a# Y; a. N 27 End While
& z5 V# W7 u+ U$ i: [- K9 b 28 sw.Stop()
& N) b" s. u2 @& z' U/ Q" R 29 Loger.Info("Reading z02j form sql sever used", sw.Elapsed.TotalSeconds.ToString())
3 t- C# J; S9 k7 i2 E ^. i u 30
# ]- }5 Z/ H/ s8 k2 r 31 sw.Start()( N1 F z, k1 S; d! e3 U$ O2 ^
32 Dim oraDa As New OracleDataAdapter()9 H* s8 C9 n5 `& k& Y( I0 Z+ A
33 oraDa.InsertCommand = cmdInsertZ02J* v3 c. a0 T3 y$ X# d
34 oraDa.InsertCommand.Parameters.Add(":plantLever", OracleType.Char, 255, "plantLever")$ J8 y8 j! `! l# t3 J9 a+ t6 o
35 oraDa.InsertCommand.Parameters.Add(":material", OracleType.Char, 255, "material")
0 T" E" r: H) u9 E 36 oraDa.InsertCommand.Parameters.Add(":oldMaterialNum", OracleType.Char, 255, "oldMaterialNum")* f; \; D1 m- V4 D6 ]1 E
37 oraDa.InsertCommand.Parameters.Add(":materialDescription", OracleType.Char, 500, "materialDescription")3 B- L, T, J4 o
38* s& D N" G5 z
39 oraDa.InsertCommand.UpdatedRowSource = UpdateRowSource.None+ _% B( }( ?) X
40 oraDa.UpdateBatchSize = 20 '''Adjust the batch size based on testing result4 e- c6 @6 x! k0 s
41% p# a9 Z& O: C6 v3 Z
42 oraDa.Update(dtSqlZ02J)
( m" v& C2 `. N& B$ R* V! g 43 sw.Stop()" {# c* |0 W+ g, G+ s0 e. O7 h) B; ~
44 Loger.Info("Insert to oracle used", sw.Elapsed.TotalSeconds.ToString()) r/ s, X" b' |2 \! W. w
在我的环境中批量插入24万笔记录用时大约260s左右。
$ \! T6 V" w( }" I9 b 貌似SQL Server中。net驱动程序提供了SqlBulkCopy类来提高大量数据导入的性能。有需要的朋友可以查下MSDN. |