Undocumented batch-size behaviour (smallBatchSize)

andrewom · **Posted:** Wed Mar 02, 2005 11:03 pm

Hi,

I'm fairly new to Hibernate and am having issues with Hibernate generating what are to me unnecessary sql statements.

It occurs when the retrieving collections of either components or associations and the use of "batch-size" option. The "batch-size" parameter does not work as documented. Internally, Hibernate generates a smallBatchSize which is the square root of the batch size.

The algorithm for retrieving a batch is then:
1. If the batch to retrieve is smaller than "smallBatchSize", retrieve one row at a time.
2. else if batch is smaller than "batch-size", retrieve "smallBatchSize" at a time
3. else if the batch is greater than "batch-size", retireve a "batch-size" items.

This algortihm is pretty much duplicated in BatchingEntityLoader and BatchingCollectionInitializer.

Why this algorithm? This generates a very large number of selects.

For example, if batch size is set to 100 and there are 79 rows to retrieve, Hibernate will generate 16 select statements to retrieve the rows. (As 79 is less than 100, it retrives in batches of the sqrt of 100 (10) to fetch 70 rows, then the last 9 individually).

What is the purpose of this algorithm? Can it be changed?

I've done a hack myself to BatchingCollectionInitializer to create a new Loader instance to load all rows when there is less than a whole batch remaining. The overhead of creating a new Loader seems insignificant compared to additional SQL statements. (The hack returns the results in a single select in the above example).

What I'm confused about is why the existing algorithm was implemented. Am I missing something fundamental? If not, will something along the lines of my hack be implemented soon?

Given my lack of experience with Hibernate, I'm not keen to patch it, but can't really live with it as is. Hopefully, I'm missing something.

Regards,
Andrew

Notes about the ouptut: the number of rows returned from the query is only 79. There are 2 collections, one of components (custom fields) and the other is a one-to-many association of links.

Hibernate version:
2.1.8

Mapping documents:

Code:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
    <class
        name="somepackage.Ticket"
        table="Tickets"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
        optimistic-lock="version"
    >

        <id
            name="id"
            column="id"
            type="java.lang.Integer"
            access="property"
        >
            <generator class="assigned">
              <!--
                  To add non XDoclet generator parameters, create a file named
                  hibernate-generator-params-Ticket.xml
                  containing the additional parameters and place it in your merge dir.
              -->
            </generator>
        </id>

        <property
            name="effectiveId"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="EffectiveId"
            length="11"
            not-null="true"
        />

        <property
            name="queue"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Queue"
            length="11"
            not-null="true"
        />

        <property
            name="type"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Type"
            length="16"
        />

        <property
            name="issueStatement"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="IssueStatement"
            length="11"
            not-null="true"
        />

        <property
            name="resolution"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Resolution"
            length="11"
            not-null="true"
        />

        <property
            name="owner"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Owner"
            length="11"
            not-null="true"
        />

        <property
            name="subject"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Subject"
            length="200"
        />

        <property
            name="initialPriority"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="InitialPriority"
            length="11"
            not-null="true"
        />

        <property
            name="finalPriority"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="FinalPriority"
            length="11"
            not-null="true"
        />

        <property
            name="priority"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="Priority"
            length="11"
            not-null="true"
        />

        <property
            name="timeEstimated"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="TimeEstimated"
            length="11"
            not-null="true"
        />

        <property
            name="timeWorked"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="TimeWorked"
            length="11"
            not-null="true"
        />

        <property
            name="status"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Status"
            length="10"
        />

        <property
            name="timeLeft"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="TimeLeft"
            length="11"
            not-null="true"
        />

        <property
            name="told"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Told"
            length="19"
        />

        <property
            name="starts"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Starts"
            length="19"
        />

        <property
            name="started"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Started"
            length="19"
        />

        <property
            name="due"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Due"
            length="19"
        />

        <property
            name="resolved"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Resolved"
            length="19"
        />

        <property
            name="lastUpdatedBy"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="LastUpdatedBy"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdated"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="LastUpdated"
            length="19"
        />

        <property
            name="creator"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Creator"
            length="11"
            not-null="true"
        />

        <property
            name="created"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Created"
            length="19"
        />

        <property
            name="disabled"
            type="boolean"
            update="true"
            insert="true"
            access="property"
            column="Disabled"
            not-null="true"
        />

        <map
            name="customFields"
            table="TicketCustomFieldValues"
            lazy="false"
            sort="unsorted"
            inverse="false"
            cascade="none"
            access="property"
            batch-size="100"
        >

              <key
                  column="Ticket"
              >
              </key>

              <index
                  column="id"
                  type="integer"
              />

              <composite-element
                  class="somepackage.TicketCustomFieldValue"
              >
        <property
            name="customField"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="CustomField"
            length="11"
            not-null="true"
        />

        <property
            name="content"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Content"
            length="255"
        />

        <property
            name="creator"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Creator"
            length="11"
            not-null="true"
        />

        <property
            name="created"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Created"
            length="19"
        />

        <property
            name="lastUpdatedBy"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="LastUpdatedBy"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdated"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="LastUpdated"
            length="19"
        />

              </composite-element>

        </map>

        <set
            name="links"
            lazy="false"
            inverse="false"
            cascade="none"
            sort="unsorted"
            access="property"
            batch-size="100"
        >

              <key
                  column="LocalBase"
              >
              </key>

              <one-to-many
                  class="somepackage.Link"
              />

        </set>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Ticket.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

