我正在尝试通过使用JAP和HIBERNATE向SQL Server 2008 R2插入一些数据。一切都“正常”,除了它非常慢。要插入20000行,大约需要45秒,而C#脚本大约需要不到1秒。
这个领域的任何资深人士都可以提供帮助吗?我会很感激。
更新:从下面的答案中得到了一些很好的建议,但仍然无法按预期工作。速度是一样的。
这是更新的persistence.xml:
<persistence version="2.0" xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"> <persistence-unit name="ClusterPersist" transaction-type="RESOURCE_LOCAL"> <provider>org.hibernate.ejb.HibernatePersistence</provider> <class>cluster.data.persist.sqlserver.EventResult</class> <exclude-unlisted-classes>true</exclude-unlisted-classes> <properties> <property name="javax.persistence.jdbc.url" value="jdbc:sqlserver://MYSERVER:1433;databaseName=MYTABLE" /> <property name="javax.persistence.jdbc.user" value="USER" /> <property name="javax.persistence.jdbc.password" value="PASSWORD" /> <property name="javax.persistence.jdbc.driver" value="com.microsoft.sqlserver.jdbc.SQLServerDriver" /> <property name="hibernate.show_sql" value="flase" /> <property name="hibernate.hbm2ddl.auto" value="update" /> <property name="hibernate.connection.provider_class" value="org.hibernate.service.jdbc.connections.internal.C3P0ConnectionProvider" /> <property name="hibernate.c3p0.max_size" value="100" /> <property name="hibernate.c3p0.min_size" value="0" /> <property name="hibernate.c3p0.acquire_increment" value="1" /> <property name="hibernate.c3p0.idle_test_period" value="300" /> <property name="hibernate.c3p0.max_statements" value="0" /> <property name="hibernate.c3p0.timeout" value="100" /> <property name="hibernate.jdbc.batch_size" value="50" /> <property name="hibernate.cache.use_second_level_cache" value="false" /> </properties> </persistence-unit>
这是更新的代码部分:
public static void writeToDB(String filePath) throws IOException { EntityManager entityManager = entityManagerFactory.createEntityManager(); Session session = (Session) entityManager.getDelegate(); Transaction tx = session.beginTransaction(); int i = 0; URL filePathUrl = null; try { filePathUrl = new URL(filePath); } catch (MalformedURLException e) { filePathUrl = (new File(filePath)).toURI().toURL(); } String line = null; BufferedReader stream = null; try { InputStream in = filePathUrl.openStream(); stream = new BufferedReader(new InputStreamReader(in)); // Read each line in the file MyRow myRow = new MyRow(); while ((line = stream.readLine()) != null) { String[] splitted = line.split(","); int num1 = Integer.valueOf(splitted[1]); float num2= Float.valueOf(splitted[6]).intValue(); myRow.setNum1(num1); myRow.setNum2(num2); session.save(myRow); if (i % 50 == 0) { session.flush(); session.clear(); } i++; } tx.commit(); } finally { if (stream != null) stream.close(); } session.close(); }
已更新,这是MyRow的来源:
@Entity @Table(name="MYTABLE") public class MyRow { @Id @GeneratedValue(strategy=GenerationType.IDENTITY) private Long id; @Basic @Column(name = "Num1") private int Num1; @Basic @Column(name = "Num2") private float Num2; public Long getId() { return id; } public void setId(Long id) { this.id = id; } public float getNum1() { return Num1; } public void setNum1(float num1) { Num1 = num1; } public int getNum2() { return Num2; } public void setNum2(int num2) { Num2 = num2; } }
要启用JDBC批处理,您应该将hibernate.jdbc.batch_size属性初始化为10到50之间(仅int)
hibernate.jdbc.batch_size=50
如果仍然不如预期的快,那么我将回顾以上文档,同时注意“注意”和第4.1节。特别是提示:“如果使用身份标识符生成器,则Hibernate透明地禁用JDBC级别的插入批处理。”