[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6314) UnionSubclassEntityPersister inefficient with unioned results in subquery
UnionSubclassEntityPersister inefficient with unioned results in subquery
-------------------------------------------------------------------------
Key: HHH-6314
URL: http://opensource.atlassian.com/projects/hibernate/browse/HHH-6314
Project: Hibernate Core
Issue Type: Improvement
Components: core
Affects Versions: 3.6.5
Environment: mysql, linux, hibernate 3.2.0
Reporter: Jon Todd
*Background*
For hibernate queries (both HQL and criteria) where the entity being selected from is a
superclass to a number of other concrete entities configured with table per concrete
class, it appears that rather than applying the WHERE criteria to each unioned table,
hibernate instead selects all results from all concrete tables in a subquery and then
applies the WHERE criteria on the unioned result. Any realistic data sizes this is
needlessly very inefficient.
*Concrete Example*
Consider we have the following entities: _BaseUser_, _HumanUser_, _AgentUser_ where the
latter two classes extend _BaseUser_ with table per concrete class configuration.
When running a simple HQL query like:
{code:sql}
FROM BaseUser WHERE id = "1234"
{code}
The generated SQL looks something like this:
{code:sql}
SELECT this_.id, this_.userName, this_.agentName
FROM (
SELECT id, userName, null as agentName FROM HumanUser
UNION
SELECT id, null as userName, agentName FROM AgentUser
) this_
WHERE
this_.id = "1234";
{code}
Would have expected the generated SQL to be:
{code:sql}
SELECT this_.id, this_.userName, this_.agentName
FROM (
SELECT id, userName, null as agentName FROM HumanUser WHERE id = "1234"
UNION
SELECT id, null as userName, agentName FROM AgentUser WHERE id = "1234"
) this_
{code}
In the former case, this ends up creating a temp table that contains the combination of
all data from every concrete instance, in the latter case it would create a temp table
with a single row.
This implementation which appears to live in
_org.hibernate.persister.entity.UnionSubclassEntityPersister. generateSubquery()_ has
rendered the use of table per concrete class inheritance unusable in our application. Is
there any short term solution?
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://opensource.atlassian.com/projects/hibernate/secure/Administrators....
-
For more information on JIRA, see: http://www.atlassian.com/software/jira