hibernate 4 has performance issue after migration

Question

Our project has been migrated from JSF1.1/Hibernate3x to JSF2.1.7/Hibernate4, added primefaces along with it. While fetching the list/records from database it was taking 30 seconds before migration, but now its taking around 3 to 4 mins after migration.It is one of the critical issue.

The table which i am trying to fetch has 101 columns and it has 11000 records. We are using SQL Server 2008 for database

Development Environment is Eclipse Kepler

Please help me out !!

hibernate.cfg.xml

<hibernate-configuration>

<session-factory>
    <property name="dialect">
        org.hibernate.dialect.SQLServerDialect      
    </property>
    <property name="myeclipse.connection.profile">
        com.microsoft.sqlserver.jdbc.SQLServerDriver
    </property>

    <property name="connection.driver_class">
        com.microsoft.sqlserver.jdbc.SQLServerDriver
    </property>
    <property name="hibernate.show_sql">false</property>
    <property name="connection.autocommit">false</property>
    <!-- Enable Hibernate's automatic session context management -->
    <property name="current_session_context_class">thread</property>
    <!-- connection pooling properties -->
    <property name="hibernate.c3p0.max_size">20</property>
    <property name="hibernate.c3p0.min_size">3</property>
    <property name="hibernate.c3p0.timeout">100</property>
    <property name="hibernate.c3p0.max_statement">50</property>
    <property name="hibernate.c3p0.idle_test_period">300</property>
    <property name="hibernate.c3p0.acquire_increment">3</property>
    <property name="transaction.factory_class">
        org.hibernate.transaction.JDBCTransactionFactory
    </property>

    <!-- For Hibernate caching -->
    <property name="hibernate.cache.use_query_cache">true</property>
    <property name="hibernate.cache.use_second_level_cache">true</property>
    <property name="hibernate.cache.region.factory_class">org.hibernate.cache.ehcache.EhCacheRegionFactory</property>
    <property name="hibernate.cache.provider_class">org.hibernate.cache.ehcache.EhCacheProvider</property>
    <property
        name="hibernate.cache.provider_configuration_file_resource_path">
        com/src/hibernate/ehcache.xml
    </property>

</session-factory>

Implementation

public List search(){
    Transaction tr=null;
    session=HibernateSessionFactory.getSession();
    StringBuilder qry = new StringBuilder();
    List list=new ArrayList();
    try{
        tr=session.beginTransaction();
        qry.append("select locn,(select locationTypeName from TblLocationTypes where locationTypeIdPk = "
                + " locn.trackLocationInfoLocationTypeIdFk) as locType,"
                + "(select locationDivisionName from TblLocationDivision "
                + "where locationDivisionIdPk=locn.trackLocationInfoDivision) as divis "
                + "from TblTrackLocationinformation locn where 1=1 ");

        Query query = session.createQuery(qry.toString());
        list = query.list();
        tr.commit();
    }
    catch(Exception e){
        if(tr != null){
            tr.rollback();
        }
        e.printStackTrace();
    }finally{
        session.flush();
        session.clear();
        session.clear();
    }
    return list;

}

Have tried even with

query.scroll instead of query.list but issue remains same.

I have referred and tried all the below links but nothing solved my issue

Migration from Hibernate 3 to 4 slows down startup

Why is the Hibernate query.list() slow?

Simple hibernate query returning very slowly

I could see some hints with .hbm.xml file

.hbm.xml

<set cascade="delete" inverse="true" lazy="false" name="tblTrackLocationrelations" sort="unsorted" table="tbl_track_locationrelations"> <key column="track_LocationRelations_location_id_fk" /> <one-to-many class="com.src.hibernate.TblTrackLocationrelations" /> </set>

