[hibernate-users] Hibernate Performance against Progress Open Edge database

[boliver at lvlomas.com]

Your Hibernate property mapping shows the column "fotosetnum"  as coming
from a Progress sequence.  So it will pick up a value from that sequence
for every row returned which will slow the query down.  Your pure JDBC
query is simply querying the table and won't do anything with sequences.

Hope this helps.

BNO


|------------>
| From:      |
|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |Joe Hansen <joe.hansen.at at gmail.com>                                                                                                    |
  >----------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| To:        |
|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |boliver at lvlomas.com                                                                                                                     |
  >----------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Cc:        |
|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |hibernate-users at lists.jboss.org                                                                                                         |
  >----------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Date:      |
|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |04/24/2009 03:19 PM                                                                                                                     |
  >----------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Subject:   |
|------------>
  >----------------------------------------------------------------------------------------------------------------------------------------|
  |Re: [hibernate-users] Hibernate Performance against Progress Open  Edge database                                                        |
  >----------------------------------------------------------------------------------------------------------------------------------------|





Thanks for your reply. The SQL query generated by Hibernate is similar
to the one generated by JDBC. Here's the SQL query generated by
Hibernate:

select photoset0_.fotosetnum as fotosetnum0_,  photoset0_.fotosettitle
as fotosett2_0_ from pub.fotoset photoset0_




On Thu, Apr 23, 2009 at 7:10 PM,  <boliver at lvlomas.com> wrote:
> I am curious to see if the SQL statements Hibernate is generating for the
> calls are the same or similar to what you are using in your straight JDBC
> calls.
>
> Can you turn on SQL logging for Hibernate and capture the actual SQL
> statements getting generated so we know exactly what we're comparing
here?
>
> BNO
>
>
> |------------>
> | From:      |
> |------------>
>
>----------------------------------------------------------------------------------------------------------------------------------------|

>  |Joe Hansen <joe.hansen.at at gmail.com>
|
>
>----------------------------------------------------------------------------------------------------------------------------------------|

> |------------>
> | To:        |
> |------------>
>
>----------------------------------------------------------------------------------------------------------------------------------------|

>  |hibernate-users at lists.jboss.org
|
>
>----------------------------------------------------------------------------------------------------------------------------------------|

> |------------>
> | Date:      |
> |------------>
>
>----------------------------------------------------------------------------------------------------------------------------------------|

>  |04/23/2009 06:54 PM
|
>
>----------------------------------------------------------------------------------------------------------------------------------------|

> |------------>
> | Subject:   |
> |------------>
>
>----------------------------------------------------------------------------------------------------------------------------------------|

>  |[hibernate-users] Hibernate Performance against Progress Open Edge
database                                                        |
>
>----------------------------------------------------------------------------------------------------------------------------------------|

>
>
>
>
>
> Hi All,
>
> I know that I am comparing apples to oranges here. But I am surprised to
> find that a simple Hibernate query ("from Photoset p") takes 4 times as
> much time as JDBC. I know that there is overhead involved with the
benefits
> that Hibernate offers. But don't you think 4 times is just too slow?
Please
> let me know if there is a way to get a better performance out of
Hibernate.
> Thank you!
>
> Here are the results:
>
> # Records: 5000
>
> # Mapped Columns: 2
> Hibernate Query / Plain JDBC: 560 ms / 150 ms
>
> # Mapped Columns: 3
> Hibernate Query / Plain JDBC: 1200 ms / 270 ms
>
> # Mapped Columns: 5
> Hibernate Query / Plain JDBC: 1680 ms / 390 ms
>
> Thanks,
> Joe
>
> /* Hibernate Code */
> Session session = sessionFactory.openSession();
> Transaction tx = session.beginTransaction();
>
> Date beginTime = new Date();
>
> Query query = session.createQuery("from Photoset p");
> List<Photoset> photosets = query.list();
> System.out.println(photosets.size() + " photoset(s) found:");
>
> Date endTime = new Date();
>
> tx.commit();
> session.close();
> System.out.println("Total Time Taken: " + (endTime.getTime() -
> beginTime.getTime()) + " ms");
>
>
> /* Photoset.hbm.xml - Hibernate Mapping file */
> <hibernate-mapping>
>     <class name="Photoset" table="pub.fotoset">
>         <id name="id" column="fotosetnum">
>             <generator class="sequence">
>                 <param name="sequence">pub.nextfotosetnum</param>
>             </generator>
>         </id>
>
>         <property name="title" column="fotosettitle" />
>     </class>
> </hibernate-mapping>
>
>
> /* Photoset.java */
> public class Photoset {
>     private Long id;
>     private String title;
>
>     Photoset() {}
>
>     public Photoset (String title) {
>         this.title = title;
>     }
>
>     public Long getId() {
>         return id;
>     }
>     public void setId(Long id) {
>         this.id = id;
>     }
>
>     public String getTitle() {
>         return title;
>     }
>     public void setTitle(String title) {
>         this.title = title;
>     }
> }
>
> /* JDBC Code that runs 4 times faster */
> Date beginTime = new Date();
>
> PreparedStatement pstmt = con.prepareStatement("select fotosetnum,
> fotosetitle from pub.fotoset");
> ResultSet rs = pstmt.executeQuery();
> int numFotosets = 0;
> while (rs != null && rs.next()) {
>     Photoset photoset = new Photoset();
>     photoset.setId(rs.getLong(1));
>     photoset.setTitle(rs.getString(2));
>     numFotosets++;
> }
> rs.close();
> System.out.println("# Photosets:" + numFotosets);
>
> Date endTime = new Date();
> System.out.println("Total Time Taken: " + (endTime.getTime() -
> beginTime.getTime()) + " ms");
>
> _______________________________________________
> hibernate-users mailing list
> hibernate-users at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/hibernate-users
>
>
>
>