Code:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
    <class
        name="somepackage.Link"
        table="Links"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
        optimistic-lock="version"
        batch-size="50"
    >

        <id
            name="id"
            column="id"
            type="java.lang.Integer"
            access="property"
        >
            <generator class="assigned">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-Link.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <property
            name="base"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Base"
            length="240"
        />

        <property
            name="target"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Target"
            length="240"
        />

        <property
            name="type"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Type"
            length="20"
            not-null="true"
        />

        <property
            name="localTarget"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="LocalTarget"
            length="11"
            not-null="true"
        />

        <property
            name="localBase"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="LocalBase"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdatedBy"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="LastUpdatedBy"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdated"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="LastUpdated"
            length="19"
        />

        <property
            name="creator"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="Creator"
            length="11"
            not-null="true"
        />

        <property
            name="created"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Created"
            length="19"
        />

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Link.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

                    List tickets = session.createCriteria(Ticket.class)
                    .add(Expression.in("status", new String[] {"new", "open", "stalled", "deleted"}))
                    .createCriteria("links").add(Expression.eq("target", "snbs://" + snbsCustomerId)).list();

Full stack trace of any exception that occurs:
N/A

Name and version of the database you are using:
Postgres 8.0.1

The generated SQL (show_sql=true):
Hibernate: select this.id as id1_, this.EffectiveId as Effectiv2_1_, this.Queue as Queue1_, this.Type as Type1_, this.IssueStatement as IssueSta5_1_, this.Resolution as Resolution1_, this.Owner as Owner1_, this.Subject as Subject1_, this.InitialPriority as InitialP9_1_, this.FinalPriority as FinalPr10_1_, this.Priority as Priority1_, this.TimeEstimated as TimeEst12_1_, this.TimeWorked as TimeWorked1_, this.Status as Status1_, this.TimeLeft as TimeLeft1_, this.Told as Told1_, this.Starts as Starts1_, this.Started as Started1_, this.Due as Due1_, this.Resolved as Resolved1_, this.LastUpdatedBy as LastUpd21_1_, this.LastUpdated as LastUpd22_1_, this.Creator as Creator1_, this.Created as Created1_, this.Disabled as Disabled1_, x0_.id as id0_, x0_.Base as Base0_, x0_.Target as Target0_, x0_.Type as Type0_, x0_.LocalTarget as LocalTar5_0_, x0_.LocalBase as LocalBase0_, x0_.LastUpdatedBy as LastUpda7_0_, x0_.LastUpdated as LastUpda8_0_, x0_.Creator as Creator0_, x0_.Created as Created0_ from Tickets this inner join Links x0_ on this.id=x0_.LocalBase where this.Status in (?, ?, ?, ?) and x0_.Target=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?

Debug level Hibernate log excerpt:
These statements show how "smallBatchSize" is being used
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields

andrewom · **Posted:** Sun Mar 06, 2005 7:10 pm

Should I post this query to the dev mailing list instead of the forum?

