extremely slow behaviour

GG_Peter · **Posted:** Fri May 14, 2004 3:14 am

First of all, I'm using MySQL 4.1 and Hibernate 2.1.3.

When I try to insert data in my table with my hibernate application it is extremely slow when comparing with regular jdbc. I wonder if this a fault of my coding or that this is normal behaviour.

I saw this in my large application, but that would be a bit long to show here. So I made a new small similar testcase which gave me the same behaviour.

I use a DAO layer and a BO layer and the relevant files in here are below. The application works but this is rather slow and as you will see below, I only insert 1000 parents with each 100 children.

My Mapping files
Child.hbm.xml

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by the Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="set.vo.ChildVO"
    table="child3"
>

    <id
        name="id"
        type="java.lang.String"
        column="id"
    >
        <generator class="uuid.hex" />
    </id>

    <property
        name="naam"
        type="java.lang.String"
        column="naam"
        not-null="true"
        length="35"
    />

    <!-- associations -->
    <!-- bi-directional many-to-one association to Parent -->
    <many-to-one
        name="parent"
        class="set.vo.ParentVO"
        not-null="true"
    >
        <column name="parentId" />
    </many-to-one>

</class>
</hibernate-mapping>

Code:

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
    "-//Hibernate/Hibernate Mapping DTD 2.0//EN"
    "http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >
    
<hibernate-mapping>
<!-- 
    Created by the Middlegen Hibernate plugin

    http://boss.bekk.no/boss/middlegen/
    http://hibernate.sourceforge.net/
-->

<class 
    name="set.vo.ParentVO"
    table="parent3"
>

    <id
        name="id"
        type="java.lang.String"
        column="id"
    >
        <generator class="uuid.hex" />
    </id>

    <property
        name="naam"
        type="java.lang.String"
        column="naam"
        not-null="true"
        length="35"
    />

    <!-- associations -->
    <!-- bi-directional one-to-many association to Child -->
    <set
        name="childs"
        lazy="true"
        inverse="true"
    >
        <key>
            <column name="parentId" />
        </key>
        <one-to-many 
            class="set.vo.ChildVO"
        />
    </set>

</class>
</hibernate-mapping>

My DAO layer
HibernateParentDAO

Code:

package set.dao;

import set.vo.ParentVO;
import be.realsoftware.ff.dao.DAOException;
import be.realsoftware.ff.common.ConfigurationException;
import be.realsoftware.ff.common.Logger;
import set.exception.AlreadyExistsException;

import java.util.List;

import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Hibernate;
import set.bo.ParentImpl;

/**
 * Created by IntelliJ IDEA.
 * User: doucepet
 * Date: Jan 29, 2004
 * Time: 9:05:28 AM
 */
public class HibernateParentDAO extends AbstractDAO implements ParentDAO{

    // Instance
    private static HibernateParentDAO instance;

     // HQL Queries
    protected static final String findParentHQL =
            "select parent from set.vo.ParentVO parent " +
            "where parent.id = :parentId";

    protected static final String findParentByNaamHQL =
            "select parent from set.vo.ParentVO parent " +
            "where parent.naam = :parentNaam";
    
    protected static final String findParentsHQL = 
            "select parent from set.vo.ParentVO parent ";

    // Constructor
    private HibernateParentDAO() throws ConfigurationException {
        super();
    }

    public static ParentDAO getInstance() throws ConfigurationException {
        if (instance == null) {
            instance = new HibernateParentDAO();
        }
        return instance;
    }

    public void createParent(ParentVO parent) throws DAOException, AlreadyExistsException {
        Logger.debug(this, "Create " + parent);
        try {
            ParentImpl.getUserSession().save(parent);
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new DAOException("Hibernate Exception tegengekomen bij createParent", e);
        }
    }

    public void updateParent(ParentVO parent) throws DAOException, AlreadyExistsException {
        Logger.debug(this, "Update " + parent);
        try {
            ParentImpl.getUserSession().update(parent);
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new DAOException("Hibernate Exception tegengekomen bij updateParent", e);
        }
    }

