英文:
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 => [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("oracle.jdbc.driver.OracleDriver");
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:
<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.
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?
答案1
得分: 1
几个小时后,我找到了问题。OJDBC的默认获取大小是10,所以随着要获取的行数增加,时间增加得非常快。
奇怪的是:这是我的第一个想法,所以我尝试在persistence.xml
中设置<property name="eclipselink.jdbc.fetch-size" value="100"/>
。但它不起作用,所以我转而尝试其他解决方案。今天我通过query.setHint("eclipselink.jdbc.fetch-size", 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 <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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论