Hibernate version:
2.1.6
Mapping documents:

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

Full stack trace of any exception that occurs:

Name and version of the database you are using:
MySQL 4.1.7
The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hi everyone,

I got a big problem in my application when about 1000 records are selected in one table. It took 25 sec for selecting 6000 records which is very poor from my point of view. So I investigated the problem by adding some duration outputs. I also ripped down my mapping which included 8 Sets associating one-to-many classes. Every set is indexed so that should speed up things, but hasn't got effected the select like expected. After removing the Sets and replacing them with just property values the seleted runs in 2,5 sec. 1/10 of the duration took before isn't bad but for selecting 6000 records in 2,5 sec is much better but still not so good imho. I also discovered that the growth of the duration isn't linear. If the table contains more then 2000 records then the duration growed rapidly with the number of records. Now using the Version without having so much Set's inside the mapping and POJO the duration is more stable.
Now here's my problem with this senario. I now got duplicated Mappings for the same table. If I only want to display the records then I use the POJO without set's. If I want to change something in the database then I use the mapping with Set's. From my point of view this is very dangerous because I use the mappings for generating/updating the DB schema.
And how may I avoid initialisation of the set's.
Is this the reason for setting a max number of results ? What kind of monster should power the database ? Has someone encountered such a scenario (while using complex mappings system gets awful slow with number of records)? I was think about to change the sets, containing just the keys instead of using the complete object.

best regards

Olaf

Seems like you are guessing. Why not use the DEBUG log to see whats really going on.

P.S. Make all your collection mappings lazy="true" and read again the documentation chapter about "Performance".

Hi Christian,

got some problem with the DEBUG setting I got a log file with 1 GB of size in less then 5 minutes. So I already limited that it will split after some MB. Now when I look into the log files, then it is really hard to resolve the place where the trouble starts, I have configured some Caches with EhCache so inside the log file I see that caches are build and accessed, so the database isn't hit everytime. I also read the Chapter about Performance (Thx to You Christion and Gavin HIA is really worth reading) and found several points that weren't switched on. So I switched on some afterwards performance was improved but not to the degree I expected. (I went to the degree that I switched on one performance param after another and then compared the performance until I got the best result for it)
Now back to the log analysis. Some of the collections being initialized contain also some bi-directional association, where the lazy tag is set to true and the inverse tag also set to true. So I also see that these Collections are also initialized and hitting the caches.
Sorry I'm guessing again but this takes/tokes most of the time in my application, because with the Mapping files not containing the large amount of collections, these log records disappear and the logs were much smaller and the application much faster.
BTW this post for getting some feedback. I wonder if some has complex mappings (I created the mappings using middlegen/ database schema is in stone ) and encounters such an effect. On startup off my project everything was really fast, but now when I got about 1000 records it's getting frustrating

And here's the Mapping:

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

<hibernate-mapping>
<!--
Created by the Middlegen Hibernate plugin 2.1

http://boss.bekk.no/boss/middlegen/
http://www.hibernate.org/
-->

<class
name="Ticket"
table="ticket"
>
<id
name="ticketId"
type="java.lang.Integer"
column="ticket_id"
>
<generator class="native">
<param name="sequence">ticket_ticket_id_seq</param>
</generator>
</id>

