Tunning Hibernate for better performance

ltcmelo · **Posted:** Thu May 20, 2004 10:58 am

HI,
i'm trying to make a little personal benchmark: DAO x Hibernate.
I know that it'll never reflect a real production situation, because i'm using some "random" queries that won't actually make a sense.
Beyond that, i'm not considering networking, traffic, and a complete use case of the system, anyway, i'd like to make this experiment for some basic comparison.
I commented a result (on this forum) that i got, but the Hibernate experts said i wasn't using correct "configuration" (mapping), then, this time, i'm asking them
(or anyone who can give me nice information) for some pieces of code, so i can make a better test.

I'd apreciate SO MUCH if experts could give me the "best" (in terms of performance only) mapping files for 2 classes:

Code:

public class Employee{
  private Integer empId;
  private String firstName;
  private String lastName;
  //constructors, getter and setters and ANYOTHER NEEDED METHOD
}

public class EmployeeSkill{
  private Integer fk_empId;
  private String skillName;
  private String skillDesc;
  //constructors, getter and setters and ANYOTHER NEEDED METHOD
}

I also would appreciate SO MUCH if the experts could give the "best" (in terms of performance) Hibernate QL code to accomplish the following SQL statements.

Code:

-- empID is the pK of this table
insert into Employee(empId, firstName, lastName)
values (1, 'john', 'miller'), (2, 'steve', 'miller'), (3, 'dug', 'miller'), (4, 'kim', 'stark');

-- skillName is the pk of this table, fk_empId references Employee
insert into EmployeeSkill (fk_empId, skillName, skillDesc)
values (1, 'gardner', 'takes care of yards'), (1, 'doctor', 'takes care of people'),  (2, 'coach', 'build computers'), (4, 'analyst', 'does a lot of things');

-- selects all skills for employee 1
select es.skillName, e.firstName, e.lastName 
from Employee e, EmployeeSkill es
where e.empId = 1 and es.fk_empId = e.empId;

-- selects all skills for any Miller employee
select es.skillName, es.skillDesc 
from EmployeeSkill es, Employee e
where es.fk_empId = e.empId and e.lastName = 'miller';

update EmployeeSkill set skillDesc = 'does only one thing' 
where skillName = 'analyst';

update Employee e, EmployeeSkill es  set skillName = 'engineer'
where es.fk_empId = e.empId and e.empId= 2

I'll put the HQL code inside a session fa

anthony · **Posted:** Thu May 20, 2004 12:43 pm

insert = new instance + session.save

update = session.load + modification via setter + commit (flush)

select = hql select query + fetch join if you need to load your association or 1 collection

all is explain in the doc

try do it by your self, and then ask questions about improvments

ltcmelo · **Posted:** Fri May 21, 2004 12:03 am

Hi,
i changed a little the classes i propposed, but here are my mappings for them.