JoseRa · **Joined:** Wed Mar 09, 2005 8:58 am **Posts:** 5

Hi,

I agree with you. Documentation is confusing about this.

I have posted a similar question in this forum:

http://forum.hibernate.org/viewtopic.php?t=939701&sid=49fc787994e28437b6a3658894ce757b

¿Do you know this behaviour is similar in Hibernate v3? ¿How do you fixed it?

Thanks a lot.

andrewom wrote:

Hi,

I'm fairly new to Hibernate and am having issues with Hibernate generating what are to me unnecessary sql statements.

It occurs when the retrieving collections of either components or associations and the use of "batch-size" option. The "batch-size" parameter does not work as documented. Internally, Hibernate generates a smallBatchSize which is the square root of the batch size.

The algorithm for retrieving a batch is then:
1. If the batch to retrieve is smaller than "smallBatchSize", retrieve one row at a time.
2. else if batch is smaller than "batch-size", retrieve "smallBatchSize" at a time
3. else if the batch is greater than "batch-size", retireve a "batch-size" items.

This algortihm is pretty much duplicated in BatchingEntityLoader and BatchingCollectionInitializer.

Why this algorithm? This generates a very large number of selects.

For example, if batch size is set to 100 and there are 79 rows to retrieve, Hibernate will generate 16 select statements to retrieve the rows. (As 79 is less than 100, it retrives in batches of the sqrt of 100 (10) to fetch 70 rows, then the last 9 individually).

What is the purpose of this algorithm? Can it be changed?

I've done a hack myself to BatchingCollectionInitializer to create a new Loader instance to load all rows when there is less than a whole batch remaining. The overhead of creating a new Loader seems insignificant compared to additional SQL statements. (The hack returns the results in a single select in the above example).

What I'm confused about is why the existing algorithm was implemented. Am I missing something fundamental? If not, will something along the lines of my hack be implemented soon?

Given my lack of experience with Hibernate, I'm not keen to patch it, but can't really live with it as is. Hopefully, I'm missing something.

Regards,
Andrew

Notes about the ouptut: the number of rows returned from the query is only 79. There are 2 collections, one of components (custom fields) and the other is a one-to-many association of links.

Hibernate version:
2.1.8

Mapping documents:

Code:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
    <class
        name="somepackage.Ticket"
        table="Tickets"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
        optimistic-lock="version"
    >

        <id
            name="id"
            column="id"
            type="java.lang.Integer"
            access="property"
        >
            <generator class="assigned">
              <!--
                  To add non XDoclet generator parameters, create a file named
                  hibernate-generator-params-Ticket.xml
                  containing the additional parameters and place it in your merge dir.
              -->
            </generator>
        </id>

        <property
            name="effectiveId"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="EffectiveId"
            length="11"
            not-null="true"
        />

        <property
            name="queue"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Queue"
            length="11"
            not-null="true"
        />

        <property
            name="type"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Type"
            length="16"
        />

        <property
            name="issueStatement"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="IssueStatement"
            length="11"
            not-null="true"
        />

        <property
            name="resolution"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Resolution"
            length="11"
            not-null="true"
        />

        <property
            name="owner"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Owner"
            length="11"
            not-null="true"
        />

        <property
            name="subject"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Subject"
            length="200"
        />

        <property
            name="initialPriority"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="InitialPriority"
            length="11"
            not-null="true"
        />

        <property
            name="finalPriority"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="FinalPriority"
            length="11"
            not-null="true"
        />

        <property
            name="priority"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="Priority"
            length="11"
            not-null="true"
        />

        <property
            name="timeEstimated"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="TimeEstimated"
            length="11"
            not-null="true"
        />

        <property
            name="timeWorked"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="TimeWorked"
            length="11"
            not-null="true"
        />

        <property
            name="status"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Status"
            length="10"
        />

        <property
            name="timeLeft"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="TimeLeft"
            length="11"
            not-null="true"
        />

        <property
            name="told"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Told"
            length="19"
        />

        <property
            name="starts"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Starts"
            length="19"
        />

        <property
            name="started"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Started"
            length="19"
        />

        <property
            name="due"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Due"
            length="19"
        />

        <property
            name="resolved"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Resolved"
            length="19"
        />

        <property
            name="lastUpdatedBy"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="LastUpdatedBy"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdated"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="LastUpdated"
            length="19"
        />

        <property
            name="creator"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Creator"
            length="11"
            not-null="true"
        />

        <property
            name="created"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Created"
            length="19"
        />

        <property
            name="disabled"
            type="boolean"
            update="true"
            insert="true"
            access="property"
            column="Disabled"
            not-null="true"
        />

        <map
            name="customFields"
            table="TicketCustomFieldValues"
            lazy="false"
            sort="unsorted"
            inverse="false"
            cascade="none"
            access="property"
            batch-size="100"
        >

              <key
                  column="Ticket"
              >
              </key>

              <index
                  column="id"
                  type="integer"
              />

              <composite-element
                  class="somepackage.TicketCustomFieldValue"
              >
        <property
            name="customField"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="CustomField"
            length="11"
            not-null="true"
        />

        <property
            name="content"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Content"
            length="255"
        />

        <property
            name="creator"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="Creator"
            length="11"
            not-null="true"
        />

        <property
            name="created"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Created"
            length="19"
        />

        <property
            name="lastUpdatedBy"
            type="java.lang.Integer"
            update="true"
            insert="true"
            access="property"
            column="LastUpdatedBy"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdated"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="LastUpdated"
            length="19"
        />

              </composite-element>

        </map>

        <set
            name="links"
            lazy="false"
            inverse="false"
            cascade="none"
            sort="unsorted"
            access="property"
            batch-size="100"
        >

              <key
                  column="LocalBase"
              >
              </key>

              <one-to-many
                  class="somepackage.Link"
              />

        </set>

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Ticket.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

