16 sec for simple Hibernate select...

vator · **Joined:** Sat Dec 01, 2007 4:34 pm **Posts:** 20

In my app the class AjaxFrontController gets a request for inserting a new category to a table and then returning the category object that was inserted.
The problem is that this takes about 16 seconds, so there must be something wrong...

Code:

public AjaxFrontController{

RecipeManager manager = new RecipeManager();

@RequestMapping("/addCategory.do")
  public void addCategory(HttpServletRequest request, HttpServletResponse response)
          throws IOException, ServletException {

    String parentId = request.getParameter("parentId");
    String newCategory = request.getParameter("newCategory");

    Category childCat = new Category(newCategory, 2);

    long l_1 = System.currentTimeMillis();
    Category c =  manager.addCategory(childCat); // This operation takes about 16 seonds!!!!!!
    long l_3 = System.currentTimeMillis() - l_1;

    loggerObj.info("Time for operation: " + new Long(l_3).toString() ); 
}}

Code:

// Service class
public class RecipeManager {

  private RecipeDaoImpl recipeImpl = new RecipeDaoImpl();

  public Category addCategory(Category newCategory) {
    return recipeImpl.addCategory(newCategory);
  }
}

Code:

public class RecipeDaoImpl {

  public Category addCategory(Category newCategory) {
    Session session = HibernateUtil.getSessionFactory().getCurrentSession();
    session.beginTransaction();
    
    Category c = (Category) session.merge(newCategory);
    session.getTransaction().commit();
    
    return c;
  }
}

Some Hibernate config

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="connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="dialect">org.hibernate.dialect.MySQLInnoDBDialect</property>
 
    <!-- Enable Hibernate's automatic session context management -->
    <property name="current_session_context_class">thread</property>

    <!-- Disable the second-level cache  -->
    <property name="cache.provider_class">org.hibernate.cache.NoCacheProvider</property>

    <!-- Echo all executed SQL to stdout -->
    <property name="show_sql">true</property>

   
  </session-factory>
</hibernate-configuration>

Rajesh Subbiah · **Joined:** Thu Dec 03, 2009 4:36 am **Posts:** 12

Hi vator ,

Try this code instead of yours.

public class RecipeDaoImpl {

public Category addCategory(Category newCategory) {
Session session = HibernateUtil.getSessionFactory().openSession();

session.beginTransaction();

session.save(newCategory);
session.flush()
session.getTransaction().commit();
session.close()

return c;
}
}

vator · **Joined:** Sat Dec 01, 2007 4:34 pm **Posts:** 20

Thanks a lot! Now it takes about 3 to 5 seconds.

But this operation is called through an ajax request, so 3 seconds is still too long time...

Is it possible to get it faster or do I have to restructure my code?

Rajesh Subbiah · **Joined:** Thu Dec 03, 2009 4:36 am **Posts:** 12

Your are just inserting a record and this won't take much time.It takes few milli seconds only.

I guess the problem is there somewhere else.

Just to confirm that , try to test your application this way .

Use a plain Java application which uses hibernate and do the same db operation and time it. If the time is very less, than there is problem in your web-application which uses the hibernate.

vator · **Joined:** Sat Dec 01, 2007 4:34 pm **Posts:** 20

It seems like all my database calls are taking several seconds... I can't find out why...

Below I've pasted the log for a simple select call. From what I can see in the log it seems like Hibernate is doing much more than it should. Could it be because I'm using fetch type eager for my select statements?

The select statement

Code:

List<Category> categoryList = session.createQuery("select c from Category c where c.categoryLevel=1").list();

The bean class

Quote:

@Table(name = "CATEGORY")
public class Category implements Serializable {

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
@Column(name = "CATEGORYID")
private Long categoryId;

@Column(name = "CATEGORY")
private String category;

@Column(name = "CATEGORYLEVEL")
private Long categoryLevel;

@ManyToOne(fetch=FetchType.EAGER, cascade = CascadeType.ALL)
@JoinTable(name = "SUBCATEGORY",
joinColumns = { @JoinColumn(name = "CHILDID")},
inverseJoinColumns = { @JoinColumn(name="PARENTID") })
private Category parent;

@ManyToMany(fetch=FetchType.EAGER, cascade = CascadeType.ALL)
@JoinTable(name = "SUBCATEGORY",
joinColumns = { @JoinColumn(name = "PARENTID")},
inverseJoinColumns = { @JoinColumn(name="CHILDID") })
private Set<Category> subCategoryList;

Log for the select statement
INFO: Hibernate: select category0_.CATEGORYID as CATEGORYID3_, category0_.CATEGORY as CATEGORY3_, category0_.CATEGORYLEVEL as CATEGORY3_3_, category0_1_.PARENTID as PARENTID4_ from CATEGORY category0_ left outer join SUBCATEGORY category0_1_ on category0_.CATEGORYID=category0_1_.CHILDID where category0_.CATEGORYLEVEL=1
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Hibernate: select subcategor0_.PARENTID as PARENTID2_, subcategor0_.CHILDID as CHILDID2_, category1_.CATEGORYID as CATEGORYID3_0_, category1_.CATEGORY as CATEGORY3_0_, category1_.CATEGORYLEVEL as CATEGORY3_3_0_, category1_1_.PARENTID as PARENTID4_0_, category2_.CATEGORYID as CATEGORYID3_1_, category2_.CATEGORY as CATEGORY3_1_, category2_.CATEGORYLEVEL as CATEGORY3_3_1_, category2_1_.PARENTID as PARENTID4_1_ from SUBCATEGORY subcategor0_ left outer join CATEGORY category1_ on subcategor0_.CHILDID=category1_.CATEGORYID left outer join SUBCATEGORY category1_1_ on category1_.CATEGORYID=category1_1_.CHILDID left outer join CATEGORY category2_ on category1_1_.PARENTID=category2_.CATEGORYID left outer join SUBCATEGORY category2_1_ on category2_.CATEGORYID=category2_1_.CHILDID where subcategor0_.PARENTID=?
INFO: Diff tid i ADDRECIPECONTROLLER: 16127