Hibernate version: 3.3.1.GA
Mapping documents: Using annotation (see code snips below)
Code between sessionFactory.openSession() and session.close():
Full stack trace of any exception that occurs: No exception occurs.
Name and version of the database you are using: MySQL 5.0
The generated SQL (show_sql=true): See below...
Debug level Hibernate log excerpt:
I am using annotation based mapping exclusively. I have a class "Contact" which contains a collection of "AbstractContactTarget" instances. The code for this property is as follows:
Code:
@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.LAZY)
@JoinColumn(name = "contactId")
@OrderBy("priority")
protected List<AbstractContactTarget> contactTargets = new ArrayList<AbstractContactTarget>();
The code for AbstractContactTarget is:
Code:
@Entity
@GenericGenerator(name = "hibernate-uuid", strategy = "hilo")
@Inheritance(strategy = InheritanceType.TABLE_PER_CLASS)
public abstract class AbstractContactTarget {
@Id
@GeneratedValue(generator = "hibernate-uuid")
protected long id;
protected String name;
protected boolean confirmed;
protected int priority;
@ManyToOne
@JoinColumn(name = "contactId")
protected Contact contact;
... Getter/Setter...
}
The concrete subclasses add properties, etc. All are mapped and the tables exist.
This mapping works fine except it takes around 2 seconds to load the collection when it is accessed (on a smallish database). After verifying that I had not simply left out a critical index, I looked at the generated query:
Code:
select
contacttar0_.contactId as contactId1_,
contacttar0_.id as id1_,
contacttar0_.id as id309_0_,
contacttar0_.name as name309_0_,
contacttar0_.confirmed as confirmed309_0_,
contacttar0_.priority as priority309_0_,
contacttar0_.contactId as contactId309_0_,
contacttar0_.emailAddress as emailAdd1_310_0_,
contacttar0_.phoneNumber as phoneNum1_325_0_,
contacttar0_.address as address334_0_,
contacttar0_.city as city334_0_,
contacttar0_.state as state334_0_,
contacttar0_.zipcode as zipcode334_0_,
contacttar0_.clazz_ as clazz_0_
from ( select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
3 as clazz_ from TTDContactTarget union
select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
4 as clazz_ from SMSContactTarget union
select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
6 as clazz_ from FaxContactTarget union
select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
7 as clazz_ from VoiceContactTarget union
select id,
null as phoneNumber, confirmed, emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
1 as clazz_ from EmailContactTarget union
select id,
null as phoneNumber, confirmed,
null as emailAddress, address, priority, zipcode, name, state, contactId, city,
5 as clazz_ from PostalContactTarget ) contacttar0_ where contacttar0_.contactId=?
order by contacttar0_.priority asc;
I noticed that the joined selectes inside the from clause are missing the needed where clause. See below for an example that runs in near 0 time with the where clauses:
Code:
select
contacttar0_.contactId as contactId1_,
contacttar0_.id as id1_,
contacttar0_.id as id309_0_,
contacttar0_.name as name309_0_,
contacttar0_.confirmed as confirmed309_0_,
contacttar0_.priority as priority309_0_,
contacttar0_.contactId as contactId309_0_,
contacttar0_.emailAddress as emailAdd1_310_0_,
contacttar0_.phoneNumber as phoneNum1_325_0_,
contacttar0_.address as address334_0_,
contacttar0_.city as city334_0_,
contacttar0_.state as state334_0_,
contacttar0_.zipcode as zipcode334_0_,
contacttar0_.clazz_ as clazz_0_
from ( select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
3 as clazz_
from TTDContactTarget WHERE contactId=458752 union
select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
4 as clazz_
from SMSContactTarget
==>>>>>> WHERE contactId=458752 union
select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
6 as clazz_
from FaxContactTarget
==>>>>>> WHERE contactId=458752 union
select id, phoneNumber, confirmed,
null as emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
7 as clazz_
from VoiceContactTarget
==>>>>>> WHERE contactId=458752 union
select id,
null as phoneNumber, confirmed, emailAddress,
null as address, priority,
null as zipcode, name,
null as state, contactId,
null as city,
1 as clazz_
from EmailContactTarget
==>>>>>> WHERE contactId=458752 union
select id,
null as phoneNumber, confirmed,
null as emailAddress, address, priority, zipcode, name, state, contactId, city,
5 as clazz_
from PostalContactTarget
==>>>>>> WHERE contactId=458752 ) contacttar0_
where contacttar0_.contactId=458752
order by contacttar0_.priority asc;
The query hibernate generates results in MySQL selecting every row in the 5 concrete tables. The filtering to just the ones for the contact is happening after MySQL has already done the work to muster all this data for no reason...
The resulting returned rows are correct in either case. The run time is dramatically different:
Generated: 3 rows in set (1.18 sec)
Tweaked: 3 rows in set (0.14 sec)
All I did was add a where clause with contactId=? to each of the unioned selects.
Anyone got any idea how to fix this or work around it?
Login
FAQ
Search