CODEWITHSUNDEEP
javahibernatejpajpql
Nitish Kashyap
Nitish Kashyap

Reputation: 51

JPA: possible reason for high query execution time

I have a spring boot application which is using a JPA query. The same query when executed directly on the live oracle DB tends to give results in some 20-40ms. On the other hand, when I try to hit using the application takes variable time ranging from 1-2 seconds to 50-60 seconds.

I want to understand the reason for this behavior as to why it is behaving unpredictably. We suspected it could be the limited number of threads in pool but later after isolating the application from external use now with only one user showed the same behavior.

The query should execute in a fast manner consistently. I wanted to know the possible reasons behind this behavior.

Upvotes: 3

Views: 1581

Answers (2)

Karol Dowbecki
Karol Dowbecki

Reputation: 44952

It could really be anything e.g. unreliable network, contended database resources, JDBC driver miss-configuration or JVM GC pause. Try to establish where is the problem: is it Java client or is the database server that is taking the time when the problem occurs.

If you suspect that the problem is the database it would be best to trace the connection and SQL query on the database server side. This will give you the most information e.g. query execution plan. Each database has it's own tools e.g. Oracle docs have entire chapter on Performing Application Tracing.

Upvotes: 1

majid
majid

Reputation: 66

One possible reason could be your entity relationships try enabling hibernate statistics for more detail:

You can enable by following: 

<persistence>
<persistence-unit name="my-persistence-unit">
    ...

    <properties>
        <property name="hibernate.generate_statistics" value="true" />

        ...
    </properties>
</persistence-unit>

Upvotes: 0

Related Questions