Code:

<?xml version="1.0" encoding="UTF-8"?>

<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN" 
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping
>
    <class
        name="somepackage.Link"
        table="Links"
        dynamic-update="false"
        dynamic-insert="false"
        select-before-update="false"
        optimistic-lock="version"
        batch-size="50"
    >

        <id
            name="id"
            column="id"
            type="java.lang.Integer"
            access="property"
        >
            <generator class="assigned">
              <!--  
                  To add non XDoclet generator parameters, create a file named 
                  hibernate-generator-params-Link.xml 
                  containing the additional parameters and place it in your merge dir. 
              --> 
            </generator>
        </id>

        <property
            name="base"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Base"
            length="240"
        />

        <property
            name="target"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Target"
            length="240"
        />

        <property
            name="type"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="Type"
            length="20"
            not-null="true"
        />

        <property
            name="localTarget"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="LocalTarget"
            length="11"
            not-null="true"
        />

        <property
            name="localBase"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="LocalBase"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdatedBy"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="LastUpdatedBy"
            length="11"
            not-null="true"
        />

        <property
            name="lastUpdated"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="LastUpdated"
            length="19"
        />

        <property
            name="creator"
            type="int"
            update="true"
            insert="true"
            access="property"
            column="Creator"
            length="11"
            not-null="true"
        />

        <property
            name="created"
            type="java.util.Date"
            update="true"
            insert="true"
            access="property"
            column="Created"
            length="19"
        />

        <!--
            To add non XDoclet property mappings, create a file named
                hibernate-properties-Link.xml
            containing the additional properties and place it in your merge dir.
        -->

    </class>

</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

                    List tickets = session.createCriteria(Ticket.class)
                    .add(Expression.in("status", new String[] {"new", "open", "stalled", "deleted"}))
                    .createCriteria("links").add(Expression.eq("target", "snbs://" + snbsCustomerId)).list();

Full stack trace of any exception that occurs:
N/A

Name and version of the database you are using:
Postgres 8.0.1

