Hibernate Community • View topic - Hibernate for database search intensive application?

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Hibernate for database search intensive application?

Page 1 of 1

[ 4 posts ]

Previous topic | Next topic

Author

Message

sairam313

Post subject: Hibernate for database search intensive application?

Posted: Fri Jun 16, 2006 2:35 pm

Newbie

Joined: Tue Jun 08, 2004 7:44 pm
Posts: 12

I have a simple application that does like searches and read only operations on a Sql view of 100,000 rows. The application runs on a j2ee cluster with 2 nodes and I have 200 concurrent users during peak time .The performance considerably degrades at load.

I was wondering if Hibernate + OS Cache/EHCache is a good candidate for solving this as compared to a direct JDBC DB search solution.

I understand that even though I cache the results, the searches still have to go against the databse every time. The searches are taking >2 secs with the direct db call which increases up to 8 secs at peak load. I was wondering if using Hibernate and 2nd level caching would provide better performance because of optimized memory management of result objects. I may not use the query cache effectively because the search criteria are too random. I also have a limitation on the memory footprint of the 2nd level cache to not exceed 50MB. (caching the entire view costs around 170MB) so I am trying to use the disk overflow feature of the caching framework (Eh or Os cache).

Please advise. Thanks in advance.

Hibernate version: 3.0.5

Mapping documents:

Code:

<?xml version="1.0" encoding="utf-8"?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">
<hibernate-configuration>
    <session-factory>
        <property name="show_sql">false</property>
        <property name="format_sql">false</property>
        <property name="hibernate.use_sql_comments">false</property>
        <property name="hibernate.generate_statistics">false</property>
        <property name="hibernate.max_fetch_depth">1</property>

   <property name="hibernate.cache.use_second_level_cache">true</property>
   <property name="hibernate.cache.use_query_cache">true</property>
        <property name="hibernate.cglib.use_reflection_optimizer">true</property>
        <property name="connection.datasource">jdbc/xyzdatasource</property>
        <property name="hibernate.jdbc.fetch_size">100</property>
        <property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

        <property name="hibernate.cache.provider_class">com.mycache.OSCacheProvider</property>
        <property name="hibernate.cache.use_minimal_puts">false</property>
        <property name="hibernate.cache.use_structured_entries">true</property>

        <!-- Mapping files -->
        <mapping resource="DataView.hbm.xml" />
    </session-factory>
</hibernate-configuration>


<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<!-- Generated Apr 20, 2006 11:47:00 AM by Hibernate Tools 3.1.0.beta4 -->

<hibernate-mapping>
    <class name="com.test.XYZ" table="DataView" >
       <cache usage="read-only"/>
   <id name="id1" type="string" column="col1"/>    
         <!-- 44 properties follow-->
          <property name="attribute1" type="timestamp"><column name="col2" length="23" /></property>
           .
           .
           .
   </class>
</hibernate-mapping>

Code between sessionFactory.openSession() and session.close():

Code:

Session session = HibernateUtil.currentSession();
Criteria searchCriteria = session.createCriteria(XYZ.class);
searchCriteria.add(Restrictions.eq("col10","value10"));
   if(null!=value20){
         searchCriteria.add(Expression.ilike("col20", value20+"%"));
      }if(null!=value21){
         searchCriteria.add(Expression.ilike("col21", value21+"%"));         
      }
      searchCriteria.addOrder(Order.asc("col22"));
      searchCriteria.addOrder(Order.asc("col23"));
      searchCriteria.addOrder(Order.asc("col24"));
      searchCriteria.setMaxResults(100);               
      searchCriteria.setCacheable(true);
             resultList = searchCriteria.list();
           session.flush();
      
}catch(Exception se){
   se.printStackTrace();
   throw new Exception("Error while doing db work");
}finally{
   HibernateUtil.closeSession();
}

Full stack trace of any exception that occurs:

none

Name and version of the database you are using:

MS SQL Server 2000

The generated SQL (show_sql=true):

select top 100 this_.col1 as col1_, from DataView this_
where this_.col10_ = > and lower(this_.col21) like ? order by this_.col 22 asc, this_.col21 asc, this_.col24 asc

Debug level Hibernate log excerpt:

error

Top

jherbst

Post subject:

Posted: Sun Jun 18, 2006 2:36 pm

Regular

Joined: Mon Jun 13, 2005 12:21 pm
Posts: 61
Location: Hannover

I guess in you're case, the performance of the SQL used by hibernate is as good as the the performance of handwritten SQL. So the execution time of you're statement won't change.
If as you say the query cache won't help cause the queries are to random (have you tried it?) I would suggest to disable it. Maintaining the query cache costs some ressource without any benefit in you're case.

As you describe you're scenario the second level cache won't help you much. It depend's how often each object is accessed, if your queries will contain some object (let's say 5000) very often and the others (95000) are quite exotic you'll get some benefit. If the propability of accessing each object is pretty equal you won't benefit from a disc based second level cache.

Without having seen it, I guess most of you're performance potential is in the database. You're accessing a database view, so know nothing about the table structur, but accessing a database with an ILIKE is often a performance problem. You should contact you're DBA if he can provide you some index data for col20 and col21.

Top

sairam313

Post subject:

Posted: Thu Jun 22, 2006 12:40 pm

Newbie

Joined: Tue Jun 08, 2004 7:44 pm
Posts: 12

Thanks for your reply. I have two questions.

jherbst wrote:

As you describe you're scenario the second level cache won't help you much. It depend's how often each object is accessed, if your queries will contain some object (let's say 5000) very often and the others (95000) are quite exotic you'll get some benefit.

This is what I am trying to understand. Since all my searches go against the database, will there be any benefit in using the second level cache even if all the result sets that my search queries result are already in cache? I can imagine accessing associations from the cache since it gets accessed by the key and I don't need to go to the DB. Does hibernate use second level cached objects to return even for results from search queries ran on DB there by saving the object creation time? How significant is it?

jherbst wrote:

How expensive do you think maintaining the query cache would be? I still plan to use query cache if it isn't too expensive on the resources because it does boost the performance for those searches that do hit the query cache

Top

jherbst

Post subject:

Posted: Sat Jun 24, 2006 7:28 am

Regular

Joined: Mon Jun 13, 2005 12:21 pm
Posts: 61
Location: Hannover

If you're query always hits the database and there are not association the benefit of the second level cache would be pretty poor. As far as I know hibernate will return the cached object, saving time for creation and evaluating the resultset but the benefit is not the great, cause creation of POJOs is pretty cheap (in CPU time).

In combination with the query cache the situation is different. Cause without any database query you can get the object VERY fast. The query cache isn't that expensive (acutally it stores a String key and the object reference).

As I wrote before, I would suggest two things:
1) Consult you're DBA regarding indixes on you're database view
2) Try it! In my experiences more than 50% of you're queries will benefit from the query cache. Just monitor the queries and look for duplicates.

Top

Page 1 of 1

[ 4 posts ]

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum