长时间使用Eclipselink从OracleDB提取数据 [英] Long time of fetching data from OracleDB using Eclipselink

问题描述

在我的应用程序中，我将Eclipselink用作OracleDB的ORM，并且遇到性能问题.

In my application I'm using Eclipselink as ORM for OracleDB and I encountered performance problem.

我正在执行这样的代码:

I'm executing code like this:

entityManager
 .createNamedQuery(RoleToPermissionEntity.FIND_BY_APPLICATION_ROLE, RoleToPermissionEntity.class)
 .setParameter(RoleToPermissionEntity.APPLICATION_ROLES_QUERY_PARAM, applicationRoles)
 .getResultList();

具有命名查询:

SELECT mapping 
FROM RoleToPermissionEntity mapping 
WHERE mapping.applicationRole IN :applicationRoles 
ORDER BY mapping.id

实体管理器由 @PersistenceContext 设置.

对于3个给定的应用程序角色，应用程序获得123行(来自393个)，每个列9个(2个带时区的时间戳，3个数字，4个短varchars).

For 3 given application roles application gets 123 rows (from 393), 9 column each (2 Timestamps with time zone, 3 numbers, 4 short varchars).

我检查了执行时间是否为给定代码执行前后的 System.nanoTime()之间的差异.大约是550毫秒，无论是第一次执行还是连续执行10次.我的假设是它应该快得多.

I checked time of execution as difference between System.nanoTime() before and after execution of given code. It's about 550 ms, no matter if it's executed 1st time or 10th in a row. And my assumption is that it should be much faster.

我的第一个猜测是查询问题，所以我检查了Eclipselink日志.执行的查询是:

My first guess was problem with query, so I checked Eclipselink logs. Executed query is:

SELECT *all_columns* 
FROM *table_name* 
WHERE (APPLICATION_ROLE IN (?,?,?)) ORDER BY ID
    bind => [3_application_roles]

对我来说还可以.我尝试将其作为本机查询执行，但结果相同.我还尝试了其他查询，例如 SELECT * FROM table_name ，但时间仍然约为500-600毫秒.

Looks ok for me. I tried to execute it as native query, but result is the same. I tried also other queries like SELECT * FROM table_name, but time still is about 500-600 ms.

这次我想进行一些比较，因此我手动创建了数据库连接并执行了如下查询:

I wanted to have some comparison for this time so I created database connection manually and executed query like:

Class.forName("oracle.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection(database_args);
Statement statement = connection.createStatement();
statement.executeQuery(query);

我执行了几次，第一次(建立连接时)花费了很长的时间，但接下来花费了50-60毫秒.

I executed it for several times, first (when connection was established) took quite a long time, but next took like 50-60 ms.

我的第二个猜测是连接池有问题.我试图在Eclipselink文档中找到一些东西，但我只注意到那些参数:

My second guess was problem with connection pool. I tried to find something in Eclipselink docs and I noticed only that parameters:

<property name="eclipselink.connection-pool.default.initial" value="1"/>
<property name="eclipselink.connection-pool.default.min" value="16"/>
<property name="eclipselink.connection-pool.default.max" value="16"/>

应该被设置.它们是，但是问题仍然存在.

should be set. They are, but the problem still exists.

我的persistence.xml的内容:

Content of my persistence.xml:

<persistence>
<persistence-unit name=unit transaction-type="JTA">
        <jta-data-source>datasource</jta-data-source>

        <exclude-unlisted-classes>false</exclude-unlisted-classes>
        <!-- cache needs to be deactivated for multiple pods -->
        <!-- https://wiki.eclipse.org/EclipseLink/Examples/JPA/Caching -->
        <shared-cache-mode>NONE</shared-cache-mode>

        <properties>
            <property name="eclipselink.logging.level" value="FINE"/>
            <property name="eclipselink.logging.level.sql" value="FINE"/>
            <property name="eclipselink.logging.parameters" value="true"/>
            <!--<property name="eclipselink.ddl-generation" value="create-or-extend-tables"/>-->
            <property name="eclipselink.weaving" value="false"/>
            <property name="eclipselink.target-database"
                      value="org.eclipse.persistence.platform.database.oracle.Oracle12Platform"/>
            <property name="eclipselink.connection-pool.default.initial" value="1"/>
            <property name="eclipselink.connection-pool.default.min" value="16"/>
            <property name="eclipselink.connection-pool.default.max" value="16"/>
        </properties>

    </persistence-unit>
</persistence>

我该怎么做才能解决此问题?

What can I do to fix this behavior?

推荐答案

几个小时后，我发现了问题所在.OJDBC的默认获取大小为10，因此随着行数的增加，获取时间会非常快地增加.

After few next hours I found the problem. Default fetch size of OJDBC is 10, so with increasing number of rows to fetch time increases very fast.

奇怪的是:这是我的第一个主意，因此我尝试在中设置<属性名称="eclipselink.jdbc.fetch-size" value ="100"/> > persistence.xml .它没有用，所以我跳到了其他解决方案.今天，我通过 query.setHint("eclipselink.jdbc.fetch-size"，100)将其设置为单个查询，并且可以使用.

What is strange: this was my first idea, so I tried to set <property name="eclipselink.jdbc.fetch-size" value="100"/> in persistence.xml. It didn't work, so I jumped to other solutions. Today I set it on single query by query.setHint("eclipselink.jdbc.fetch-size", 100) and it works.

这篇关于长时间使用Eclipselink从OracleDB提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！