How can I reduce the index size of a hibernate collection (set with composite-element)?

Question

I need to reduce index-sizes because of mssql limit of 900 bytes.

I have a class which has a collection declared as a set. Because of this, the primary key consists of all notnull columns including the foreign key. An index is created out of this primary key. I do not need the index to be over all these columns.

Is there a way to reduce the index size without changing the whole setup of the data structure?

Here is the current configuration of the collection inside of the surrounding class definition:

  <set cascade="save-update,persist,merge,refresh,replicate,evict,delete,delete-orphan" fetch="select" lazy="true" table="mySubsetTable" batch-size="1000" name="attributes">
    <key foreign-key="FK_Mothertable">
      <column name="number"/>
      <column name="data"/>
    </key>
    <composite-element class="MySubsetElement">
      <property name="type" length="200" not-null="true" type="class"/>
      <property name="attribute" length="2000" column="attrValue" not-null="false"/>
      <property name="myboolean" type="boolean">
        <column name="myboolean"/>
      </property>
      <property name="anotherAttribute" length="200"/>
      <property name="evenAnotherAttribute" length="200" not-null="true"/>
      <property name="evenOneMoreAttribute" not-null="true">
        <type name="SomeClass">
          <param name="enumClass">someEnumClass</param>
        </type>
      </property>
    </composite-element>
  </set>

I am currently using hibernate 3.3.1 with xdoclet annotations:

  /**
   * Attributes of this matchable
   * 
   * @hibernate.set table="mySubsetTable" cascade="save-update,persist,merge,refresh,replicate,evict,delete,delete-orphan" lazy="true"
   *                batch-size="1000" fetch="select"
   * @hibernate.key foreign-key="FK_Mothertable"
   * @hibernate.key-column name="number"
   * @hibernate.key-column name="data"
   * @hibernate.composite-element class="MySubsetElement"
   */
   public Set<MySubsetElement> getSubsetElements() { ... }

Thanks a lot for your suggestions!

(And please do not refer me to http://docs.jboss.org/hibernate/ I already found this.)

EDIT I cannot reduce the size of all properties to fit the size limits. An index consisting of the foreign keys would suffice. Also I would really like a solution which does not change the underlying datastructure as I am working on a product which is already in use.

You're using `composite elements`. Is that required? Why not make "first class citizen" entities of `MySubsetElement` and use a normal `OneToMany` relationship? — JimmyB, May 06 '16 at 11:40
I have a huge structure here and somebody who came before me someday defined it that way... If there is a way to do this differently I want to here it and evalutate later if it can be applied to my case. Thank you for your suggestions! — emi-le, May 06 '16 at 11:43
Would this mean, configuring "MySubset" as an entity and making a one-to-many relation in the set? — emi-le, May 06 '16 at 11:45

score 1 · Answer 1 · answered May 06 '16 at 11:52

1

You are using composite elements for your set. This may really be the 'right' way, because all MySubsetElements depend on their owner, but it also has implications on the relational model as you're witnessing now.

I'd suggest something like the following approach (I'm using annotations, you may want to translate that to your mapping configuration):

@Entity
class MySubsetElement {

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;

  @ManyToOne(optional=false)
  private MyParentElement owner;

  public MySubsetElement( MyParentElement owner ) {
    ...
  }

}

and

@Entity
public class MyParentElement {

  @Id
  @GeneratedValue(strategy=GenerationType.AUTO)
  private Long id;

  @OneToMany(mappedBy="owner", cascade={CascadeType.ALL})
  private Set<MySubsetElement> children;

}

answered May 06 '16 at 11:52

JimmyB

12,101
2
28
44

Here the problem is, that the Subset needs to know the Parent when creating it, I think. But your answer pointed me in the right direction and I found something in the [jboss documentation](http://docs.jboss.org/hibernate/orm/3.5/reference/en/html/collections.html#collections-indexed "collections-indexed") about uni-directional mappings which I will try out. Thanks – emi-le May 06 '16 at 12:13
"Here the problem is..." - Why would that be a problem? The parent must exist prior to the subset, and to put the subset into the parent the parent must also be at hand. Usually not a problem. – JimmyB May 06 '16 at 12:22
Hi I realized it, by adding a not-null constraint on the set. It works now. I am still not 100% happy, because the subset table now has an additional column, but that's life. Thanks again. – emi-le May 11 '16 at 13:36

score 0 · Accepted Answer · answered May 11 '16 at 13:51

here is how I realized Jimmy's suggestion:

<hibernate-mapping>
    <class name="MyParent" ....>
      ...
      <set cascade="save-update,persist,merge,refresh,replicate,evict,delete,delete-orphan" fetch="select" lazy="true" table="SubsetTable" batch-size="1000" name="attributes">
        <key foreign-key="FK_ParentTable" not-null="true">
          <column name="number"/>
          <column name="data"/>
        </key>
        <one-to-many class="MySubset" entity-name="MySubsetentity"/>
      </set>
      ...
    </class>

    <class name="MySubset" ....>
          <id name="id" type="long">
            <column name="id"/>
            <generator class="MyIdGeneratorClass">
              <param name="sequence">mySequence</param>
            </generator>
          </id>
          <property name="type" length="200" not-null="true" type="class"/>
          <property name="attribute" length="2000" column="attrValue" not-null="false"/>
          <property name="myboolean" type="boolean">
            <column name="myboolean"/>
          </property>
          <property name="anotherAttribute" length="200"/>
          <property name="evenAnotherAttribute" length="200" not-null="true"/>
          <property name="evenOneMoreAttribute" not-null="true">
            <type name="SomeClass">
              <param name="enumClass">someEnumClass</param>
            </type>
          </property>   
    </class>
</hibernate-mapping>

The important part is the not-null="true" inside the key tag of the Parent subset definition. This enables the Subset to remain ignorant of the parent.

How can I reduce the index size of a hibernate collection (set with composite-element)?

2 Answers2