CODEWITHSUNDEEP
javapostgresqljpaeclipselink
bob-cac
bob-cac

Reputation: 1302

JPQL performance vs native queries

consider the following structure

@Entity
@NamedQuery(name="find",query="select a from A a where a.parent.id=:id")
public class A {
@Id
int id;
String name;
@OneToMany
@JoinColumn
List<A> childs;
@ManyToOne(fetch=fetchType.lazy)
A parent;
}

The table A in the postgre engine contains about 700000 records

executing this named jpql query take an average 2.2 sec while executing the same query after translating it to native take an average of 0.3 sec

"select id,name, parent_id from A where parent_id=?"

it tried the eclipse link profiler the property that take most of the time of the query is "execute statement" about 1.9 sec while 0.3 sec are lost on cache, log in, transaction management etc...

how to find why the query consume more time in jpql. Do i have missed something in configuration or it is natural for jpql to have an overhead of 1.9 sec for such simple query when there are a lot of data in the database

edit:

note when i used the following named query (mentioning the fields/column) the performance have not increased.

query="select a.name,a.id from A a where a.parent.id=:id"

Upvotes: 0

Views: 6631

Answers (1)

user7375303
user7375303

Reputation:

What I can see as difference between the two methods is: 1- JPQL = select a from A a where a.parent.id=:id, will perform an SQL join on the database to fetch data, you will have something like this behind the scenes:

select * from A a
        JOIN parent on parent.id = a.parent_id
        WHERE a.parent_id = ?

2- Native SQL = select id, name, parent_id from A where parent_id=?, won't perform any join and will fetch data on a unique table which is more direct from a performance perspective.

I encourage people facing same issues to enable show_sql property in order to compare generated SQL queries as a first start. The delay could be caused by other factors: locks on tables, etc.

Upvotes: 0

Related Questions