0
I am developing a Farm Management system. One of the basic queries is to list the animals by type that are alive (inFarm = true). Below is the method that executes this query.
public List<T> findByInFarm(T model, boolean inFarm) throws DAOException {
List<T> animalList = null;
EntityManager em = JpaHelper.getEntityManager();
Query query = em
.createQuery("SELECT a FROM " + model.getClass().getSimpleName() + " a WHERE a.inFarm = :inFarm");
query.setParameter("inFarm", inFarm);
try {
long start = System.currentTimeMillis();
animalList = (List<T>) query.getResultList();
long end = System.currentTimeMillis();
System.out.println("findByInFarm() demorou " + (end - start) + " millis");
} catch (Exception e) {
throw new DAOException(e.getCause().toString());
} finally {
if (em.isOpen()) {
em.close();
}
}
return animalList;
}
The query returns the expected result, but after executing the same query a few times, the performance drops a lot. I inserted a System.currentTimeMillis() before query.getResultList() and an after to measure the performance of the query and I ran the test below.
public void test() {
while (true) {
try {
List<Cow> cowList = new Cow().findByInFarm(true);
new Thread().sleep(2000);
} catch (Exception e) {
e.printStackTrace();
}
}
}
Below is the result obtained after some repetitions:
- findByInFarm() took 1017 Millis
- findByInFarm() took 615 Millis
- findByInFarm() took 606 Millis
- findByInFarm() took 591 Millis
- findByInFarm() took 566 Millis
- findByInFarm() took 561 Millis
- findByInFarm() took 586 Millis
- findByInFarm() took 588 Millis
- findByInFarm() took 546 Millis
- findByInFarm() took 3036 Millis
- findByInFarm() took 4876 Millis
- findByInFarm() took 4959 Millis
- findByInFarm() took 4904 Millis
- findByInFarm() took 4924 Millis
- findByInFarm() took 4936 Millis
The first consultations are very quick but after the tenth repetition the time increases a lot. Could someone give a hint where the possible causes of this might be?
Note: I am using Hibernate 5.2, Postgresql 9.4 and Java 8.
To try to improve a little, try to create a index in that column. But that still doesn’t answer your question.
– Don't Panic
Answer at this link, but requires translation. :(
– Don't Panic
@Everson, the chart already has an index. I believe the problem is not in Postgresql because when I run the same SELECT directly in the database (using pgAdmin), this problem does not occur.
– Rodrigo
Almost 5 seconds to execute such a simple query is very strange indeed. Besides, Hibernate uses cache of 1° level, so after the first query, it would already search in this cache the result. Try changing the jdbc driver version, see if anything changes.
– romarcio
I solved my problem, I was missing a connection pool. I followed this tutorial website and it worked. Thank you all.
– Rodrigo