0

I have a problem with a page loading of data. rich:datatable is used to display list of announcements.

Announcement class include Set of AnnouncedLot class and in a mapping it is specified as

<set cascade="all,delete-orphan" lazy="false" name="lots" table="ad_lot">
<key column="lotId"/>
<many-to-many class="AnnouncedLot" column="anlotId"/>
</set>

AnnouncedLot class mapping is 

<class name="AnnouncedLot" table="announcedLot">
  <id name="id" type="java.lang.Long" unsaved-value="-1">
     <generator class="native"/>
  </id>
  <property name="idValue" not-null="false" unique="true"/>
  <property name="databaseId" not-null="false" unique="true"/>
  <property length="4047" name="title"/>
  <property length="4047" name="description"/>
  <property length="4047" name="source"/>
  <property length="65534" name="adText"/>
  <property name="sum"/>
</class>

At the moment there are about 1,5 million of announcement in the database and 3,5 millions of lots. One announcement can have 1 or even 100 lots.

Page loads only 30 first results of an announcement query. It loads quickly when there are no lots in the database, but with lots it can take around 1,5 min.

I have tried to change lazy to true and to google this problem but haven't found much. Can anyone help with this?

Update:

After checking what query takes a lot of time, I've found out that the rows counting query is the cause. Query contained lots.sum condition due to default filter parameter. I have change the filter so it doesn't have to use the set and the performance has improved.

Thanks everyone for help.

Adilya Taimussova
  • 551
  • 1
  • 6
  • 12
  • Have you tried a database trace to see what queries are actually being run? – Jeremy Stein May 29 '12 at 12:34
  • Yeah, just found out that count query of total number of announcements takes long time and it hangs on copying data to tmp table. And this query, generated by hibernate, contains left join to lots table. – Adilya Taimussova May 30 '12 at 06:28
  • The row count is implemented as `criteria.setProjection(Projections.rowCount());` have no idea what could be wrong with it. – Adilya Taimussova May 30 '12 at 06:57

1 Answers1

2

Try to use batch-size attribute on the many-to-many association.

http://docs.jboss.org/hibernate/orm/3.3/reference/en/html/performance.html#performance-fetching-batch

ramdane.i
  • 634
  • 5
  • 7
  • Thank you for your answer. I have tried it but it's still slow. I haven't paid attention to in the announcement class mapping. Seems like it causes a problem. Checked mysql server status and it appears that sending data for query "select count(*) as y0_ from isdb.announcement this_ left outer join isdb.tender_announcements this_1 on this_" takes whole 2 minutes. – Adilya Taimussova May 30 '12 at 05:48