title: Hibernate union-subclass (table per concrete class) mapping generator of "increment" very slow?
body: <p>My Hibernate hbm file looks something like this with a mysql DB:</p>
  
  <pre><code>&lt;hibernate-mapping&gt;
  &lt;class name="com.company.common.bo.position.Parent" table="Parents"
      abstract="true"&gt;
      &lt;id name="id"&gt;
          &lt;generator class="increment" /&gt;
      &lt;/id&gt;
      &lt;property name="date" not-null="true" /&gt;
      &lt;property name="milliseconds" not-null="true" /&gt;
      &lt;property name="shares"&gt;
          &lt;column name="shares" precision="19" scale="6" not-null="true" /&gt;
      &lt;/property&gt;
      &lt;many-to-one name="ticker" column="tickerID" not-null="true" index="_tickerID_date_milliseconds_idx" /&gt;
      &lt;many-to-one name="auditTrail" column="auditTrailID"
          not-null="false" cascade="save-update" lazy="false" fetch="select" /&gt;
  
      &lt;union-subclass name="com.company.common.bo.position.SubclassA"
          table="SubclassAs"&gt;
          &lt;many-to-one name="account" column="accountID" not-null="true" foreign-key="SubclassA_accountID_fk" /&gt;
          &lt;many-to-one name="portfolio"&gt;
              &lt;column name="portfolioID" not-null="true"/&gt;
          &lt;/many-to-one&gt;
          &lt;many-to-one name="individualTrade"&gt;
              &lt;column name="individualTradeID" not-null="false"/&gt;
          &lt;/many-to-one&gt;  
          &lt;many-to-one name="positionTransfer" column="positionTransferID"
                  cascade="save-update" not-null="false"/&gt;
      &lt;/union-subclass&gt;
  
      &lt;union-subclass
              name="com.company.common.bo.position.SubclassB" table="SubclassBs"&gt;
          &lt;many-to-one name="individualTrade"&gt;
              &lt;column name="individualTradeID" not-null="false" /&gt;
          &lt;/many-to-one&gt;  
          &lt;many-to-one name="account" column="accountID" not-null="true" foreign-key="SubclassBs_accountID_fk"/&gt;
          &lt;many-to-one name="internalExecution" column="executionID"
                  cascade="save-update" not-null="false" /&gt;
      &lt;/union-subclass&gt;       
  
      &lt;union-subclass name="com.company.common.bo.position.SubclassC"
          table="SubclassCs"&gt;
      &lt;/union-subclass&gt;
  &lt;/class&gt;
  </code></pre>
  
  <p></p>
  
  <p>So basically i have an abstract class Parent and 3 subclasses (SubclassA, B, C) that extend it. In the database there are 3 tables (for the 3 subclasses). The id generator is "increment" because the union subclass mapping doesn't allow me to use native. So it looks like with increment, the ID is unique among the 3 tables. When I look at the hibernate sql, it basically finds the max ID from all 3 tables, and uses that as the next ID. But the query it uses seems very inefficient. This is what I see it doing:</p>
  
  <pre><code>select max(ids_.id) from ( select id from SubclassAs union select id from SubclassBs union select id from SubclassCs ) ids_ 
  </code></pre>
  
  <p>Which takes over 12 seconds to run. Each of those tables has more than a million records each. It's unioning every single ID together and then selecting the max out of that.</p>
  
  <p>If i do something like this:</p>
  
  <pre><code>select max(ids_.id) from ( select max(id) as id from SubclassAs union select max(id) as id from SubclassBs union select max(id) as id from SubclassCs ) ids_
  </code></pre>
  
  <p>It is much faster, less than one millisecond, because the inner union only gets the max from each table, and then i select just the max out of those 3 records.</p>
  
  <p>Is there a way to tell hibernate to do this instead, or is there a better way of using a generator for the ID across these 3 tables?</p>
  
  <p>Thanks</p>