<property
name="ticketNumber"
type="java.lang.String"
column="ticket_number"
not-null="true"
length="20"
/>
<property
name="description"
type="java.lang.String"
column="description"
not-null="true"
length="255"
/>
<property
name="receiptNotice"
type="java.lang.String"
column="receipt_notice"
length="255"
/>
<property
name="locked"
type="java.lang.String"
column="locked"
not-null="true"
length="1"
/>
<property
name="infoCompletion"
type="java.lang.String"
column="info_completion"
length="40"
/>
<property
name="waiting"
type="java.lang.String"
column="waiting"
length="1"
/>
<property
name="personIdEdit"
type="int"
column="person_id_edit"
not-null="true"
length="4"
/>
<property
name="personIdIndicate"
type="java.lang.Integer"
column="person_id_indicate"
length="4"
/>
<property
name="personIdCreate"
type="int"
column="person_id_create"
not-null="true"
length="4"
/>
<property
name="personIdUpdate"
type="java.lang.Integer"
column="person_id_update"
length="4"
/>
<property
name="archived"
type="java.lang.String"
column="archived"
not-null="true"
length="1"
/>
<property
name="datetimeCreate"
type="java.sql.Timestamp"
column="datetime_create"
length="8"
/>
<property
name="datetimeUpdate"
type="java.sql.Timestamp"
column="datetime_update"
length="8"
/>
<property
name="datetimeCompletion"
type="java.sql.Timestamp"
column="datetime_completion"
length="8"
/>
<property
name="dateArchived"
type="java.sql.Timestamp"
column="date_archived"
length="8"
/>
<property
name="superTicket"
type="java.lang.Boolean"
column="super_ticket"
length="1"
/>
<property
name="estimatedTime"
type="java.lang.Float"
column="estimated_time"
length="8"
/>
<property
name="elapsedTime"
type="java.lang.Float"
column="elapsed_time"
length="8"
/>
<!-- Associations -->
<!-- bi-directional many-to-one association to Project -->
<many-to-one
name="project"
class="Project"
not-null="true"
outer-join="false"
index="IDX_PROJECT"
>
<column name="project_id" />
</many-to-one>
<!-- bi-directional many-to-one association to State -->
<many-to-one
name="state"
class="State"
not-null="true"
outer-join="false"
index="IDX_STATE"
>
<column name="state_id" />
</many-to-one>
<!-- bi-directional many-to-one association to Environment -->
<many-to-one
name="environment"
class="Environment"
not-null="true"
outer-join="false"
index="IDX_ENV"
>
<column name="environment_id" />
</many-to-one>
<!-- bi-directional many-to-one association to Comment -->
<many-to-one
name="comment"
class="Comment"
not-null="true"
outer-join="false"
index="IDX_COMMENT"
>
<column name="wait_comment_id" />
</many-to-one>
<!-- bi-directional many-to-one association to Priority -->
<many-to-one
name="priorityByPriorityId"
class="Priority"
not-null="true"
outer-join="false"
index="IDX_PRIO"
>
<column name="priority_id" />
</many-to-one>
<!-- bi-directional many-to-one association to Priority -->
<many-to-one
name="priorityByPriorityInternId"
class="Priority"
not-null="true"
outer-join="false"
index="IDX_PRIO_INTERN"
>
<column name="priority_intern_id" />
</many-to-one>
<!-- bi-directional many-to-one association to Category -->
<many-to-one
name="category"
class="Category"
not-null="true"
outer-join="false"
index="IDX_TICKET_CAT"
>
<column name="category_id" />
</many-to-one>
<!-- bi-directional one-to-many association to TicketHistory -->
<set
name="ticketHistories"
lazy="true"
inverse="true"
cascade="none"
outer-join="false"
>
<cache usage="read-write"/>
<key>
<column name="ticket_id" not-null="false" index="IDX_TICKET_HISTORY"/>
</key>
<one-to-many
class="TicketHistory"
/>
</set>
<!-- bi-directional one-to-many association to RefTicket -->
<set
name="refTickets"
lazy="true"
inverse="true"
cascade="none"
outer-join="false"
>
<key>
<column name="ticket_id" not-null="false" index="IDX_TICKET_REFTICKET"/>
</key>
<one-to-many
class="RefTicket"
/>
</set>
<!-- bi-directional one-to-many association to Comment -->
<set
name="comments"
lazy="true"
inverse="true"
cascade="none"
outer-join="false"
>
<key>
<column name="ticket_id" not-null="true" index="IDX_TICKET_COMMENT"/>
</key>
<one-to-many
class="Comment"
/>
</set>
<!-- bi-directional one-to-many association to Changes -->
<set
name="changes"
lazy="true"
inverse="true"
cascade="none"
outer-join="false"
>
<key>
<column name="ticket_id" not-null="false" index="IDX_TICKET_CHANGES"/>
</key>
<one-to-many
class="Changes"
/>
</set>
<!-- bi-directional one-to-many association to Estimation -->
<set
name="estimations"
lazy="true"
inverse="true"
cascade="none"
outer-join="false"
>
<key>
<column name="ticket_id" not-null="true" index="IDX_TICKET_ESTIMATION"/>
</key>
<one-to-many
class="Estimation"
/>
</set>
</class>
</hibernate-mapping>

if someone i interessed in the other mappings then I will post these.