Note that my point is to get a "best configuration" that provides a better performance (as i'll be comparing it to DAO). I'm very new to Hibernate and I'm doing this, as i said, for making my choice if i'll start to use Hibernate or not (unfortunatelly, i cannot "afford" to work to spent to much time learning Hibernate, getting good at it, and then making my decision... i think that's how it work to most of us. That's why i need some help).

Code:

<hibernate-mapping>
    <class
        name="hibernate.Employee"
        table="Employee"
        dynamic-update="false"
        dynamic-insert="false"
    >
       <id
            name="empId"
            column="empId"
            type="java.lang.Integer"
        >
            <generator class="native">
            </generator>
        </id>
       <property
            name="firstName"
            type="java.lang.String"
            update="true"
            insert="true"
            column="firstName"
        />
       <many-to-one
            name="fk_orgId"
            class="hibernate.Organization"
            cascade="none"
            outer-join="auto"
            update="true"
            insert="true"
            column="fk_orgId"
        />
       <property
            name="lastName"
            type="java.lang.String"
            update="true"
            insert="true"
            column="lastName"
        />
  </class>
</hibernate-mapping>

And for the other class:

Code:

<hibernate-mapping>
    <class
        name="hibernate.Organization"
        table="Organization"
        dynamic-update="false"
        dynamic-insert="false"
    >
      <id
            name="orgId"
            column="orgId"
            type="java.lang.Integer"
        >
            <generator class="native">
            </generator>
        </id>
        <set
            name="employees"
            lazy="true"
            inverse="false"
            cascade="none"
            sort="unsorted"
            order-by="firstName"
        >

              <key
                  column="fk_orgId"
              />

              <one-to-many
                  class="hibernate.Employee"
              />
        </set>
      <property
            name="orgAddress"
            type="java.lang.String"
            update="true"
            insert="true"
            column="orgAddress"
        />
      <property
            name="orgName"
            type="java.lang.String"
            update="true"
            insert="true"
            column="orgName"
        />
    </class>
</hibernate-mapping>

In the code i'm using to make measurments, i'm using
inserts the following way:

Code:

Organization org2 = new Organization(orgId11, "reeblock", "reeblock street");
session.save(org2, orgId11);
session.flush();

and selects in the following way:

Code:

List emps = session.find("from Employee as e where e.fk_orgId = 10");
Iterator iter = emps.iterator();
while (iter.hasNext()){
   Employee empOrg10 = (Employee)iter.next();
   System.out.println("Employee is: " + empOrg10.getLastName());
}

I'll post more code later, because i'm still working on it.

FINALLY, as i said, i'd appreciate so much if Hibernate experts help me in the following.

- Give me some tips on the mapping files for better performance.

- Show me some kinda specific query for this situation in wich Hibernate can really show the diference from SQL, in other words, some kinda of query that i' say: "I can do that ?? cool !!!"

Thanks all very much,
ltcmelo

anthony · **Posted:** Fri May 21, 2004 3:06 am

Quote:

- Give me some tips on the mapping files for better performance.

It has no sense, all performance workaround can be "isolated" by the definition of lazy loading for collections and associations, it also required a strong knowledge of HQL.
But these parameters must match exactly with your use cases, here you're only dealing with a general mapping file, each use case can be optimized so take a look at the doc and understand lazy aspect + second level cache, it can be a great performance issue.

Quote:

- Show me some kinda specific query for this situation in wich Hibernate can really show the diference from SQL

HQL is made above SQL, it allows you writing complex queries easily but won't be more performant than pure SQL as it generates pure SQL....
What you must know is that a 20 lines SQL query can sometimes be written in a 5 lines HQL.

But all this appears in the reference doc....

_charles_ · **Posted:** Fri May 21, 2004 3:24 am

Hi,

I'm sorry for you, but I'm not the expert you were waiting for... but, I can maybe try to tell you about some features that could help you making the choice of Hibernate insteed of SQL...

First of all, Hibernate is an Objet Relationnal Mapping tool, it helps you think all your application with the Object Oriented model. You don't think your query with the tables' id's but in term of compositions or associations. Unitl here, you could say, "Ok, but it doesn't change a lot for me..., I already know SQL and it's not a problem for me to know the table ids...".

But, don't forget that Hibernate is a Persistent Layer, not only a mapping tool! It means that Hibernate do a real work for you! for example, it optimizes all the queries.
It uses lazy loading (will you really program it yourself?), it detects the changes done when you do an update, and optimize the query.
With a good use of the sessions, you can put all the queries in cache and do all in the same time. Isn't this a real difference with SQL?

Don't forget that you can change the database with only changing three lines! (the driver, the database location, and the dialect) Could you be sure doing the same thing with SQL?

There are lots of other mechanisms I don't know a lot about (use of datasource, connection provider, ..., ..., ...)
Don't forget I'm not an expert! But what I really know is I'll never use something else that Hibernate to be my Persistent Layer... Sometimes, I would not need to have a real persistent layer, so, Hibernate does't use.

Bye,

Charles

ltcmelo · **Posted:** Fri May 21, 2004 9:53 am

Hi Anthony and Charles,
i found your comments interesting , but i sill have a couple doubts.

Quote:

It has no sense, all performance workaround can be "isolated" by the definition of lazy loading for collections and associations, it also required a strong knowledge of HQL. But these parameters must match exactly with your use cases, here you're only dealing with a general mapping file...

I didn`t quite understand the above sentence, what actually does "isolated" mean here?

Anthony, could you give one (maybe a simple one) example of what you meant (you said it requires a strong knowledge of HQL, something i don`t have; that`s why i ask for a example because i need to show something "nice" to my boss that i`ll convince him on using Hibernate, in other words... although i`m realling studying Hibernate, i don`t have time to get good on it before this decision).

