[hibernate-issues] [Hibernate-JIRA] Created: (HHH-6314) UnionSubclassEntityPersister inefficient with unioned results in subquery

[Jon Todd (JIRA)]

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.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira