Optimize queries

galencita · **Joined:** Wed Jun 18, 2008 11:55 am **Posts:** 6

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version:3.1

Name and version of the database you are using:MySQL 5.0

Hello,
I have several queries in my application I want to optimise, up 4 levels of inner join. Using query cache, Spring Hibernate 3.1 and 1.2.7, as MySQL database. I copy an example

Code:

select this_.id as y0_, this_.item_date as y1_, this_.amount as y3_, company3_.firmname as y7_, company3_.id as y8_, 
client2_1_.firmname as y9_, client2_.id as y10_, operator1_.first_name as y11_, operator1_.last_name as y12_
from items this_ 
inner join companies company3_ on this_.company_id=company3_.id 
inner join client client2_ on this_.client_id=client2_.id 
left outer join firms client2_1_ on client2_.id=client2_1_.id 
inner join users operator1_ on this_.operator_id=operator1_.id 
order by this_.id desc;

Thank you!

farzad · **Posted:** Fri Jun 27, 2008 11:49 am

what do you want to optimize?

Farzad-

galencita · **Joined:** Wed Jun 18, 2008 11:55 am **Posts:** 6

Such queries reduce the performance of my application. For example there is any way to cache data from the company or operator?

farzad · **Posted:** Fri Jun 27, 2008 1:33 pm

galencita wrote:

Such queries reduce the performance of my application. For example there is any way to cache data from the company or operator?

yes you can but you have to set those relations to be fetched by a select, and configure the entities to be cached.

Farzad-

galencita · **Joined:** Wed Jun 18, 2008 11:55 am **Posts:** 6

Farzad, thank you for your response
That changed my settings in hbm.xml, add fetch.

Code:

<class name="project.items.model.Items" table="items">
        <id name="id" column="id" type="long">
            <generator class="native"/>
        </id>
        <version name="version" type="timestamp"/>
        <many-to-one name="company" not-null="true" column="company_id" cascade="none" fetch="select"/>
        <property name="amount" not-null="true"/>
        <many-to-one name="operator" not-null="true" column="operator_id" cascade="none" fetch="select"/>
    </class>

How do for caching entities?

farzad · **Posted:** Fri Jun 27, 2008 3:03 pm

galencita wrote:

Farzad, thank you for your response
That changed my settings in hbm.xml, add fetch.

Code:

<class name="project.items.model.Items" table="items">
        <id name="id" column="id" type="long">
            <generator class="native"/>
        </id>
        <version name="version" type="timestamp"/>
        <many-to-one name="company" not-null="true" column="company_id" cascade="none" fetch="select"/>
        <property name="amount" not-null="true"/>
        <many-to-one name="operator" not-null="true" column="operator_id" cascade="none" fetch="select"/>
    </class>

How do for caching entities?

You need to specify a cache provider for hibernate in the hibernate config file. I usually use ehcache. After that you need to add <cache usage="..." region="..."/> under class tags. In addition you need to configure you cache provider, for example ehcache, for eviction policies for each region.

Farzad-

galencita · **Joined:** Wed Jun 18, 2008 11:55 am **Posts:** 6

I will take this suggestion. One more question: using second-level cache, further optimize these consultations?

farzad · **Posted:** Fri Jun 27, 2008 5:09 pm

galencita wrote:

I will take this suggestion. One more question: using second-level cache, further optimize these consultations?

That's exactly what I explained before. The cache tag specifies that a specific entity should be stored in the second level cache.

Farzad-

galencita · **Joined:** Wed Jun 18, 2008 11:55 am **Posts:** 6

Okay, thank you very much for your help

Cameron McKenzie · **Posted:** Mon Jul 07, 2008 9:02 am

Definitely take advantage of the second level cache through the cache tag. I think you'll find a market improvement.

Don't look past various database tricks as well for optimizing access. Nobody knows the database more than the database itself!