    public void deleteParent(String parentId) throws DAOException {
        try {
            Logger.debug(this, "Delete parent met id: " + parentId);
            // search and return
            String query = findParentHQL;
            List list = ParentImpl.getUserSession().find(query, parentId,
                                      Hibernate.STRING);
            if(list.size() == 0) {
                System.out.println("Geen parent met id " + parentId);
            }
            else {
                ParentVO parent = (ParentVO) list.get(0);
                ParentImpl.getUserSession().delete(parent);
            }
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new DAOException("Hibernate Exception tegengekomen bij deleteParent", e);
        }
    }

    public List findParents() throws DAOException {
        try {
            Logger.debug(this, "Finding all Parents");
            String query = findParentsHQL;
            List list = ParentImpl.getUserSession().find(query);
            if(list.size()!=0){
                return list;
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findParents");
        }
        return null;
    }

    public List findChildrenForParent(String parentId) throws DAOException {
        return DAOFactory.getChildDAO().findChildrenForParent(parentId);
    }

    public ParentVO findParent(String parentId) throws DAOException {
        try {
            Logger.debug(this, "Find ParentVO with id : " + parentId);
            String query = findParentHQL;
            List list = ParentImpl.getUserSession().find(query, parentId, Hibernate.STRING);
            if(list.size()!=0){
                return (ParentVO) list.get(0);
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findParent");
        }
        return null;
    }

    public ParentVO findParentByNaam(String parentNaam) throws DAOException {
        try {
            Logger.debug(this, "Find ParentVO with id : " + parentNaam);
            String query = findParentByNaamHQL;
            List list = ParentImpl.getUserSession().find(query, parentNaam, Hibernate.STRING);
            if(list.size()!=0){
                return (ParentVO) list.get(0);
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findParent");
        }
        return null;
    }
}

ChildVO

Code:

package set.dao;

import set.vo.ChildVO;
import be.realsoftware.ff.dao.DAOException;
import be.realsoftware.ff.common.ConfigurationException;
import be.realsoftware.ff.common.Logger;
import set.exception.AlreadyExistsException;

import java.util.List;

import set.bo.ChildImpl;
import net.sf.hibernate.HibernateException;
import net.sf.hibernate.Hibernate;

/**
 * Created by IntelliJ IDEA.
 * User: doucepet
 * Date: Jan 29, 2004
 * Time: 9:06:23 AM
 */
public class HibernateChildDAO extends AbstractDAO implements ChildDAO{

    // Instance
    private static HibernateChildDAO instance;

     // SQL Queries
    protected static final String findChildHQL =
            "select child from set.vo.ChildVO child " +
            "where child.id = :childId";
    
    protected static final String findChildByNaamHQL =
            "select child from set.vo.ChildVO child " +
            "where child.naam = :childNaam";

    protected static final String findChildrenHQL =
            "select child from set.vo.ChildVO child ";
    
    protected static final String findChildrenForParentHQL =
            "select child from set.vo.ChildVO child " +
            "where child.parent = :parentId";
    
    // Constructor
    private HibernateChildDAO() throws ConfigurationException {
        super();
    }

    public static ChildDAO getInstance() throws ConfigurationException {
        if (instance == null) {
            instance = new HibernateChildDAO();
        }
        return instance;
    }

    public void createChild(ChildVO child) throws DAOException, AlreadyExistsException {
        Logger.debug(this, "Create " + child);
        try {
             ChildImpl.getUserSession().save(child);
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new DAOException("Hibernate Exception tegengekomen bij createChild", e);
        }
    }

    public void updateChild(ChildVO child) throws DAOException, AlreadyExistsException {
        Logger.debug(this, "Update " + child);
        try {
             ChildImpl.getUserSession().update(child);
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new DAOException("Hibernate Exception tegengekomen bij updateChild", e);
        }
    }

    public void deleteChild(String childId) throws DAOException {
        try {
            Logger.debug(this, "Delete child met id: " + childId);
            // search and return
            String query = findChildHQL;
            List list = ChildImpl.getUserSession().find(query, childId,
                                      Hibernate.STRING);
            if(list.size() == 0) {
                System.out.println("Geen child met id " + childId);
            }
            ChildVO child = (ChildVO) list.get(0);
            ChildImpl.getUserSession().delete(child);
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new DAOException("Hibernate Exception tegengekomen bij deleteChild", e);
        }
    }

    public void deleteChildrenForParent(String parentId) throws DAOException {
        try {
            Logger.debug(this, "Deleting Children for ParentVO (id) : "+parentId);
            String query = findChildrenForParentHQL;
            List list = ChildImpl.getUserSession().find(query, parentId, Hibernate.STRING);
            if(list.size()!=0){
                for(int i=0;i<list.size();i++) {
                    ChildVO child = (ChildVO) list.get(i);
                    Logger.debug(this, "Deleting "+child);
                    ChildImpl.getUserSession().delete(child);
                }
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "Hibernate Exception bij deleteChildrenForParent");
        }
    }

    public List findChildren() throws DAOException {
        try {
            Logger.debug(this, "Searching all children");
            String query = findChildrenHQL;
            List list = ChildImpl.getUserSession().find(query);
            if(list.size()!=0){
                return list;
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findChildren");
        }
        return null;
    }

    public List findChildrenForParent(String parentId) throws DAOException {
        try {
            Logger.debug(this, "Searching children for ParentVO (id) : "+parentId);
            String query = findChildrenForParentHQL;
            List list = ChildImpl.getUserSession().find(query, parentId, Hibernate.STRING);
            if(list.size()!=0){
                return list;
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findChildrenForParent");
        }
        return null;
    }

    public ChildVO findChild(String childId) throws DAOException {
        try {
            Logger.debug(this, "Searching child (id) : "+childId);
            String query = findChildHQL;
            List list = ChildImpl.getUserSession().find(query, childId, Hibernate.STRING);
            if(list.size()!=0){
                return (ChildVO) list.get(0);
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findChild");
        }
        return null;
    }

    public ChildVO findChildByNaam(String childNaam) throws DAOException {
        try {
            Logger.debug(this, "Searching child (naam) : "+childNaam);
            String query = findChildByNaamHQL;
            List list = ChildImpl.getUserSession().find(query, childNaam, Hibernate.STRING);
            if(list.size()!=0){
                return (ChildVO) list.get(0);
            }
        }
        catch (HibernateException e) {
            Logger.debug(this, "HibernateException bij findChildByNaam");
        }
        return null;
    }
}

My BO Layer
ChildImpl

Code:

package set.bo;

import set.vo.ChildVO;
import set.exception.BOException;
import set.exception.AlreadyExistsException;

import java.util.List;

import net.sf.hibernate.Transaction;
import net.sf.hibernate.HibernateException;
import set.dao.DAOFactory;
import be.realsoftware.ff.common.Logger;
import be.realsoftware.ff.dao.DAOException;

/**
 * Created by IntelliJ IDEA.
 * User: doucepet
 * Date: Jan 29, 2004
 * Time: 9:15:28 AM
 */
public class ChildImpl extends AbstractBusiness implements Child{

    private static Child instance;

    public static Child getInstance() {
        if (instance == null) {
            instance = new ChildImpl();
        }
        return instance;
    }

    public void createChild(ChildVO child) throws BOException, AlreadyExistsException {
        Transaction t = null;
        try {
            t = getUserSession().beginTransaction();
            DAOFactory.getChildDAO().createChild(child);
            t.commit();
        } catch (AlreadyExistsException e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw e;
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new BOException("Error tijdens mappen", e);
        } catch (Exception e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw new BOException("Error tijdens het aanmaken van child", e);
        }
    }

    public void updateChild(ChildVO child) throws BOException, AlreadyExistsException {
        Transaction t = null;
        try {
            t = getUserSession().beginTransaction();
            DAOFactory.getChildDAO().updateChild(child);
            t.commit();
        } catch (Exception e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw new BOException("Error tijdens het verwijderen van parent", e);
        }
    }

    public void deleteChild(String childId) throws BOException {
        Transaction t = null;
        try {
            t = getUserSession().beginTransaction();
            DAOFactory.getChildDAO().deleteChild(childId);
            t.commit();
        } catch (Exception e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw new BOException("Error tijdens het verwijderen van child", e);
        }
    }

    public ChildVO findChild(String childId) throws BOException {
        try {
            return DAOFactory.getChildDAO().findChild(childId);
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij findChild");
            return null;
        }
    }

    public ChildVO findChildByNaam(String childNaam) throws BOException {
        try {
            return DAOFactory.getChildDAO().findChildByNaam(childNaam);
        }
        catch (DAOException e) {
            throw new BOException("DAOException bij findChildByNaam", e);
        }
    }

    public List findChildren() throws BOException {
        try {
            return DAOFactory.getChildDAO().findChildren();
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij findChildren");
            return null;
        }
    }

    public List findChildrenForParent(String parentId) throws BOException {
        try {
            return DAOFactory.getChildDAO().findChildrenForParent(parentId);
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij findChildForParent");
            return null;
        }
    }
}

ParentImpl

Code:

package set.bo;

import set.vo.ParentVO;
import set.vo.ChildVO;
import set.exception.BOException;
import set.exception.AlreadyExistsException;

import java.util.List;
import java.util.Iterator;

import net.sf.hibernate.Transaction;
import net.sf.hibernate.HibernateException;
import set.dao.DAOFactory;
import be.realsoftware.ff.common.Logger;
import be.realsoftware.ff.dao.DAOException;

/**
 * Created by IntelliJ IDEA.
 * User: doucepet
 * Date: Jan 29, 2004
 * Time: 9:15:36 AM
 */
public class ParentImpl extends AbstractBusiness implements Parent{

    private static Parent instance;

    public static Parent getInstance() {
        if (instance == null) {
            instance = new ParentImpl();
        }
        return instance;
    }

    public void createParent(ParentVO parent) throws BOException, AlreadyExistsException {
        Transaction t = null;
        try {
            t = getUserSession().beginTransaction();
            DAOFactory.getParentDAO().createParent(parent);
            if(parent.getChilds()!=null){
                getUserSession().flush();
                createChildren(parent);
            }
            t.commit();
        } catch (AlreadyExistsException e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw e;
        } catch (HibernateException e) {
            Logger.error(this, e);
            throw new BOException("Error tijdens mappen", e);
        } catch (Exception e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw new BOException("Error tijdens het aanmaken van parent", e);
        }
    }

    public void updateParent(ParentVO parent) throws BOException, AlreadyExistsException {
        Transaction t = null;
        try {
            t = getUserSession().beginTransaction();
            DAOFactory.getParentDAO().updateParent(parent);
//            DAOFactory.getChildDAO().deleteChildrenForParent(parent.getId());
//            if(parent.getChilds()!=null){
//                createChildren(parent);
//            }
            t.commit();
        } catch (Exception e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw new BOException("Error tijdens het verwijderen van parent", e);
        }
    }

    public void deleteParent(String parentId) throws BOException {
        Transaction t = null;
        try {
            t = getUserSession().beginTransaction();
            DAOFactory.getChildDAO().deleteChildrenForParent(parentId);
            getUserSession().flush();
            //delete ParentVO - Children automatically deleted?
            DAOFactory.getParentDAO().deleteParent(parentId);
            t.commit();
        } catch (Exception e) {
            try {
                t.rollback();
            } catch (Exception ex) {
                Logger.error(this, ex);
            }
            throw new BOException("Error tijdens het verwijderen van parent", e);
        }
    }

    public ParentVO findParent(String parentId) throws BOException {
        try {
            return DAOFactory.getParentDAO().findParent(parentId);
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij findParent");
            return null;
        }
    }

    public ParentVO findParentByNaam(String parentNaam) throws BOException {
        try {
            return DAOFactory.getParentDAO().findParentByNaam(parentNaam);
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij findParentByNaam");
            return null;
        }
    }

    public List findParents() throws BOException {
        try {
            return DAOFactory.getParentDAO().findParents();
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij findParents");
            return null;
        }
    }

    public List findChildrenForParent(String parentId) throws BOException {
        try {
            return DAOFactory.getParentDAO().findChildrenForParent(parentId);
        } catch (DAOException e) {
            Logger.debug(this, "DAOException bij findChildrenForParent");
            return null;
        }
    }

    private void createChildren (ParentVO parent){
        try {
            Iterator iter = parent.getChilds().iterator();
            do {
                DAOFactory.getChildDAO().createChild((ChildVO) iter.next());
            } while (iter.hasNext());
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij createChildren");
        }
        catch (AlreadyExistsException e) {
            Logger.debug(this, "AlreadyExistsException bij createChildren");
        }
    }
}

When I try to run the following testcase this Hibernate version takes about 20 minutes to complete. While the regular jdbc version takes 3 minutes.

Code:

public void testCreateParentWithChildren() throws BOException {

        ParentVO [] parent = new ParentVO[1000];
        for(int i=0;i<1000;i++){
            parent[i] = new ParentVO("ParentVO "+(i+1));
            ChildVO [] child = new ChildVO[100];
            Set set = new HashSet();
            for(int j=0;j<100;j++){
                child[j] = new ChildVO("Child "+(j+1)+" for ParentVO "+(i+1), parent[i]);
                set.add(child[j]);
            }
            parent[i].setChilds(set);
        }

        try {
            // Create parent1
            for(int i=0;i<1000;i++) {
                ParentImpl.getInstance().createParent(parent[i]);
                Logger.debug(this, parent[i].getNaam()+" completed");
            }
        } catch (Exception e) {
            e.printStackTrace();
            fail(e.getMessage());
        } finally {
            // Delete parent
            //ParentImpl.getInstance().deleteParent(parent1.getId());
        }
}

Am I doing something wrong?
Any help is appreciated !

Peter

michael · **Posted:** Fri May 14, 2004 3:48 am

Probably you should do everything in one transaction and not in 1000?

GG_Peter · **Posted:** Fri May 14, 2004 3:49 am

Urgh, I think I need some more sleep.

GG_Peter · **Posted:** Fri May 14, 2004 3:55 am

So I best make another method in my BO layer like createParents. Cause in my view the BO layer is responsible for starting and commiting your transaction. I don't want the application (web-app) to do anything database related.

In the web-app all parents are created one at a time, so that wouldn't be a real problem. But I was running the testcase to populate my database with some more records. The original idea was to put 1.000.000 extra parents. Luckily I tried first with 1.000. ;)

michael · **Posted:** Fri May 14, 2004 4:11 am

Probably you should try doing your session/transaction handling in a servlet filter, or use something like springs declarative transaction handling.

GG_Peter · **Posted:** Fri May 14, 2004 4:17 am

k, thx for your time and reply

Peter

GG_Peter · **Posted:** Fri May 14, 2004 4:42 am

Yes, 1 minutes 30 seconds

Twice as fast as JDBC now, almost 20 times faster as my solution. :)

rtayek · **Posted:** Sat May 15, 2004 2:30 am

GG_Peter wrote:

First of all, I'm using MySQL 4.1 and Hibernate 2.1.3.
...

Code:

package set.bo;
...
public class ParentImpl extends AbstractBusiness implements Parent{
...
    private void createChildren (ParentVO parent){
        try {
            Iterator iter = parent.getChilds().iterator();
            do {
                DAOFactory.getChildDAO().createChild((ChildVO) iter.next());
            } while (iter.hasNext());
        }
        catch (DAOException e) {
            Logger.debug(this, "DAOException bij createChildren");
        }
        catch (AlreadyExistsException e) {
            Logger.debug(this, "AlreadyExistsException bij createChildren");
        }
    }
}

Peter

pm4ji, nice dao's, but the iter.next() above will throw if the parent has no children.