一尘不染

具有HIBERNATE的JPA插入速度非常慢

hibernate

我正在尝试通过使用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;
}
}

阅读 273

收藏
2020-06-20

共1个答案

一尘不染

要启用JDBC批处理,您应该将hibernate.jdbc.batch_size属性初始化为10到50之间(仅int)

hibernate.jdbc.batch_size=50

如果仍然不如预期的快,那么我将回顾以上文档,同时注意“注意”和第4.1节。特别是提示:“如果使用身份标识符生成器,​​则Hibernate透明地禁用JDBC级别的插入批处理。”

2020-06-20