长时间从Oracle数据库中使用Eclipselink获取数据

huangapple go评论74阅读模式
英文:

Long time of fetching data from OracleDB using Eclipselink

问题

在我的应用程序中,我使用Eclipselink作为OracleDB的ORM,并且遇到了性能问题。

我正在执行如下的代码:

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个短字符串)。

我通过测量执行代码前后的System.nanoTime()之差来检查执行时间。无论是第一次执行还是连续执行10次,执行时间都大约为550毫秒。我认为执行时间应该更快。

我最初的猜测是查询的问题,所以我检查了Eclipselink的日志。执行的查询为:

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

在我看来,这看起来没问题。我尝试将其作为本地查询执行,但结果仍然相同。我还尝试了其他查询,如SELECT * FROM table_name,但时间仍然在500-600毫秒左右。

我想要对这个时间进行一些比较,所以我手动创建了数据库连接,并执行了以下查询:

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

我多次执行了这个查询,第一次(在建立连接时)花费了相当长的时间,但后续执行只需要50-60毫秒。

我第二个猜测是连接池的问题。我试图在Eclipselink文档中找到一些信息,我注意到只有以下参数:

<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"/>

应该已经设置了它们,但问题仍然存在。

我的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>

我应该做些什么来解决这个问题?

英文:

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();

with named query:

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

Entity manager is set by @PersistenceContext.

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

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.

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 =&gt; [3_application_roles]

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(&quot;oracle.jdbc.driver.OracleDriver&quot;);
connection = DriverManager.getConnection(database_args);
Statement statement = connection.createStatement();
statement.executeQuery(query);

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

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

&lt;property name=&quot;eclipselink.connection-pool.default.initial&quot; value=&quot;1&quot;/&gt;
&lt;property name=&quot;eclipselink.connection-pool.default.min&quot; value=&quot;16&quot;/&gt;
&lt;property name=&quot;eclipselink.connection-pool.default.max&quot; value=&quot;16&quot;/&gt;

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

Content of my persistence.xml:

&lt;persistence&gt;
&lt;persistence-unit name=unit transaction-type=&quot;JTA&quot;&gt;
        &lt;jta-data-source&gt;datasource&lt;/jta-data-source&gt;

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

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

    &lt;/persistence-unit&gt;
&lt;/persistence&gt;

What can I do to fix this behavior?

答案1

得分: 1

几个小时后,我找到了问题。OJDBC的默认获取大小是10,所以随着要获取的行数增加,时间增加得非常快。

奇怪的是:这是我的第一个想法,所以我尝试在persistence.xml中设置&lt;property name=&quot;eclipselink.jdbc.fetch-size&quot; value=&quot;100&quot;/&gt;。但它不起作用,所以我转而尝试其他解决方案。今天我通过query.setHint(&quot;eclipselink.jdbc.fetch-size&quot;, 100)在单个查询中设置它,然后它有效了。

英文:

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.

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

huangapple
  • 本文由 发表于 2020年5月3日 18:40:10
  • 转载请务必保留本文链接:https://go.coder-hub.com/61573091.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定