Queries slow down after an entity is loaded in session

alexandermalfait · **Joined:** Tue Nov 24, 2009 12:20 pm **Posts:** 4

I'm using Hibernate EntityManager, and am experiencing a weird slowdown in my Hibernate queries. Take a look at this code:

Code:

    public void testQuerySpeed() {
        for(int i = 0; i < 1000; i++) {
            em.createNativeQuery("SELECT 1").getResultList();
        }
    }

This runs in about 750ms on my machine. Not blazing fast considering it's just selecting a constant integer, but acceptable. My problem arises the moment ANY entities are loaded in my EntityManager session before I launch my query:

Code:

    public void testQuerySpeed() {
        CommercialContact contact = em.find(CommercialContact.class, 1890871l);

        for(int i = 0; i < 1000; i++) {
            em.createNativeQuery("SELECT 1").getSingleResult();
        }
    }

The em.find() is fast, but the runtime 1000 queries increased more than ten-fold, to about 10 seconds. If I put an `em.clear()` after the `em.find()`, the problem goes away again and runtime goes back to 750ms.

I've used a native query here, but the problem exists with HQL queries as well. It seems ALL queries take at least 70ms whenever an entity is in the EntityManager session.

This performance drop is really hurting us when generating lists where n+1 queries are needed.

I've tested the latest Hibernate 3.5 beta, and have the exact same problem. Has anyone seen this problem, or any ideas on how to fix it?

I'm using PostgreSQL 8.3, using resource local transactions (running in Tomcat). Using the built-in connection pool, but using C3P0 made no difference.

mmerder · **Posted:** Tue Nov 24, 2009 12:36 pm

Never had this issue. How do you measure performance?

alexandermalfait · **Joined:** Tue Nov 24, 2009 12:20 pm **Posts:** 4

mmerder wrote:

Never had this issue. How do you measure performance?

Just using System.currentTimeMillis(). The runtime increases ten-fold, so I don't think it's a measuring issue..

mmerder · **Posted:** Wed Nov 25, 2009 4:42 am

Well, I can't see any code which does the measuring in the method you posted. Do you measure the while method-execution? Then you include the em.find...

alexandermalfait · **Joined:** Tue Nov 24, 2009 12:20 pm **Posts:** 4

I'd snipped the benchmarking code.. Here it is:

Code:

    public void testQuerySpeed() {
        CommercialContact contact = em().find(CommercialContact.class, 1890871l);

        Benchmark benchmark = new Benchmark("Selecting one 1000 times");

        for(int i = 0; i < 1000; i++) {
            em().createNativeQuery("SELECT 1").getSingleResult();
        }

        benchmark.finish();
    }

Code to Benchmark:

Code:

public class Benchmark {

    private final String name;
    private long startedAt;

    private Logger logger = Logger.getLogger("BENCHMARK");

    public Benchmark(String name) {
        this.name = name;
        
        start();
    }

    private void start() {
        startedAt = System.currentTimeMillis();
        
        logger.info("[BENCHMARK] " + name + " - started");
    }
    
    public void finish() {
        long time = getElapsedTime();
        
        logger.info("[BENCHMARK] " + name + " - finished in " + time + "ms");
    }

    public long getElapsedTime() {
        return System.currentTimeMillis() - startedAt;
    }
}

alexandermalfait · **Joined:** Tue Nov 24, 2009 12:20 pm **Posts:** 4

It turns out I was running in automatic flush mode, and the flush check was causing the slowdown. Setting my entityManager to FlushModeType.COMMIT fixes my problem.

More information in this StackOverflow thread