CODEWITHSUNDEEP
javaoracle-databaseeclipselink
szczyzanski
szczyzanski

Reputation: 131

Long time of fetching data from OracleDB using Eclipselink

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?

Upvotes: 1

Views: 953

Answers (1)

szczyzanski
szczyzanski

Reputation: 131

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.

Upvotes: 2

Related Questions