Also, what kind of other mapping files i should have to configure for better performance? My file is a general mapping one, ok... but isn`t that how it should be?

Thanks again,
ltcmelo

anthony · **Posted:** Fri May 21, 2004 10:14 am

http://www.hibernate.org/hib_docs/refer ... ryhql.html

HQL (it's quite a complex HQL query)
select cust
from Product prod,
Store store
inner join store.customers cust
where prod.name = 'widget'
and store.location.name in ( 'Melbourne', 'Sydney' )
and prod = all elements(cust.currentOrder.lineItems)

in sql it will be something like:
SELECT cust.name, cust.address, cust.phone, cust.id, cust.current_order
FROM customers cust,
stores store,
locations loc,
store_customers sc,
product prod
WHERE prod.name = 'widget'
AND store.loc_id = loc.id
AND loc.name IN ( 'Melbourne', 'Sydney' )
AND sc.store_id = store.id
AND sc.cust_id = cust.id
AND prod.id = ALL(
SELECT item.prod_id
FROM line_items item, orders o
WHERE item.order_id = o.id
AND cust.current_order = o.id
)
this was to demonstrate HQL power.

Now let's talk about ORM, you must realize what Object Relationnal Mapping is.
It allows you to work with a fine grained domain model.
It may contain one or more graphs of object.
That is to say you can travel in this graph having only one entry point, example, you have the name of a customer.
You'll query to retrieve the customer Object.
Then having it you'll be able to call myCustomer.getMother().getAdress().getState().getPresident().getDog() if you want, hibernate will hit the db for you and you won't have to write a query to retrieve all this.
There is two ways to think:
- you load all the object graph when retrieving the customer.... it will retrieve all the db and won't be performant
- to avoid this, you can "lazy" load some part of the object graph which will loaded only when asked (example at calling myCustomer.getMother())

There is no GENERAL rule, your mapping files must match with your business and lazy loading is only one of powerfull things about Hibernate.
Some other improvments can be done, i think of JBossCache for example. But you must know about your technical architecture before choosing a cache.

And i really think you'll waste less time reading the doc than waiting for other people to explain you how it works.

And if you don't like reading, buy commercial introduction to Hibernate, it's really nice! ;)

anthony · **Posted:** Fri May 21, 2004 10:15 am

oh and Charles intro is exactly what you must understand and his english is better than mine :)

ltcmelo · **Posted:** Fri May 21, 2004 12:43 pm

Anthony,
thanks for your replies, they`ll help me for a better undertanding of hibernate tech.

Beyond that, i`m actually reading docs and the Hibernate Reference (as you said), but sometimes things are not that clear (specially if your reading in a foreign language) and that`s why i post questions for theorical stuff too. I`m sure it has happened to you, but there are situations in wich a few answers for simple questions worth more than a whlole chapter of a book.

Thanks,
ltcmelo

larry · **Joined:** Fri Jan 23, 2004 4:29 pm **Posts:** 39

Have a look at http://www.open-lab.com/ppolsinelli/hibernateWorks.pdf

It explains how to use proxies for lazy loading of many-to-one relationships which you seem to have.

Just add the proxy attribute.

Code:

    <class
        name="hibernate.Employee" 
        proxy="hibernate.Employee"