|
我正在尝试通过使用JAP和HIBERNATE向SQL Server 2008
$ F6 U0 y- j+ B9 ZR2插入一些数据。一切都“正常”,除了它非常慢。要插入20000行,大约需要45秒,而C#脚本大约需要不到1秒。; z8 i2 W( n% m# K0 J F* {9 W
这个领域的任何资深人士都可以提供帮助吗?我会很感激。
3 M% N: a1 x: v9 P* A8 `# Z, |更新:从下面的答案中得到了一些很好的建议,但仍然无法按预期工作。速度是一样的。' N* n% j2 G) U% S9 A" k# m
这是更新的persistence.xml:
. }1 M, v) g6 J7 X9 Z) }) e7 e' U7 }
org.hibernate.ejb.HibernatePersistence
7 G8 K% U* r* O1 M ^, M5 s) k+ R cluster.data.persist.sqlserver.EventResult
; ?* b9 x* X$ G$ \- T true
# I1 D& {( H. P+ J; q G/ a8 e( W; s2 A9 l h
3 W2 y, C) f8 R+ U# x+ {
, N; x6 Y. \* U6 ~3 d
' H |$ f9 ~5 | 1 [# d+ x S4 ^; C+ X: j7 U+ L1 `
, X0 Z+ g$ O" e( N8 U
2 @; |5 ~5 r7 [, Q% |6 f M& l
3 D* X- x6 X8 O7 x- G - e9 _ K) L3 X q$ A2 v7 g% X
2 d9 F; Y+ ?/ Z7 ^ d. N
- c5 E U7 m. W- s: Y. r2 h) u0 I8 s ; Q7 F+ ^9 v* E( w/ U
& ~) ]( y. n) |; L
. ?( U- o( @. w! J
* U" C' f: X2 Q ^/ S5 x. z
9 Z1 q1 r) c+ k# ~8 D8 ^* C' {8 }! ^ 3 E9 W# a% `8 f' R
* d2 Z+ w9 L' F2 ~
这是更新的代码部分:
5 n9 k/ w8 Z9 k4 d9 Q8 k; f b0 mpublic static void writeToDB(String filePath) throws IOException {3 a# U' z+ Y, w0 d$ n
EntityManager entityManager = entityManagerFactory.createEntityManager();
, w% n5 }5 f% P4 Y! [7 L& X: t8 [ Session session = (Session) entityManager.getDelegate();
' L- w/ A1 ]* ` Transaction tx = session.beginTransaction();: N5 U1 E2 V8 X1 U4 k
int i = 0;+ H/ w2 \- K0 y- H h
URL filePathUrl = null;
$ b2 E9 }* L. }* x4 b( e' o try {
; u$ M: I5 L$ o+ p& P filePathUrl = new URL(filePath);
; n3 m6 N, S a' Y1 r' a } catch (MalformedURLException e) {
1 E# b0 y/ O1 }: I) R5 F8 K; y filePathUrl = (new File(filePath)).toURI().toURL();
) b' E f" Y0 t }
) \" `1 U. A0 ]3 r String line = null;
5 B) Y: u7 R7 U BufferedReader stream = null;
& F/ H3 s5 K1 u9 [ Q# @ try {
0 t- U! Y: C# y& a; V InputStream in = filePathUrl.openStream();
" c& G( b5 D; ~: b0 X, |$ C stream = new BufferedReader(new InputStreamReader(in));
" b! V' P. i; v i3 y$ H- k. R' ^/ F9 X
// Read each line in the file+ `7 r! t6 K( ~- b/ m
MyRow myRow = new MyRow();
% s3 J; o# J! f while ((line = stream.readLine()) != null) {
" y5 l. ]) L3 y) B/ A: V4 A String[] splitted = line.split(",");! O7 e$ y3 K" i( O n
int num1 = Integer.valueOf(splitted[1]);
) \% ]" x7 i T- R& b3 C float num2= Float.valueOf(splitted[6]).intValue();8 m' m+ V) j: x1 H0 [. a6 D5 M: e
myRow.setNum1(num1);3 ]9 G H k, h
myRow.setNum2(num2);- i; k6 }. j, x5 J8 V
session.save(myRow);2 \+ r. n0 M4 M" u
if (i % 50 == 0) {
5 h% D+ ^ S* b" D session.flush();0 z* p [% g2 r9 v) |) x5 l
session.clear();
% @2 k P# E' N4 V& A" x0 t }8 w. h5 L4 i5 k; t" K7 D2 a
i++; c, W& l( T2 P& b$ q% v
}
/ v: j4 w3 i1 [ tx.commit();
. r" |! w3 [( n7 @8 L( x } finally {, _: e5 j; r& v) j" j2 g) l
if (stream != null)6 @; |1 G4 U! J: f! `% Q2 }
stream.close();
* W% U6 ]+ ~2 p# z. t# z }
) I( `* f7 K7 j0 Z& | session.close();0 ~ G0 X1 v' B. G, v0 }% u
}
3 Q" R1 K& w& H已更新,这是MyRow的来源:5 d8 H$ m, y4 X% Z
@Entity( Z5 `6 ?+ X( r8 Y; l9 p# A6 F
@Table(name="MYTABLE")
; Q. o. A1 w' x9 w# cpublic class MyRow {
6 ]; @. M3 C$ F& _7 ?5 \; O@Id
6 E4 w1 V+ b: G8 d! y@GeneratedValue(strategy=GenerationType.IDENTITY) . {/ n: U. Q& ^$ R u9 O8 Z/ J
private Long id;9 n5 S( u' }2 x6 C9 w9 `
@Basic
1 e3 S2 m1 ]. R& @@Column(name = "Num1")
" e0 h4 I) u- r$ S3 aprivate int Num1;
: O+ O# _/ }: J@Basic
v* a. C7 C8 U% `6 O( _@Column(name = "Num2")
! O E' x. B: b* ]) ?/ Z$ mprivate float Num2;( ~* q+ T( z8 Y
public Long getId() {
$ ]/ v" G4 F* Z" \) M3 R. k T3 R return id;- x h3 o+ H; }# N/ ~2 t: q
}
" {5 u. n; H8 \. j$ Z3 Jpublic void setId(Long id) {
% p5 @, r; ?6 A( A$ z- d/ K! q% B this.id = id;* {% C8 w( F7 F7 g
}
( f4 b9 B( f' u1 X! X, o; Tpublic float getNum1() {
; [ M; y% ]/ z+ A- Q( R return Num1;
) R( f! Y4 x2 ]/ h- R) f6 F}- a4 e3 V& h2 R8 Z# Y/ g
public void setNum1(float num1) {
2 r# s1 F+ H; J7 q' W Num1 = num1;
. q/ Y8 @; m6 W3 Q# p) L0 W}
* @8 f8 O! \# }# Xpublic int getNum2() {' A/ T9 E/ ?# v6 D8 K
return Num2;; Q) f/ w! Z ^5 Z5 ~1 I. o
}
3 e N2 L$ R: d& ^+ P0 S& y$ s2 Ypublic void setNum2(int num2) {
- n! D, o {/ ?: \8 r0 D* i* e Num2 = num2;
+ Y4 i, o% Q2 r8 I% E6 ]}3 L/ _* C7 t" a$ L7 M, w
}
" i1 g' M3 f! r. Q( R: U# ? ( y% i/ r4 D/ ^9 f, q; ?
解决方案:" b2 O/ R% E) X$ f! m
, d P! g3 z) h8 N) c: X
( ]0 q: Q g3 c( F
6 g3 M- j( y# N
要启用JDBC批处理,您应该将hibernate.jdbc.batch_size属性初始化为10到50之间(仅int)
* P5 |2 @. _0 P$ C$ |' P" ihibernate.jdbc.batch_size=50
" p( `3 w( ], K; G如果它仍然不如预期的那么快,那么我将回顾上面的文档,同时注意“注意”和第4.1节。特别是提示:“如果使用身份标识符生成器,??则Hibernate会透明地禁用JDBC级别的插入批处理。” |
|