The generated SQL (show_sql=true):
Hibernate: select this.id as id1_, this.EffectiveId as Effectiv2_1_, this.Queue as Queue1_, this.Type as Type1_, this.IssueStatement as IssueSta5_1_, this.Resolution as Resolution1_, this.Owner as Owner1_, this.Subject as Subject1_, this.InitialPriority as InitialP9_1_, this.FinalPriority as FinalPr10_1_, this.Priority as Priority1_, this.TimeEstimated as TimeEst12_1_, this.TimeWorked as TimeWorked1_, this.Status as Status1_, this.TimeLeft as TimeLeft1_, this.Told as Told1_, this.Starts as Starts1_, this.Started as Started1_, this.Due as Due1_, this.Resolved as Resolved1_, this.LastUpdatedBy as LastUpd21_1_, this.LastUpdated as LastUpd22_1_, this.Creator as Creator1_, this.Created as Created1_, this.Disabled as Disabled1_, x0_.id as id0_, x0_.Base as Base0_, x0_.Target as Target0_, x0_.Type as Type0_, x0_.LocalTarget as LocalTar5_0_, x0_.LocalBase as LocalBase0_, x0_.LastUpdatedBy as LastUpda7_0_, x0_.LastUpdated as LastUpda8_0_, x0_.Creator as Creator0_, x0_.Created as Created0_ from Tickets this inner join Links x0_ on this.id=x0_.LocalBase where this.Status in (?, ?, ?, ?) and x0_.Target=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where ((links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?) or (links0_.LocalBase=?))
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where ((customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?) or (customfiel0_.Ticket=?))
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?
Hibernate: select links0_.LocalBase as LocalBase__, links0_.id as id__, links0_.id as id0_, links0_.Base as Base0_, links0_.Target as Target0_, links0_.Type as Type0_, links0_.LocalTarget as LocalTar5_0_, links0_.LocalBase as LocalBase0_, links0_.LastUpdatedBy as LastUpda7_0_, links0_.LastUpdated as LastUpda8_0_, links0_.Creator as Creator0_, links0_.Created as Created0_ from Links links0_ where links0_.LocalBase=?
Hibernate: select customfiel0_.Ticket as Ticket__, customfiel0_.CustomField as CustomFi2___, customfiel0_.Content as Content__, customfiel0_.Creator as Creator__, customfiel0_.Created as Created__, customfiel0_.LastUpdatedBy as LastUpda6___, customfiel0_.LastUpdated as LastUpda7___, customfiel0_.id as id__ from TicketCustomFieldValues customfiel0_ where customfiel0_.Ticket=?

Debug level Hibernate log excerpt:
These statements show how "smallBatchSize" is being used
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.links
DEBUG [main] BatchingCollectionInitializer: batch loading collection role (small batch): somepackage.Ticket.customFields

andrewom · **Posted:** Wed Mar 09, 2005 8:52 pm

I've just had a look at the 3.0rc1 source and it is not fixed. The algorithm has been improved, however.

Instead of having just having 3 batch sizes of "batch-size", sqrt(batch-size) and 1, it now creates an array of batch sizes. Batch sizes are allocated by dividing recursively the batch-size by 2 (with a minor adjustment).

e.g. If your batch size is 100, it will create the following batch sizes:
100, 50, 25, 12, 6, 4, 3, 2, 1.

So now, if your query returns 98 rows it will perform the following selects:
Select 50 (48 remaining)
Select 25 (23 remaining)
Select 12 (11 remaining)
Select 6 (5 remaining)
Select 4 (1 remaining)
Select 1

Still 6 queuries, when I'd argue with a "batch-size" of 100, it should be 1. Naturally, the number of queries depends on how neatly it fits into the batch sizes created.

This isn't a bug as such, but it still seems broken to me