From where are you calling this method? How are you measuring is that the actual result of the search method or the rendering on screen? — M. Deinum, May 15 '15 at 13:35
@M.Deinum, Thank for reply. I am calling through .xhtml page,its added now — Radhamani Muthusamy, May 15 '15 at 13:36
And also please answer the question what you are measuring, the rendering of the screen or the execution of the method. Those are 2 different things. Also when using it in xhtml (and doing it wrong) can lead to 3 or more calls to the method. Take additional information being rendered due to new component library and your time grows exponentially. Putting 11000 rows with loads of columns on screen is generally not a very good idea. Especially not when you are adding all sort of behavior (or in this case PrimeFaces is adding that). — M. Deinum, May 15 '15 at 14:23
@RadhamaniMuthusamy Have tested the query directly in sql ? to eliminate a db problems like missing index ...? — Master Mind, May 15 '15 at 15:11
@M.Deimum, 1.5 minutea is been taking when we do query.list itself then to display as UI , taking around 3 to 4 mins — Radhamani Muthusamy, May 15 '15 at 17:21
@master mind, Thanks for reply. yea I have executed directly, its taking in milliseconds not even a second — Radhamani Muthusamy, May 15 '15 at 17:24
then could you activate loggin the sql generated by hibernate to see the face of the query — Master Mind, May 15 '15 at 17:48
@MasterMind, i have activated sql generated by hibernate(true) , i could observe the statement is executing 11000 times as it has 11000 records to return. I am not sure its doing right way. Is it correct way ? — Radhamani Muthusamy, May 18 '15 at 05:14
@M.Deinum, Thank you. PrimeFaces is taking care of sorting the list. We are displaying only 4 to 5 columns in the screen, i just put a not that table has 101 columns as java also have that much of variables along with getter setters in constructor. — Radhamani Muthusamy, May 18 '15 at 05:22
It doesn't matter how much you display, everything is going from the database to the app server and used to construct objects. If you only show a subset of that columns I would suggest a dedicated object for this and a specific query, instead of retrieving everything. judging from what you describe you have serious issues with the amount of data being transferred and also with your mapping (because if there are 11000 additional single queries there is something seriously wrong). As mentioned create a dedicated object for the screen and optimized query. — M. Deinum, May 18 '15 at 05:30
Also an in-memory sort with 11000 is going to be slow, very slow. You want to do sorting on the database level (and probably paging to to limit the amount of data). — M. Deinum, May 18 '15 at 05:31
@M.Deinum, I found in .hbm.xml there is code written like this .This table only is executing 11000 times, i think this is the culprit, but we need this for deletion purpose. — Radhamani Muthusamy, May 18 '15 at 06:01
Sounds like hibernate is fetching some detail information in a lazy or way. — Kukeltje, May 18 '15 at 06:09
Please don't add code etc. as comments that is unreadable. Update your initial question... — M. Deinum, May 18 '15 at 06:55
The problem is the non-laze relation you can try to make it lazy (shouldn't make a difference for deletes but should for reads). Regardless of that, still you have an overhad of 3 minutes in rendering due to the amount of data and the fact that prime faces is doing an in memory sort you would need to fix that as well. — M. Deinum, May 18 '15 at 06:58
@M.Deinum, I have added the code with the question itself. Yes i made correction the lazy like lazy=true. I could see some changes that i is taking 6 to 10 seconds to execute the query and to list it. Still then it is taking around 2.25 minutes. Please explain me how to achieve it "prime faces is doing an in memory sort you would need to fix that as well" — Radhamani Muthusamy, May 18 '15 at 07:17
@Kukeltje, Thank you. I did change the way of lazy, could see some changes — Radhamani Muthusamy, May 18 '15 at 07:18
You have a large results and by default prime faces is doing an internal sort (i.e. comparing values itself) and this is slow... You should implement ordering on the database instead of sorting/paging with prime faces in memory sorting/paging. — M. Deinum, May 18 '15 at 07:26
@M.Deinum, We are not using primefaces datatable sorting but using pagination. I am trying to optimize the query. Lets see how it goes, i will update on this — Radhamani Muthusamy, May 18 '15 at 10:36
According to your earlier comments you did use sorting. Still everything needs to go to the client, chopped up in parts per page. I would strongly suggest to use a `LazeDataModel` and implement paging (and sorting) on the database. This reduces the amount of data that is being send and allows to optimize your query. — M. Deinum, May 18 '15 at 10:39
Thanks for ur suggestion and help. I am trying to implement LazyDataModel, got stuck up, so learning and doing it. — Radhamani Muthusamy, May 18 '15 at 12:48

score 0 · Accepted Answer · answered May 19 '15 at 06:37

Modifying the lazy works out , it reduced nearly 1 minute. Changed from lazy=false to lazy=true

<set cascade="delete" inverse="true" lazy="true" name="tblTrackLocationrelations" sort="unsorted" table="tbl_track_locationrelations"> <key column="track_LocationRelations_location_id_fk" /> <one-to-many class="com.src.hibernate.TblTrackLocationrelations" /> </set>

Added to this, there were few codes keep on calling through constructor so cleaning the code will help to get rid of the performance issue.

hibernate 4 has performance issue after migration

1 Answers1