JPQL performance vs native queries

Question

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"

Of course you'd have to expect a performance hit. `SELECT a FROM A ...` means you're selecting entities, i.e. EclipseLink would have to (list not complete): parse the JPQL query, convert it to SQL, execute the SQL query, create entities from the result. I don't know EclipseLink that well but if it's similar to Hibernate there's a lot more involved, e.g. updating any caches, checking for dirty entities etc. — Thomas, Apr 04 '17 at 09:37
yeah i know , but after using the profiler the time loss is in query execution itself. also note the queries i am using expected to return a max of 10 result — bob-cac, Apr 04 '17 at 09:41
Are you sure you checked all the queries that EclipseLink is executing? Your mapping looks like `parent` is being fetched eagerly and thus what's actually getting executed might be more than the simple query you manually tested (at least that query might get executed multiple times). — Thomas, Apr 04 '17 at 09:46
Show the profiler results, as if "select a.name,a.id from A a where a.parent.id=:id" is returning in the amount of time native SQL would, then it seems likely the difference has to do with the amount of time processing the results to build entities. — Chris, Apr 04 '17 at 19:09

score 0 · Answer 1 · answered Jul 25 '20 at 10:02

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.

JPQL performance vs native queries

1 Answers1