JPA与HIBERNATE插入非常慢 [英] JPA with HIBERNATE insert very slow

问题描述

我试图通过使用JAP和HIBERNATE插入一些数据到SQL Server 2008 R2。一切工作除了它是非常缓慢。要插入20000行，大约需要45秒，而C＃脚本大约需要不到1秒。

I am trying to insert some data to SQL Server 2008 R2 by using JAP and HIBERNATE. Everything "works" except for that it's very slow. To insert 20000 rows, it takes about 45 seconds, while a C# script takes about less than 1 second.

此域中的任何老手都能提供帮助吗？非常感谢。

Any veteran in this domain can offer some helps? I would appreciate it a lot.

更新：从下面的答案得到了一些很好的建议，但它仍然不能按预期工作。速度是一样的。

Update: got some great advices from the answers below, but it still doesn't work as expected. Speed is the same.

这里是更新的persistence.xml：

Here is the updated 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的源代码：

Updated, here is the source for 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）

To enable JDBC batching you should initialize the property hibernate.jdbc.batch_size to between 10 and 50 (int only)

hibernate.jdbc.batch_size=50

如果仍然没有达到预期的那么快，那么我将审查上面的文档，注意注意和第4.1节。特别是，如果使用身份标识符生成器，​​Hibernate会在JDBC级别禁用插入批处理。

If it's still not as fast as expected, then I'd review the document above paying attention to NOTE(s) and section 4.1. Especially the NOTE that says, "Hibernate disables insert batching at the JDBC level transparently if you use an identity identifier generator."

这篇关于JPA与HIBERNATE插入非常慢的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！