I am currently facing a performance issue with Hibernate and Postgresql.
I have this query (I use the criteria API with JPA MetaModel):
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
cq.from(MyEntity.class);
cq.multiselect(
root.get(MyEntity_.id),
root.get(MyEntity_.name)
);
criteriaQuery.where(
root.get(MyEntity_.link).in(links)
);
Query query = entityManager.createQuery(cq);
long start = System.currentTimeMillis();
List<Object[]> results = query.getResultList();
System.out.println("Query execution time : " + System.currentTimeMillis() - start);
Please note that the "links" variable is a collection of objects (the size of the collection is not limited, but my request is executed with 289 elements).
When I execute this query, I see that the following SQL is running:
SELECT e.id, e.name FROM my_table e WHERE e.link_id IN (?, ?, ? ..... ?);
The prepared statement is executed in sleep mode, and the query execution time is about 4.5 seconds.
If I change my query:
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Object[]> cq = cb.createQuery(Object[].class);
cq.from(MyEntity.class);
cq.multiselect(
root.get(MyEntity_.id),
root.get(MyEntity_.name)
);
criteriaQuery.where(
root.get(MyEntity_.link).get(Link_.id).in(ids)
);
Query query = entityManager.createQuery(cq);
long start = System.currentTimeMillis();
List<Object[]> results = query.getResultList();
System.out.println("Query execution time : " + System.currentTimeMillis() - start);
Now the ids variable is the set of all link object identifiers, and now the following SQL query is executed:
SELECT e.id, e.name FROM my_table e WHERE e.link_id IN (1, 2, 3, 4, ..... 289);
A simple instruction is executed by hibernation, and the request execution time is about 100 milliseconds.
, :
, jdbc ( PreparedStatement), 100 . , , PreparedStatement , , .
, :
- Hibernate 4.2.2-Final
- PostgreSQL-9.3-1102-jdbc41
- - Postgresql 9.3.1
?
.