Anyway, the fix I applied in 2.1.8 (and is similar in 3.0rc1) is to modify BatchingCollectionInitializer (you'll need to modify BatchingEntityLoader as well if you specifiy "batch-size" at the entity level).

The fix creates a new batch loader instance if the remaining rows is less than the batch size.

In pseudo-code:

Code:

  if (remainingBatchSize < batchSize)
      if (batchLoader instanceof CollectionLoader)
           new CollectionLoader(remaingBatchSize).loadBatch(...)
      else if (batchLoader instanceof OneToManyLoader)
            new OneToManyLoader(remainingBatchSize).loadBatch(...)
  else
      batchLoader.loadBatch(...)

The existing Hibernate code seems to go to great lengths to avoid creating new batch loader instances. However, it my testing, the overhead of this is insignifcant compared to hitting the database extra times.

Hope this helps.

JoseRa · **Joined:** Wed Mar 09, 2005 8:58 am **Posts:** 5

Thank you for your help !

Yes, I agree with you.
Now, I'm working in an application that have very poor performance.

We have a database with many tree data structures, with very nested tables hierarchy, and I'm using this feature (batch fetching) to improve performance.

Effectively, sending many JDBC queries to a remote database slows down the application very much, so I'm triying to reduce the number of queries.

I'll try to modify source code to adapt it to my needs.

Again, thanks a lot !

andrewom · **Posted:** Thu Mar 10, 2005 7:11 pm

Let me know how you get on.

If you want, I can clean up my version of BatchingCollectionInitializer (for 2.1.8) and post it here.

andrewom · **Posted:** Thu Mar 10, 2005 9:50 pm

Here's my patched version of the 2.1.8 BatchingCollectionInitializer. I've done a quick test with a batch size of 3, retrieving from 1-7 rows and it seems fine.

Basically, it works as you'd expect. If there is less than a full batch left to retrieve, it retrieves the remainder in one select.

Note: this only works for collections (both components and associations). You'll need to change BatchingEntityLoader (which is a cut and paste of BatchingCollectionInitializer!) if you specify batch-size at the class level as well.

Please let me know if you use it and how it goes as I'll probably end up sticking this in production. (it doesn't appear that Hibernate itself will be changed as I've seen other posts about this that date back around 6 months).

Good luck!
Andrew

Code:

package net.sf.hibernate.loader;

import java.io.Serializable;
import java.sql.SQLException;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.collection.CollectionPersister;
import net.sf.hibernate.collection.AbstractCollectionPersister;
import net.sf.hibernate.engine.SessionImplementor;
import net.sf.hibernate.engine.SessionFactoryImplementor;

/**
 * "Batch" loads collections, using multiple foreign key values in the
 * SQL <tt>where</tt> clause.
 * 
 * @see CollectionLoader
 * @see OneToManyLoader
 * @author Gavin King
 */
public class BatchingCollectionInitializer implements CollectionInitializer {
   
   private static final Log log = LogFactory.getLog(BatchingCollectionInitializer.class);
   
   private final Loader nonBatchLoader;
   private final Loader batchLoader;
   private final int batchSize;
   private final CollectionPersister collectionPersister;

    /**
     * Note: smallBatchSize & smallBatchLoader are no longer used
     */
   public BatchingCollectionInitializer(CollectionPersister collPersister, int batchSize, Loader batchLoader, int smallBatchSize, Loader smallBatchLoader, Loader nonBatchLoader) {
      this.batchLoader = batchLoader;
      this.nonBatchLoader = nonBatchLoader;
      this.batchSize = batchSize;
      this.collectionPersister = collPersister;
   }

   public void initialize(Serializable id, SessionImplementor session)
      throws SQLException, HibernateException {
      Serializable[] batch = session.getCollectionBatch(collectionPersister, id, batchSize);
      if (batch[batchSize-1]==null ) {
            // Number of items is less than the batch. Calculate the remainder
            int remainder = 0;
            for (; remainder < batch.length; remainder++) {
                if (batch[remainder] == null) {
                    break;
                }
            }

            if (remainder == 1) {
                // Optimisation for a single row
                System.out.println("***** Using non-batch loader");
                nonBatchLoader.loadCollection( session, id, collectionPersister.getKeyType() );
            } else {
                System.out.println("***** Using new batch loader instance");
                // Create a new batch loader instance for the remainder
                if ( log.isDebugEnabled() ) log.debug("creating new batch loader instance of size: " + remainder);
                Serializable[] remainingBatch = new Serializable[remainder];
                System.arraycopy(batch, 0, remainingBatch, 0, remainder);
                SessionFactoryImplementor factory = session.getFactory();
                Loader loader;

                if (batchLoader.getClass().equals(CollectionLoader.class)) { // avoid instanceof just in case loaders extend one another
                    loader = new CollectionLoader((AbstractCollectionPersister)collectionPersister, remainder, factory);
                } else if (batchLoader.getClass().equals(OneToManyLoader.class)) {
                    loader = new OneToManyLoader((AbstractCollectionPersister)collectionPersister, remainder, factory);
                } else {
                    throw new RuntimeException("Unknown loader class: " + batchLoader.getClass().getName());
                }
                loader.loadCollectionBatch(session, remainingBatch, collectionPersister.getKeyType());
                log.debug("done batch load");
            }
      }
      else {
            System.out.println("***** Using shared batch loader instance");
         if ( log.isDebugEnabled() ) log.debug( "batch loading collection role: " + collectionPersister.getRole() );
         batchLoader.loadCollectionBatch( session, batch, collectionPersister.getKeyType() );
            log.debug("done batch load");
      }
   }
}

JoseRa · **Joined:** Wed Mar 09, 2005 8:58 am **Posts:** 5

OK. I'll tell you.

Now we are porting the application from Hibernate 1.2 to Hibernate 2.1.8.

Hibernate 2.1.8 has a richer HQL syntax than previous version, so I could write efficiently data retrieving.

The most important thing to do for us is to avoid initializes inside while loops, for example, for retrieving a 3 level hierarchy:

Programa 1:n Prestacion 1:n Alternativa

Code:

objPrograma = (Programa) hib_session.load(Programa.class, id);
Hibernate.initialize(objPrograma.getPrestacions());
if (objPrograma.getPrestacions() != null) {
    Iterator itPrestacions = objPrograma.getPrestacions().iterator();
    while (itPrestacions.hasNext()) {
        Prestacion objPrestacion = (Prestacion)itPrestacions.next();
        Hibernate.initialize(objPrestacion.getAlternativas());
    }
}

This causes the n+1 select problem. I retrieve all Prestacion objects from a Programa object with a select.
But then, I have to send a select for every Prestacion object to retrieve its Alternativa objects associated.

The number of database registers has grown very much in the last months and we started to have performance problems.

"Hibernate in Action" book, does not recommend exhaustive batch-fetching use, because there are better ways for this (eager fetching with outer-joins enabled). But I'm trying to find a quick solution.

Thank you very much for posting your code !
I'll take a look at it and I will tell you.

andrewom · **Posted:** Sun Mar 13, 2005 7:33 pm

Outer join fetching is limited to a single relationship for a given entity. e.g. If you have 2 one-to-many relationships, only one can be defined as an "outer-join". Your only option for subsequent relationships is batching.

I guess the limitation protects you against inadvertently defining a cartesian product. Unfortunately, there are still cases where a cartesian product performs better than batching ... hopefully, the limitation will be removed in future releases.

Also, be careful when using the outer-join approach as it returns duplicates. This seems like a clear bug to me, but the developers don't agree and it appears the standard "solution" is to wrap the results in a HashSet.

andrewom · **Posted:** Tue Mar 15, 2005 7:47 pm

This is was submitted to JIRA and rejected. See http://opensource.atlassian.com/project ... se/HB-1464.

Apparently, the developers aren't interested in the performance of Hibernate.

gavin · **Posted:** Tue Mar 15, 2005 8:47 pm

On the contrary, we are very concerned about the negative performance impact of creating many different PreparedStatements when the maximum batch-size is large.

andrewom · **Posted:** Tue Mar 15, 2005 11:59 pm

Firstly, thanks for shedding some light on your thinking.

However, I don't agree that the current solution is a good one. In my example above with a batch-size of 100, retrieving 98 rows will take 6 selects (using the improved 3.0rc1 algorithm).

Addmittedly that's a bad case for the algorithm, but the average must be 3-4 selects when the remainder is less than the batch size.

The time to prepare a statement is much less than the time to execute 3-4 selects.

Are you worried about evicting other statements from a PreparedStatement cache? Again, I don't know if this is a legitimate concern, but if so, you could build the query dynamically. Avoiding a PreparedStatement here makes some sense as it is unlikely that you'll repeatedly get the same remainder.

jpwinne · **Posted:** Tue Jan 15, 2008 4:19 am

gavin wrote:

On the contrary, we are very concerned about the negative performance impact of creating many different PreparedStatements when the maximum batch-size is large.

Maybe I am missing something, but when you have batch-size 5
(so 5, 2 and 1 statements),
and 4 items with ids 1,2,3,4 to retrieve,
why don't you put two times "4" in the "in" clause,
so that you only need one query:

"from Item i where i.id in (1,2,3,4,4)"