Performance problem with LEFT JOIN

toni May 11, 2007 10:05 AM

Hi,

I have a LEFT JOIN, which looks like this:


entityManager.createQuery("SELECT t FROM Template t LEFT JOIN t.messages c WHERE t.client.hostname=:ch GROUP BY t.id, t.subject, t.text, t.client ORDER BY count(c)").setParameter("ch", client.getHostname()).getResultList();

The problem is that the larger the table grow the poorer the performance is. After a while it takes about 3-4 seconds to execute.

Any ideas how I could optimize this?

1. Re: Performance problem with LEFT JOIN

proudchief May 14, 2007 10:42 AM (in response to toni)

I don't see a join clause in your query. Without a join clause, you will get a cartesian product, and the performance will degrade exponentially as the tables grow.

You need something like "Template.id = message.id" to avoid a cartesian product.

You can see examples of what I'm talking about here:
http://www.w3schools.com/sql/sql_join.asp
Actions