@Where with or-clause is slow!

aleksabl · **Joined:** Tue Aug 08, 2006 5:00 am **Posts:** 6

I have some performance problems when using the @Where annotation. The problem occours when I'm using an or-clause where all elements are true. Example:

@OneToMany(mappedBy = "owner")
@Where(clause ="1=1 or 1=1 or 1=1")
private Set<Building> buildings;

Iterating this collection takes about half a second. Without the @Where-clause it takes less than 50ms. I've tested it several times on three different computers/platforms. In another (a bit more complex) example, the difference is 25 seconds versus 100ms.

The DEBUG-log-files are huge (several MBs) and they may contain confidential information, so it's not feasable for me to attach them.

I'm using Hibernate Annotations v3.2.0.cr1 (and hibernate 3.2.0.cr2) and PostgreSQL v8.1.4 with the 8.1-407.jdbc3 drivers.

aleksabl · **Joined:** Tue Aug 08, 2006 5:00 am **Posts:** 6

I just discovered an important detail about this bug. If I place parenthesis around the query (as in @Where(query="(1=1 or 1=1 or 1=1)"), there performance problems are gone.

Please let me know if you would like me to make a JIRA issue about it.

emmanuel · **Posted:** Thu Aug 10, 2006 10:33 pm

looks like a DB issue to me. look at the (different) generated SQL and run it. This should raise the difference

aleksabl · **Joined:** Tue Aug 08, 2006 5:00 am **Posts:** 6

It seems like a Hibernate bug to me.

Code executed:

Code:

      
Owner owner = ownerManager.getOwner(1219L);
      
      for (Building building : owner.getBuildings()) {
         System.out.println(building.getName());
      }

Produced SQL without the @Where-annotation:

Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?

Hibernate: select buildings0_.owner_id as owner11_10_, buildings0_.id as id10_, buildings0_.id as id0_9_, buildings0_.streetName as streetName0_9_, buildings0_.streetNumber as streetNu3_0_9_, (...) left outer join FdvAdministration fdvadminis9_ on buildings0_.id=fdvadminis9_.building_id where buildings0_.owner_id=? order by buildings0_.name asc

DONE.

Here is the produced SQL with the @Where(clause="1=1 OR 1=1") on owner.getBuildings()

Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select buildings0_.owner_id as owner11_10_, buildings0_.id as id10_, buildings0_.id as id0_9_, buildings0_.streetName as streetName0_9_, (...) left outer join FdvAdministration fdvadminis9_ on buildings0_.id=fdvadminis9_.building_id where 1=1 OR 1=1 and buildings0_.owner_id=? order by buildings0_.name asc

Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?
Hibernate: select owner0_.id as id24_0_, owner0_.name as name24_0_, (...)from ownertable owner0_ where owner0_.id=?

DONE.

Look at the where-clause when the annotation is used:
1=1 OR 1=1 and buildings0_.owner_id=?, which is the same as:
(1=1 OR (1=1 and buildings0_.owner_id=?) which is a tautology.

Hibernate should probably have placed parenthesis around the where-clause.

emmanuel · **Posted:** Fri Aug 11, 2006 1:55 pm

interesting, is it a where on a @ManyToMany or an @OneToMany @JoinTable?

aleksabl · **Joined:** Tue Aug 08, 2006 5:00 am **Posts:** 6

@OneToMany:

Owner:

Code:

@Entity
@AccessType(value = "field")
@Table(name = "ownertable")
public class Owner {

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE)
   private Long id;

   @OneToMany(mappedBy = "owner")
   @OrderBy("name")
    @Where(clause="1=1 OR 1=1")
   private Set<Building> buildings;

   (...)
}

Building:

Code:

@Entity
@AccessType(value = "field")
public class Building {

   @Id
   @GeneratedValue(strategy = GenerationType.SEQUENCE)
   private Long id;

   @NotNull
   @ManyToOne
   private Owner owner;

   (...)
}

emmanuel · **Posted:** Fri Aug 18, 2006 5:52 pm

This is actually a bug in Hibernate Core
http://opensource.atlassian.com/projects/hibernate/browse/HHH-2015