Hibernate union-subclass (table per concrete class) mapping generator of "increment" very slow?
My Hibernate hbm file looks something like this with a mysql DB:
<hibernate-mapping>
<class name="com.company.common.bo.position.Parent" table="Parents"
abstract="true">
<id name="id">
<generator class="increment" />
</id>
<property name="date" not-null="true" />
<property name="milliseconds" not-null="true" />
<property name="shares">
<column name="shares" precision="19" scale="6" not-null="true" />
</property>
<many-to-one name="ticker" column="tickerID" not-null="true" index="_tickerID_date_milliseconds_idx" />
<many-to-one name="auditTrail" column="auditTrailID"
not-null="false" cascade="save-update" lazy="false" fetch="select" />
<union-subclass name="com.company.common.bo.position.开发者_如何学运维SubclassA"
table="SubclassAs">
<many-to-one name="account" column="accountID" not-null="true" foreign-key="SubclassA_accountID_fk" />
<many-to-one name="portfolio">
<column name="portfolioID" not-null="true"/>
</many-to-one>
<many-to-one name="individualTrade">
<column name="individualTradeID" not-null="false"/>
</many-to-one>
<many-to-one name="positionTransfer" column="positionTransferID"
cascade="save-update" not-null="false"/>
</union-subclass>
<union-subclass
name="com.company.common.bo.position.SubclassB" table="SubclassBs">
<many-to-one name="individualTrade">
<column name="individualTradeID" not-null="false" />
</many-to-one>
<many-to-one name="account" column="accountID" not-null="true" foreign-key="SubclassBs_accountID_fk"/>
<many-to-one name="internalExecution" column="executionID"
cascade="save-update" not-null="false" />
</union-subclass>
<union-subclass name="com.company.common.bo.position.SubclassC"
table="SubclassCs">
</union-subclass>
</class>
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:
select max(ids_.id) from ( select id from SubclassAs union select id from SubclassBs union select id from SubclassCs ) ids_
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.
If i do something like this:
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_
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.
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?
Thanks
If increment
doesn't satisfy you, you can use some other generator strategy, and, since MySQL doesn't support sequences, the next suitable option is a hilo
strategy.
精彩评论